BULK INSERT is not working correctly

Question

I used bulk insert into SQL Server Management Studio 2008 R2, 10 words from a text UTF-8 file, into single column.

However, the words do not appear correctly, I get extra space in front of some words.

Note: None of the answers have solved my problem, so far. :(

At worst just trim the result if your data integrity seems fine other than additionnal spaces. — plalx, Sep 25 '13 at 02:56
@Szymon UTF-8, check the link in the question, you will find all the information. — Hasan Yılmaz, Sep 25 '13 at 03:31

score 0 · Answer 1 · answered Sep 25 '13 at 03:05

0

This issue may occur if you are not using the correct collation (language settings). You need to use the appropriate collation in order to display your data in the correct format. See the link http://technet.microsoft.com/en-us/library/ms187582(v=sql.105).aspx for more details.

answered Sep 25 '13 at 03:05

Honorificabilitudinitas

300
1
2
10

Check the encoding of your file. – Honorificabilitudinitas Sep 25 '13 at 03:28
column is nvarchar(50), and file is UTF-8, I still get the same error. – Hasan Yılmaz Sep 25 '13 at 03:37

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Look at this post How to write UTF-8 characters using bulk insert in SQL Server?

Quote: You can't. You should first use a N type data field, convert your file to UTF-16 and then import it. The database does not support UTF-8.

Original answers

look at the encoding of youre text file. Should be utf8. If not this could cause problems.

Open with notepad, file-> save as and choose encoding

After this try to import as a bulk

secondly, make sure the column datatype is nvarchar and not varchar. Also see here

edited May 23 '17 at 12:20

Community

1
1

answered Sep 25 '13 at 03:16

lordkain

3,061
1
13
18

a and set the columnn datatype to nvarchar instead of varchar – lordkain Sep 25 '13 at 03:43
@Iordkain [Click here to see the screenshot. It already is.](http://i.stack.imgur.com/4Yueb.png) – Hasan Yılmaz Sep 25 '13 at 03:47

score 0 · Answer 3 · answered Sep 25 '13 at 03:22

0

You can also try using a different row terminator:

bulk insert table_name
from 'filename.txt' WITH (ROWTERMINATOR='\n')

answered Sep 25 '13 at 03:22

Szymon

42,577
16
96
114

Can you also try different `DATAFILETYPE` and `CODEPAGE` options as described in here: http://technet.microsoft.com/en-us/library/ms188365(v=sql.100).aspx – Szymon Sep 25 '13 at 03:40

BULK INSERT is not working correctly

3 Answers3