I have a file which is comma delimited. The text qualifier is double quote(“”). There are commas within the text qualifier. How can I bulk insert this file into the sql server table? I tried using format file but it keeps giving me below error "The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly." I changed the size but still got the same message. Here’s my format file: > 10.0 > 2 > 1 SQLCHAR 0 100 "," 1 CDate "" > 2 SQLCHAR 0 50 "\\r\\n" 10 Ord_ID "" And below is the data file example > "7/17/2013 10:36:40 AM" "32309224" Is there anyway I can bulk insert the file without format file? Thanks.
Without a format file, you'd need 5 columns, 1 for each double-quote (DQ) delimiter plus a leading column. The leading column would come before the first DQ and would be blank. The 2nd column would be the date/time after the first DQ. The 3rd column would be the blank folowing the 2nd DQ. The 4th column would be the 32309224 after the 3rd DQ. Finally, another blank column would follow the 4th DQ. While that seems a waste, it will likely be much faster than importing the row as a blob an splitting it even if it turns out that both of your desired columns are "fixed length".