Solved

Bulk Insert Issue

Posted on 2014-03-05
5
1,577 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:
EugeneZ 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
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: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now