none
批量导入UTF8编码文本到SQL SERVER2008R2乱码 RRS feed

  • 问题

  • 每天有30几个TXT文本文档更新数据,每个文档数据有十几万行,编码全部为国际标准码UTF-8编码,字段分隔为“TAB”,行分隔为“0X0A”;目前导入SQL Server2008R2中做数据分析,我是用bulk insert导入。现在遇到的问题是:所有导入的行项目,只要是中文字符就是乱码,查资料说SQL不支持UTF8编码,不知道怎么转换,求助专业人员,谢谢!
    2018年3月24日 3:43

答案

  • Hi,邹建先生,谢谢您给的方法,我测试了单个文件转换代码,这个powershell代码是正确的,谢谢你。批量转换的这个代码不可行,提示如下:

    Out-File : 未能找到路径“E:\data\800\tapf\unicode\ZSD1039.TXT”的一部分。
    所在位置 行:1 字符: 93
    + dir *.txt |%{ $a,$b=$_.fullname,$_.name;write-host $a; get-content $a -encod utf8 | out-file <<<<  ".\unicode\$b" -en
    coding unicode }
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

    后来修改了".\unicode\$b",改成“.unicode$b”即可正常运行。

    以上,非常感谢邹先生的帮忙,Thanks a lot!



    • 已编辑 彬林林 2018年3月28日 8:53
    • 已标记为答案 彬林林 2018年3月28日 8:53
    2018年3月28日 1:13

全部回复

  • Take look at this one: https://blogs.msdn.microsoft.com/qingsongyao/2009/04/10/sql-server-and-utf-8-encoding-1-true-or-false/
    2018年3月24日 18:13
  • 谢谢,但是还是不知道该怎么做才能正常的去存储这些utf8编码的中文字符啊,我的表格字段类型都是nvarchar,请知悉,谢谢解惑
    2018年3月25日 10:01
  • Sql server doesn't support utf8 encoding.
    2018年3月26日 0:37
  • 官网文档很清楚地写了不支持

    https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

    2018年3月26日 1:23
  • 可以考虑写 SSIS,在这个里面是楒转换的
    2018年3月26日 1:24
  • SSIS是什么代码?
    2018年3月27日 2:25
  • Hi 彬林林,

    我之前做过一个类似的项目,将17万行的词典数据导入SQL Server,测试过很多办法包括SSIS,Import and Export Wizard,但最终总是不能很好的解决UTF-8字符的问题,最后将txt文档转换为XML导入数据库,但是步骤比较麻烦,可能需要很多手动的工作,你可以尝试一下这个方法:

    1. 将txt格式的数据进行整理,将行分隔符设为回车,字段分隔符换为其他不使用的字符或者字符串(这里推荐使用VScode使用正则表达式修改,其他很多txt编辑器都无法打开和修改十几万行数据)整理为如下格式(我使用的“|”作为分隔符):

    2. 将上面修改完的数据通过一些方法改为XML格式的数据,这里我是用的是PowerShell:

    $content = Get-Content "C:\Users\***\Desktop\dic.txt" -encoding utf8
    $result = foreach ($item in $content)
    {
        echo "<item>"
        $record = ([regex]::Matches($item,'\|.*?\|') | Select-Object -Property Value)
        echo "<record1>"
        $record[0] -replace ('\|')
        echo "</record1>"
        echo "<record2>"
        $record[1] -replace ('\|')
        echo "</record2>"
        echo "<record3>"
        $record[2] -replace ('\|')
        echo "</record3>"
        echo "<record4>"
        $record[3] -replace ('\|')
        echo "</record4>"
        echo "</item>"
    }
     ($result -replace ('@{Value=') -replace ('}') | Out-File "C:\Users\***\result.txt"

    得到以下结果,在文本的最前面添加<root>,最后添加</root>

    3. 将XML数据插入SQL Server,这里我们需要使用sp_xml_preparedocument存储过程和OPENXML函数,参考我的代码:

    CREATE TABLE XMLforImport
    (
    Id INT IDENTITY PRIMARY KEY,
    XMLData XML,
    LoadedDateTime DATETIME
    )
    
    
    INSERT INTO XMLforImport(XMLData, LoadedDateTime)
    SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
    FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;


    4. 将存入SQL Server的XML生成数据表,参考以下代码:

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
    
    
    SELECT @XML = XMLData FROM XMLforImport
    
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    
    
    SELECT * into dbo.[your_table]
    FROM OPENXML(@hDoc, 'root/item')
    WITH 
    (
    record1 [nvarchar](max) 'record1',
    record2 [nvarchar](max) 'record2',
    record3 [nvarchar](max) 'record3',
    record4 [nvarchar](max) 'record4'
    )
    
    
    EXEC sp_xml_removedocument @hDoc
    GO

    看一下结果(这个结果是之前那个17万行的词典插入数据库的)

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年3月27日 5:49
    版主
  • 你可以在此基础上将你的30个表进行自动化处理,比如批量修改成XML,批量插入数据库,每个步骤请检查一下输出结果以免出现字符问题。

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年3月27日 6:11
    版主
  • 如果转成 xml, 那似乎转编码更快速一些吧

    powershell 中可以直接用

    get-content a.txt -encoding utf8 | out-file a.unicode.txt -encoding unicode

    2018年3月27日 9:51
  • dir *.txt |%{ $a,$b=$_.fullname,$_.name;write-host $a; get-content $a -encod utf8 | out-file -encod unicode ".\unicode\$b" }
    这个是批量转
    2018年3月27日 9:58
  • Teige Gao,非常感谢耐心解答,给了我解决此问题的新思路,不胜感激!
    2018年3月28日 1:01
  • Hi,邹建先生,谢谢您给的方法,我测试了单个文件转换代码,这个powershell代码是正确的,谢谢你。批量转换的这个代码不可行,提示如下:

    Out-File : 未能找到路径“E:\data\800\tapf\unicode\ZSD1039.TXT”的一部分。
    所在位置 行:1 字符: 93
    + dir *.txt |%{ $a,$b=$_.fullname,$_.name;write-host $a; get-content $a -encod utf8 | out-file <<<<  ".\unicode\$b" -en
    coding unicode }
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

    后来修改了".\unicode\$b",改成“.unicode$b”即可正常运行。

    以上,非常感谢邹先生的帮忙,Thanks a lot!



    • 已编辑 彬林林 2018年3月28日 8:53
    • 已标记为答案 彬林林 2018年3月28日 8:53
    2018年3月28日 1:13