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.
| 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. |
 |
|
|
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. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-18 : 09:03:20
|
| What is the collation of the column in SQL? |
|
|
|
skc40
Starting Member
34 Posts |
Posted - 2014-11-18 : 11:17:13
|
| It's SQL_Latin1_General_CP1_CI_AS. |
|
|
|
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? |
|
|
|
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 |
|
|
|
skc40
Starting Member
34 Posts |
Posted - 2014-11-21 : 12:48:34
|
| process handled internally.Thanks gbritton!! |
|
|
|
|
|
|
|
|
Bulk insert txt file with unicode character