Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Bulk Insert Issue

Posted on 2014-03-05
5
Medium Priority
?
1,772 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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