How to import data to a table with 14 columns via BCP if my data file has less than 13 delimiters?

Question

Sorry for the extremely awkward wording in that question. I'll explain.

I have a table with 14 columns in which i'm trying to import data to via BCP. My data comes from a text file. This text file is TAB delimited. Logically there should be 13 delimiters for 14 cells of data in a row. My data is inconsistent and doesn't have delimiters if the values at the end are null. This means that some rows of data only have 10 delimiters. This causes my data to "wrap around" when it is imported. The first cell of data in my text file is being put in the 10th column of the row prior to it. It should be the first cell in its own new row.

The thing is every single row in the text file ends in with "CRLF" which is used by default in BCP.

Is there a way to tell BCP to fill in all 14 columns before moving on to the next row? Or will i have to re format my data file every time i import (not ideal).

Here is my BCP command:

 bcp testdb.dbo.MACARP in C:\Users\sysbrady\Desktop\MyData.txt /c /T /t "\t" /E -S WSTVDISTD023\SQLEXPRESS

Answer 1

"Is there a way to tell BCP to fill in all 14 columns before moving on to the next row?"

When you say "fill in", do you mean you want BCP to keep the null values present in your text file? The -k qualifier tells BCP to keep the nulls (make sure the column in your table allows nulls). See link below:

http://msdn.microsoft.com/en-us/library/ms187887.aspx

"The thing is every single row in the text file ends in with "CRLF" which is used by default in BCP."

This is unclear - could you post an image? Unsure of whether you have phrased this as a problem, or a feature you want to retain.