Load csv file into a table

[uksql]

All,

When I try to load data into a table using a csv file I get below errors.

bulk insert client_key
from 'T:\CLIENT_KEY.txt'
WITH (
    FIELDTERMINATOR =',',
    ROWTERMINATOR = '\r\n',
    DATAFILETYPE  = 'widenative')

Msg 4866, Level 16, State 4, Line 1
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.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Sample of file, first 2 lines

56QS2LttX5P3DVN5WQVcOWyk5Y=,vIXIFDGBDVK62m2Ka6/yuPLCJMc=,2010-07-01 00:00:00.000,2019-09-09 00:00:00.000,NULL,NULL,NULL,NULL,NULL
5A7OUtuTDodcn59DhimXhpCFRQw=,7D751838D2D3DVZ5Q97A22FB6945F1FE559578681D0478815D3436A7DD1B14352,2009-01-01 00:00:00.000,2019-01-01 00:00:00.000,NULL,NULL,NULL,NULL,key2

Thank you all in advance.

[Rabbit]

I think the error message is fairly clear. It's saying the first row, first column in the text file is too long to fit in the table field it's trying to insert into.

[uksql]

But the value I a mtrying to insert is very small.
Below is the create table stmt.

CREATE TABLE [dbo].[CLIENT_KEY](
    [CLNT_REF_ID] [varchar](256) NOT NULL,
    [CLIENT_KEY] [varchar](256) NULL,
    [START_DATE] [datetime] NULL,
    [END_DATE] [datetime] NULL,
    [USM_CREATOR] [varchar](10) NULL,
    [USM_CREATE_TS] [datetime] NULL,
    [USM_UPDATOR] [varchar](50) NULL,
    [USM_UPDATE_TS] [datetime] NULL,
    [ENC_KEY] [varchar](50) NULL
) ON [PRIMARY]

[Rabbit]

Are you using SQL Server 2005 before cumulative update 4? Because apparently it's a known bug. http://support.microsoft.com/kb/942660

It suggests using ASCII instead of Unicode as a workaround.

[uksql]

Rabbit - but my version of SQL Serverin which I am trying to insert is 2008 R2 not 2005.


And thank you next time i'll make sure I use code snippets.

[Rabbit]

I would try using ASCII anyways to see if that resolves the issue. Especially since your field in the table is ASCII but you're using unicode to read your text file.