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.
| 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? |
 |
|
|
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 |
|
|
|
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? |
|
|
|
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 |
|
|
|
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. |
|
|
|
|
|
|
|
|
2005 Import/Export Flat File