Solved

Bulk Insert Issue

Posted on 2014-03-05
5
1,683 Views
Last Modified: 2014-03-07
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
Comment
Question by:Rayne
  • 3
5 Comments
 

Author Comment

by:Rayne
ID: 39907432
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
 

Author Comment

by:Rayne
ID: 39907434
is the above error after running the bulk insert sql
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 250 total points
ID: 39908720
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
 
LVL 42

Accepted Solution

by:
Eugene Z earned 250 total points
ID: 39911436
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
 

Author Comment

by:Rayne
ID: 39911872
thanks Guys
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question