使用BULK INSERT不能正确导入重音字符 [英] Accented characters not correctly imported with BULK INSERT

问题描述

我正在导入源CSV文件，我不知道源编码，我只能看到�（ANSI编码）或<$ c $当我用 Notepad ++ （ （UTF8-without-BOM encoding） .com / questions / 13953430 / how-to-get-encoding-of-cvs-file>相关问题）。

到数据库 mssql-2008 使用批量插入：

  DECLARE @bulkinsert NVARCHAR（2000）
 SET @bulkinsert = 
 N'BULK INSERT #TempData FROM'''+ 
 @FilePath + 
 N'''WITH（FIRSTROW = 2，FIELDTERMINATOR =''，''，ROWTERMINATOR =''\\\
''）'
 EXEC sp_executesql @bulkinsert 
  

然后将其复制到 table1 / code>在column1（ varchar（））中。现在，当我看到这个 table1 我看到一些？代替这些字符。

我尝试过 cast 到 nvarchar（）但它没有帮助。

当我深入了解这些字符真的是与我们同时下载的链接的支持，我看到字符é，ä，å等。

我会使用替换来修复数据，但我需要做一些丑陋的代码，看看单个模式的单词和替换，这似乎很难。 >

数据库/表排序规则： SQL_Latin1_General_CP1_CI_AS
column1（Varchar（80））

我可以将这些字符更改为类似英语的字符或原始字符，而不是？标记。

我看过 Collat​​ion和Unicode支持，这没有帮助我。我了解这对编码意味着什么，但没有提供给我操作。我已经查看了在这里的大多数职位在 stackoverflow 是的有一些帖子，但不匹配我的搜索。

我无法弄清楚问题所在。

解决方案

在评论中回答。您尝试过吗？

http://msdn.microsoft.com/en-us/library/ms189941.aspx

选项DATAFILETYPE ='widenative'

根据Esailiga的注释，文本在批量导入之前或之后被截断。我同意这听起来像CSV文件本身是单字节。 Unicode需要选项DATAFILETYPE ='widenative'。如果CSV文件是单字节，则不是魔术翻译回来。

太糟糕了é是扩展ASCII和支持SQL字符，所以更多的证据问题是在CSV。

SELECT CAST（' é'AS char（1））

注意这是作为扩展ASCII（<255）

听起来像你需要回到

在SQL中是未知的。与记事本中的相同。

I am importing a source CSV file, I don't know the source encoding and I can only see either � (ANSI encoding) or � (UTF8-without-BOM encoding) when I open a the file with Notepad++ (related question).

This file has been imported to the database mssql-2008 using bulk insert:

DECLARE @bulkinsert NVARCHAR(2000)              
  SET @bulkinsert =               
 N'BULK INSERT #TempData FROM ''' +               
@FilePath +               
N''' WITH (FIRSTROW = 2,FIELDTERMINATOR = ''","'',ROWTERMINATOR =''\n'')'              
     EXEC sp_executesql @bulkinsert 

This is then copied to the regular table1 from #tempData in a column1 (varchar()). Now when I look into this table1 I see some ? in place of those characters.

I have tried to cast to nvarchar() but it does not help.

when I digged into what those characters really are with support of the link we download at same time, I saw that the characters were é,ä,å and so on.

I would use replace to fix the data but I need to make some ugly codes and look into individual pattern of words and replace, so seems difficult.

database/table collation: SQL_Latin1_General_CP1_CI_AS column1(Varchar(80))

Can I change these characters to English-like characters or the original characters instead of ? marks.

I have looked at Collation and Unicode Support which did not help me. I understood what it means about encoding but did not supply me with what to do. I have looked into most of the posts here in stackoverflow yes there are some posts about it but did not match my search.

I am unable to figure out where the problem lies.

解决方案

It was answered in the comment. Did you try it?

http://msdn.microsoft.com/en-us/library/ms189941.aspx

Option DATAFILETYPE ='widenative'

Based on comment from Esailiga did the text get truncated before or after the bulk import. I agree it sounds like the CSV file itself is single byte. Unicode requires option DATAFILETYPE ='widenative'. If the CSV file is single byte the is not magic translation back.

What is too bad is é is extended ASCII and supported with SQL char so more evidence the problem is at the CSV.
SELECT CAST('é' AS char(1))
notice this works as extended ASCII (<255)

Sounds like you need to go back to the source.

The ? in SQL is unknown. Same as � in notepad.

这篇关于使用BULK INSERT不能正确导入重音字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！