我的平面文件应该是UCS-2，但是我不能导入MySQL数据库。

Question

我有20个管道分隔的文本文件，我想将它们转换成一个MySQL数据库。数据附带的手册上写着

由于难以显示标准拉丁字符集以外的字符的数据，所有数据都使用Unicode (UCS-2)字符编码进行显示。所有CSV文件都是使用商业标准构造的，首选格式是管道分隔符(“AC.26”)和回车+行提要(CRLF)作为行结束符。

我在Win 8.1上使用了MySQL Workbench 6.2.5，但是手册提供了示例Server脚本来创建20个表。这是一个。

/****** Object: Table [dbo].[tbl_Company_Profile_Stocks] Script Date:
12/12/2007 08:42:05 ******/
CREATE TABLE [dbo].[tbl_Company_Profile_Stocks](
[BoardID] [int] NULL,
[BoardName] [nvarchar](255) NULL,
[ClientCompanyID] [int] NULL,
[Ticker] [nvarchar](255) NULL,
[ISIN] [nvarchar](255) NULL,
[OrgVisible] [nvarchar](255) NULL
)

我对MySQL进行了如下调整。

/****** Object: Table dbo.tbl_Company_Profile_Stocks Script Date:
12/12/2007 08:42:05 ******/
CREATE TABLE dbo.tbl_Company_Profile_Stocks
(
BoardID int NULL,
BoardName varchar(255) NULL,
ClientCompanyID int NULL,
Ticker varchar(255) NULL,
ISIN varchar(255) NULL,
OrgVisible varchar(255) NULL
);

因为手册上说平面文件是UCS-2，所以我在创建它时将dbo模式设置为UCS-2 default collation。这是很好的AFAIK。是LOAD INFILE失败了。由于数据是用CRLF行结尾的管道分隔的，所以我尝试如下。

LOAD DATA LOCAL INFILE 'C:/Users/Richard/Dropbox/Research/BoardEx_data/unzipped/Company_Profile_Stocks20100416.csv'
INTO TABLE dbo.tbl_company_profile_stocks
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

但是在本例中，行被导入，消息是0 row(s) affected Records: 0 Deleted: 0 Skipped: 0 Warnings: 0。所以我试着用\n行结尾代替。这会导入一些内容，但是我的整数值变成了零，文本变得非常宽。消息是14121 row(s) affected, 64 warning(s): 1366 Incorrect integer value: <snip> Records: 14121 Deleted: 0 Skipped: 0 Warnings: 28257。

如果我在“崇高文本3”中打开平面文本文件，“编码助手”包建议该文件使用UTF-16 LE with BOM编码。如果我在创建UTF-16 default collation模式时使用dbo重复上面的内容，那么我的结果是相同的。

我怎么才能解决这个问题？编码让我抓狂！

Answer 1

可能主要的问题是LOAD数据需要这个子句(请参阅参考文献)：

CHARACTER SET ucs2

万一这还不够.

• 你能得到一些csv文件的十六进制转储吗？我想确定它真的是ucs2。(ucs2是非常罕见的。)通常文本在utf8中传输。)如果当您将文本粘贴到此论坛时，它看起来是可读的，那么它可能是utf8。
• 在MySQL中没有"dbo“(”数据库所有者“)，只有数据库。
• 请提供SHOW CREATE TABLE tbl_Company_Profile_Stocks
• (只是一项建议)不要在表名前加上"tbl_"；它只会造成混乱而不是澄清。
• 为表提供一个PRIMARY KEY。

Answer 2

@Rick有正确的答案(即用LOAD DATA选项为CHARACTER SET设置编码)。但在我的例子中，因为MySQL不支持UCS-2不起作用。

Note 无法加载使用ucs2字符集的数据文件。

这里有一些可行的方法。最后，我使用了这个SQLite，而不是MySQL，但是最后一个解决方案应该与MySQL或任何其他接受平面文件的DB一起工作。

SQLiteStudio

在这种情况下，SQLiteStudio是最简单的解决方案。我更喜欢命令行解决方案，但是SQLiteStudio图形用户界面接受UCS-2编码和任何分隔符。这将数据保存在UCS-2中。

在Windows命令行中转换为ASCII

到ASCII的最简单的转换是在带有TYPE的Windows命令行中。

for %%f in (*.csv) do (
    echo %%~nf
    type "%%~nf.csv" > "%%~nf.txt"
)

这可能会导致特殊字符的问题。在我的例子中，它保留了单引号和双引号，这给SQLite导入带来了一些问题。这是最粗野的做法。

在Python中转换为ASCII

import codecs
import glob
import os

for fileOld in glob.glob('*.csv'):
    print 'Reading: %s' % fileOld
    fileNew = os.path.join('converted', fileOld)
    with codecs.open(fileOld, 'r', encoding='utf-16le') as old, codecs.open(fileNew, 'w', encoding='ascii', errors='ignore') as new:
        print 'Writing: %s' % fileNew
        for line in old:
            new.write(line.replace("\'", '').replace('"', ''))

这是最可扩展的方法，并将允许您更精确地控制您转换或保留哪些数据。