Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1843
  • Last Modified:

Bulk Insert Issue

Hello,

Here is the code
BULK INSERT [Sandbo]..[aaaLE] FROM '\\some\fg\cBCopy.csv'
WITH (

  FIELDTERMINATOR = '\t',
  ROWTERMINATOR = '\n'
);
GO

I am trying to bulk insert a csv file (tab delimited) that has all text columns and one (number) column at the very end.

What’s the issue…
0
Rayne
Asked:
Rayne
  • 3
2 Solutions
 
RayneAuthor Commented:
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 16 (myNumberDSet).
0
 
RayneAuthor Commented:
is the above error after running the bulk insert sql
0
 
regmigrantCommented:
without seeing the actual data this is very difficult to troublesheet but reading the error message I would guess that (at least) row 1 column 16 has a non-ascii character. If the data is valid (ie: non-ascii is allowed) then you may get it working with a codepage option which you would need to set appropriately for the characters in the set

The following is from MS website
In SQL Server 2005 and later versions, BULK INSERT enforces new data validation and data checks that could cause existing scripts to fail when they are executed on invalid data in a data file.
 
CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32
0
 
Eugene ZCommented:
what is your sql server version\edition\sp?

if you use sql 2008

check your csv file:
SQL Server 2008 does not support UTF-8 - you need to convert it to
UTF-16 encoding  (just save file "with encoding 16 and try to run your code again)

check the reported bug:

BULK INSERT and BCP does not recognize codepage 65001
https://connect.microsoft.com/SQLServer/feedback/details/370419/

https://connect.microsoft.com/SQLServer/feedback/details/321839/msft-edw-tapblock-bcp-doesnt-recognize-field-terminator-on-code-page-65001
0
 
RayneAuthor Commented:
thanks Guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now