Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Bulk Insert Issue

Posted on 2014-03-05
5
Medium Priority
?
1,799 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 1000 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 43

Accepted Solution

by:
Eugene Z earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 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