0

I am using BCP to import a quoted CSV file into the database.

The table is correctly defined (IMO) as a mix of NVARCHAR and NUMERIC fields. Some of the numeric fields have decimals (100.1).

Sample CSV line

"007091901713","010288367-001","7093900093","010288367-001","TBayTel","CELL","877.5","832.5","877.5",,"5","DOG LAKE","ON","48.674680556","-89.556313889","457","47","B8",,"60","I","5M00D7WFC","75","Kathrein 80010692V01","47","320","3","12","N","1.8","N",,"2016-11-18"

Error

#@ Row 1, Column 7: Invalid character value for cast specification @#
"007091901713"  "010288387-001" "7093900093"    "010288387-001" "TBayTel"   "CELL"  "877.5" "832.5" "877.5" <NULL>  "5" "MONTREAL RIVER"    "ON"    "47.2472"   "-84.5983"  "529"   "87"    "B8"    <NULL>  "60"    "I" "5M00D7WFC" "A7"    "EMSRR801200DA2"    "87"    "222"   "0" "12"    "N" "1.9"   "N" <NULL>  "2016-11-18"

Column 7 is the value of "877.5" which is defined in the table as a NUMERIC(16,8) which I believe should accept the value - its within bounds. And if I do a normal SQL insert statement with copying and reformatting the CSV line it does work. I've tried using both regular BCP commands as well as having a format file (I created a format file based on the table).

bcp "dbo.table" in "full-extract.csv"  -U "$adminSqlLogin" -P "$password" -S $serverNameFQDN -d $databaseName -f full_extract.bcp.fmt  -e "./error-full.log" -a 65535 -b 10000
siromega
  • 132
  • 11
  • 1
    Unless I recall incorrectly, `bcp` doesn't support quote identified files. – Thom A May 06 '23 at 21:13
  • Please show your format file `full_extract.bcp.fmt` – Charlieface May 06 '23 at 22:16
  • 1
    The bcp Utility doesn't support Quoted CSV files. If you need to handle those, and you're on SQL Server 2017 or later, have you considered using `BULK INSERT` with the [`FORMAT = 'CSV'` option](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql#format--csv) for RFC 4180 support? – AlwaysLearning May 07 '23 at 00:09
  • @AlwaysLearning you are correct. I had to load the file into Azure and the BULK INSERT from Azure Blob storage with the FORMAT = 'CSV' option. I found other problems with the file but at least I was able to get past this issue. Thank you! – siromega May 07 '23 at 02:52

0 Answers0