BULK INSERT PROBLEM for CAV file

• pgainer

  SSC Enthusiast

  Points: 181

  More actions

  February 19, 2023 at 10:59 pm

  #4151705

  Hello SQL Server Community,

  I'm not new to T-SQL but I have a simple problem that I have not been able to solve after several hours of research.

  I have several CSV files that I want to import into SQL Server (v2008 R2)

  I have created a temp table.

  CREATE TABLE DataTMP

  (

  Col1 varchar(255) NOT NULL,

  Col2 varchar(255) NOT NULL,

  Col3 varchar(255) NOT NULL,

  Col4 varchar(255) NOT NULL,

  Col5 varchar(255) NOT NULL,

  Col6 varchar(255) NOT NULL,

  Col7 varchar(255) NOT NULL

  )

  I have used the following query

  BULK INSERT DataTMP

  FROM 'c:\data\File1.csv'

  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'); -- I have also used 0x0a

  When I run the query, I get the following error message

  "Bulk load data conversion error (truncation) for row 1, column 7 (Col7)".

  I loaded the file in a hex viewer and the columns are separated with a comma and each line is terminated with a hex 0a

  The CSV file has 7 Columns [Transaction Date],[Post Date],[Description],[Category],[Type],[Amount],[Memo]

  The Memo column is almost always NULL.

  Does anybody see what I'm doing wrong?

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  February 20, 2023 at 1:33 pm

  #4151972

  dup post - see replies in https://www.sqlservercentral.com/forums/topic/bulk-insert-problem-for-cav-file-2