I'd like to bcp import a file that sometimes misses the last column/s. There's an EOL character instead. For some reason, bcp wraps around, ignoring the EOL character, and continues reading from the next row of the file. Instead, I'd like to replace the missing columns by null.
I've tried using bcp, bulk insert and the DTS Wizard. So far, I've only been succesfull using the DTS Wizard. I also do some other bcp imports, so I'd like to stick with bcp.
bcp table1 in myFile.csv -SServer1 -T -c -k -t\t -r\n
The file has lf as row-terminator, but I guess it wont be a problem changing that into cr/lf. The lf is readable for those rows that match the layout of the table. It's the rows that don't match that are wrapped. As far as I can see now, either the file will have to include empty values instead of nothing or I'll have to divide the proces in smaller processes.