Member Avatar for jovillanuev

Hi All,

When I run this script using bulk insert i got an error message
but when I try the first 6 records there is no problem.
can anyone give the reason and what is the problem?

Use chrisMartin
Bulk Insert SampleTable_Jonel
FROM 'c:\planning\ProductRecoveryDataFeedytd_3.csv'   --Sheet 1
With
(
DATAFILETYPE = 'char',
FieldTerminator = ',',
CODEPAGE = 'OEM',
KEEPNULLS,
RowTerminator = '\n', FirstRow = 1)

Error message

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 11 (InsertDate).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 11 (InsertDate).

thanks you.

jov

  • 5 Contributors
  • 5 Replies
  • 322 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by Eagletalon

Dani AI

Generated

The error shows the loader choked converting the InsertDate column for specific rows. That usually means the raw CSV contains values SQL Server can’t parse as a datetime under the current import settings (format/encoding/hidden characters/extra delimiters). , and pointed in the right directions — below are focused, practical diagnostics and fixes you can apply without touching the target table.

Load raw text into a staging table (all columns NVARCHAR/VARCHAR) so the import won’t fail on conversion. Then find the offending rows and inspect their bytes and first character codepoint:

CREATE TABLE dbo.StagingCSV (Col1 NVARCHAR(4000), Col2 NVARCHAR(4000), InsertDate NVARCHAR(200));
-- (bulk-load into StagingCSV)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn,
       InsertDate, DATALENGTH(InsertDate) bytes,
       UNICODE(LEFT(InsertDate,1)) firstChar
FROM dbo.StagingCSV
WHERE ISDATE(LTRIM(RTRIM(InsertDate))) = 0
  AND LTRIM(RTRIM(InsertDate)) <> '';

Common quick fixes (apply after confirming which problem you have):

  • Remove BOM or zero-width chars (firstChar = 65279), and strip stray carriage returns:
    UPDATE dbo.StagingCSV
SET InsertDate = LTRIM(RTRIM(
CASE WHEN UNICODE(LEFT(InsertDate,1)) = 65279 THEN SUBSTRING(InsertDate,2,LEN(InsertDate)-1) ELSE InsertDate END
));
UPDATE dbo.StagingCSV SET InsertDate = REPLACE(InsertDate, CHAR(13), '');
  • If rows use a different date layout, convert with an explicit style or use TRY_CONVERT/TRY_PARSE (if available) and only insert valid rows:
    INSERT INTO dbo.Target (InsertDate)
SELECT CONVERT(datetime, InsertDate, 101) FROM dbo.StagingCSV WHERE ISDATE(InsertDate)=1;

Also verify file encoding and row terminator: a Windows CSV usually ends with CR+LF (use '\r\n') and Unicode files need DATAFILETYPE='widechar' (or appropriate CODEPAGE). If the CSV has quoted fields or embedded commas, use a format file or an ETL tool (SSIS/PowerShell) that properly handles quoted CSVs. Always work on a copy and validate with the staging queries above before inserting into production.

Recommended Answers

All 5 Replies

Member Avatar for debasisdas

how i will know what data is in the .csv file

Member Avatar for Member #905211

looks like the date formats don't match

Member Avatar for Eagletalon

is it possible the data contains characters that would "break" the insert? eg. a VARCHAR value like: 'Stan's bbq'

Member Avatar for crishlay

may possible InsertDate is null in Excel file, validate Isnull(InsertDate) then set some date in query

Member Avatar for Eagletalon

Could you possibly provide some example data? perhaps the record that "breaks" the insert? (you mentioned the first 6 works, perhaps nr 7 is a problem?)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.