SQL Bulk Insert or BCP

Hi,

Does anyone know if it is possible when using SQL Bulk Insert or BCP will allow for a variable number of columns in the input csv file? Or is it a requirement for these two commands to have a fixed number of cols in the input file?

I'm using Bulk Insert with fieldterminator = '","' and rowterminator = '\n' for the delimited csv file, but my input file does not have a fixed number of cols, and is not padded out with the appropiate number of empty ",".

Is there a way around this?

Roger

I'm not sure if you can have a variable number of fields in a csv file.

I'd probably just write something that would add the delimeter however many times the line needed in order to make it importable.

Then the bulk insert would work.

Both applications expect there to be the same number of columns in each row, even if you have a row of ,,,,,,,,,,,,,,,,,,,,,,,'s each of the empties is considered a NULL value.

Here is a quick and dirty fix:

Open the delimited file in Excel.
File - Save As
CSV File
Click YES (you want to lose any features that may not be compatible with CSV etc...)
Find the file
Rename it to .txt
Open it in Notepad
Make sure it has the newly added commas.

On Problem, your row delimiter is "," instead of just ,

Use DTS, and set Text Qualifier = "
Delimiter = ,
Rowterminator <CR><LF>

All 3 of those should be set by default.

Not sure about BULK INSERT, but I beleive BCP will allow a Text Qualifier as well, so you could just use your commas as delimeters instead of ",".

Let me know if you are still having issues I do this alot.