Solved

Flat Text File Format

Posted on 2013-01-21
8
424 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 82

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 82

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now