Solved

Flat Text File Format

Posted on 2013-01-21
8
430 Views
Last Modified: 2013-01-21
I got a new Vendor and FTP the flat text file. However for some reason  <carriage return> and <linefeed> does not work and instead of structured data (seven fields per row I got one string. In my package the row terminator is set to {CR}{LF} but seems cannot find it in the text. Did anybody have this problem before? Any suggestions? Thank you.
0
Comment
Question by:Burzhuin
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38801827
Are you show the row delimiter is CrLf?  Perhaps it is just Lf.  You can confirm this with a Hex editor otherwise you can post a sample here and we can verify for you.
0
 

Author Comment

by:Burzhuin
ID: 38801855
I confirmed it and it is CrLf.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38801978
Then I am afraid I have no idea what could be going on given all the information you have provided.

Good luck.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 16

Accepted Solution

by:
AlexPace earned 500 total points
ID: 38802001
Make sure the text file is sent over FTP in ASCII mode instead of BINARY mode
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 38802105
How about the field terminator, what is it set for? Also can you attach a data sample?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38802370
@AlexPace, ASCII mode normally does line-ending translations, BINARY mode does not.  In addition, it does it going both ways so even if it gets translated when uploaded, it will be re-translated when downloaded so it will look like nothing was changed on the server when it was.
0
 
LVL 16

Expert Comment

by:AlexPace
ID: 38802550
@DaveBaldwin, yes that's why I suggested it.  It is possible that the new host is not a windows box so using ASCII mode will "fix" the line endings as far as SQL Server is concerned.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38802616
@AlexPace, good then, I wasn't sure.  Looks the poster agrees with you.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 29
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 65
Are triggers slow? 7 13
sql server query 18 41
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question