Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 2005 Import/Export Flat File

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-01-21 : 15:10:15
This is very strange. I have a tab delimited file that has a CR/LF as a row terminator. I can load it in to an Access 03 DB, no problem. When I use the SQL Server 2005 Import Export Wizard it recognizes the first row, but for row two it dumps all fields in to a last column on row one. I can see in the last column in the wizard all of the data with the tabs between.

Then the next row is row 3 and row 4 ends up crammed in to the last column of row 3. The next row is row 5 with row 6 crammed in to the last column of row 5, etc, etc, etc.

I'm going nuts here. Has anyone experienced this before?

Greg

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 15:16:57
Are you sure all rows end in CR/LF? Did the file come from a Unix/Linux system by any chance?
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-01-21 : 16:15:27
I have not programmatically looked at the last 2 ASCII codes at the end of each line, but when viewing the files in NotePad I see distinct rows. They also look fine in UltraEdit and it will prompt me to convert to DOS format for odd files. Also, as I said, the MS Access 2003 import wizard has no problem with the files.

Greg
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 16:26:12
Hmmm. Usually MS Access will ignore little inconsistencies and just import it anyway, but SSIS might not. Notepad sometimes does the same thing, haven't used UltraEdit in a while so I can't say about that.

Could it be a Unicode file instead of ANSI?
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-01-21 : 19:34:21
Not sure if it is ANSI, but something is odd. This is a tab delimited file. I opened it in Excel, which opened it fine, and then saved it as a tab delimited file under a new name. It went from 282kb to 276kb and then SSIS loaded it. I'm going to need to talk to the people who sent me these. There are 16 files and I'm supposed to get them monthly or maybe quarterly. Either way, this is a pain.

Greg
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 20:11:58
Another option you might want to try is BULK INSERT or bcp, both of which can import a tab delimited file. Assuming either or both of them error out, you'll get some detailed messages about where in the file the errors occurred. The last suggestion I have is to use a hex editor and poke around looking for stray control characters, anything that's not 0x09, 0x0A or 0x0D.
Go to Top of Page
   

- Advertisement -