BULK INSERT ERROR using Format File - Bulk load data conversion error (truncation) for row 1, column 1

Hi,

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the \r\n is correct for line 7 right?

Any ideas where I'm going wrong?

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).

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)".

BULK

INSERTtbl_ASX_Data_temp

FROM

'M:\Data\ASX\Import\Test.txt'

WITH

(FORMATFILE='M:\Data\ASX\SQL\Format\Import.Fmt')

GO

12.0
7
1       SQLNCHAR       0        6      ","     1      ASXCode       SQL_Latin1_General_CP1_CI_AS
2       SQLINT         0       10      ","     2      ImportDate    ""
3       SQLFLT8        0       20     ","      3      Open          ""
4       SQLFLT8        0       20     ","      4      High          ""
5       SQLFLT8        0       20     ","      5      Low           ""
6       SQLFLT8        0       20     ","      6      Close         ""
7       SQLFLT8        0       20     "\r\n"   7      Volume        ""


June 29th, 2015 8:16am

Hi,

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the \r\n is correct for line 7 right?

Any ideas where I'm going wrong?

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).

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)".

BULK

INSERTtbl_ASX_Data_temp

FROM

'M:\Data\ASX\Import\Test.txt'

WITH

(FORMATFILE='M:\Data\ASX\SQL\Format\Import.Fmt')

GO

12.0
7
1       SQLNCHAR       0        6      ","     1      ASXCode       SQL_Latin1_General_CP1_CI_AS
2       SQLINT         0       10      ","     2      ImportDate    ""
3       SQLFLT8        0       20     ","      3      Open          ""
4       SQLFLT8        0       20     ","      4      High          ""
5       SQLFLT8        0       20     ","      5      Low           ""
6       SQLFLT8        0       20     ","      6      Close         ""
7       SQLFLT8        0       20     "\r\n"   7      Volume        ""


Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 8:16am

Hello,

You try to read the file from drive M:; is that a local drive or a network share mapped as a drive in your profile? If it's the last one, then you can not use a drive name M:, you would have to use the UNC path instead.

And please avoid double posts: https://social.msdn.microsoft.com/Forums/en-US/e4358d83-2abf-468f-b434-5b49e3d760bf/bulk-insert-error-using-format-file-bulk-load-data-conversion-error-truncation-for-row-1-column?forum=sqlexpress

June 29th, 2015 8:30am

Hi there,

Can confirm M: drive is a local drive.

Sorry for the double post, it was by accident. When I first posted I got an error something like, the server is currently unavailable so I re-tried posting and it ended up with 2 copies.

Is there a way I can delete to 2nd copy?

Cheers

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 5:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics