Having problems with a format file data read in

Question

I have 15 .txt files with 1 million records each that I am trying to read into SQL using bulk insert and a non-XML format file. It is mostly working, however I am having a slight issue.

Although I specified the length of each of the columns in the format file, when I do:

select top (100) * from my_table 

it's obvious that the data in each row after the first row is bumped one space forward, ie, there is 1 position unaccounted for.

instead of row2(var1, var2, var3,etc) being:            60|34| 8966|F| TG

each row after the first reads:                                 6|03|4 896|6|F T

This extra position does not exist in the original .txt source file. 

I have tried two remedies to this issue. 

The first, was to extend the length of the last column from char(11) to char(12) to try and 'eat up' that extra position so that my data wasn't bumping forward into the wrong columns. However, when I make the column large in both the table and the format file, it still bumps forward data in all rows after the first by one position.

The second try was to insert a new column in after the last column and make it char(1). However, this just led to data truncation issues and then it wouldn't even read data in at all, so a step even further in the wrong direction from my first attempt at solving this. 

I can't create a new column in front of the very first or extend the length of the first column because then it would through off the first row, and hence, the following 999999. 

Not sure how else to account for this extra space. I even tried changing my format file's ending column newline\carriage return from '\r' to' \n\r' and this resulted, again, in the file not being read in at all. 

It's important that I can get this format file method to work, since using the import wizard would mean having to retype and specify all the columns, 15 separate times instead of just using the same file over and over and having a new table name that takes seconds to create..and I don't have SSIS. 

How else can I stop my data from all bumping forward by one position?!!

Thanks!!

Answer 1

Change your row terminator to \r\n

\n\r is backwards

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

Thank you, that actually resolved the entire bumping issue! I don't know how these things manage to confuse me for hours...

-Fail Econgirl8