Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Bulk insert txt file with unicode character

Author  Topic 

skc40
Starting Member

34 Posts

Posted - 2014-11-17 : 12:39:09
Hi, I am having an issue related to Bulk insert.

file is a fixed length text file (LF). File length=100.

Bulk insert works perfectly fine except that there are unicode character and adds extra position in the file when importing data.

I have tried using below options, still there's formatting issue.
Datafiletype=widechar,
CODEPAGE = 'ACP' | 'OEM' | 'RAW' | 'code_page'
,
DATAFILETYPE =
'char' | 'native'| 'widechar' | 'widenative' }

unicode character in test.txt file is é and this is how records are imported in sql server table é

Is there a way to suppress the imported value??

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 15:58:51
What do you mean by "suppress"? Do you mean delete the character or replace it with something else?

In any case, your best bet is to use BCP to import the data to a staging table, then SQL to populate the target table from the staging table, while converting the data as you wish.
Go to Top of Page

skc40
Starting Member

34 Posts

Posted - 2014-11-17 : 17:28:37
by suppress, I meant same value!! sorry for not being clear.

If import file has é, then I would like to show it as é in sql table.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 09:03:20
What is the collation of the column in SQL?
Go to Top of Page

skc40
Starting Member

34 Posts

Posted - 2014-11-18 : 11:17:13
It's SQL_Latin1_General_CP1_CI_AS.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 11:39:30
Is the SQL Column NVARCHAR? Also, what is the encoding of the source file?
Go to Top of Page

skc40
Starting Member

34 Posts

Posted - 2014-11-18 : 12:41:48
yes, nvarchar.

Changed column to varchar, char, no luck.

It looks like an encoding issue at the source file, but have not received confirmation about the type of encoding used.

Will post later.

Thanks
Go to Top of Page

skc40
Starting Member

34 Posts

Posted - 2014-11-21 : 12:48:34
process handled internally.

Thanks gbritton!!
Go to Top of Page
   

- Advertisement -