mysql includes_MySQL在制表符分隔文件（LOAD DATA INFILE）上包含逗號

here is a peculiar scenario: I am trying to import a tab delimited file in to mysql which has for some rows with prices column above 1,000. When I finish import, the values after the comma are excluded and I end up with just 1 in my table. Tried in both Linux and Windows environment with same results.

這是一個特殊的場景:我正在嘗試將制表符分隔文件導入到mysql中,其中某些行的價格列高於1,000。當我完成導入時,逗號后面的值被排除在外,我最終在表格中只有1。在Linux和Windows環境中都嘗試過相同的結果。

For example, if row 1, column 1 in a tab delimited file contains value 1,564.50 I end up with 1 in my row 1 column 1 in my MySQL table. this is the statement I am using.

例如,如果第1行,制表符分隔文件中的第1列包含值1,564.50,我在MySQL表的第1行第1列中以1結尾。這是我正在使用的聲明。

LOAD DATA LOCAL INFILE 'blah.txt'

INTO TABLE `table1`

FIELDS TERMINATED BY '\t'

OPTIONALLY ENCLOSED BY ''''

LINES TERMINATED BY '\n';

So my question is 1. can someone confirm this or how I can fix this behavior if I am missing something on my end. 2. If this is a mysql bug what are my options for a work around to importing these values properly till mysql fixes it.

所以我的問題是1.如果我遺漏了一些東西,有人可以確認這個或如何解決這個問題。 2.如果這是一個mysql錯誤,我可以選擇正確導入這些值,直到mysql修復它。

1 个解决方案

#1

2

The default delimiter of Windows is comma. Change it with another character and match it with your source data.

Windows的默認分隔符是逗號。使用其他字符更改它並將其與源數據匹配。

To change default delimiter:

要更改默認分隔符:

Go to the Control Panel --> Regional and Language Options (or Date, Time and Regional Options --> Regional and Language Option on some machines) --> click the Customize button. In the dialog box that opens you will see and option for "List separator" in which you can change the comma to a different character --> click Apply then Ok and then Apply again. Now when you go into Excel and choose Export as CSV the new character will be used as the delimiter.

轉到“控制面板” - >“區域和語言選項”(或“日期,時間和區域選項 - >某些計算機上的區域和語言選項”) - >單擊“自定義”按鈕。在打開的對話框中,您將看到“列表分隔符”和選項,您可以在其中將逗號更改為其他字符 - >單擊“應用”,然后單擊“確定”,再單擊“應用”。現在,當您進入Excel並選擇“導出為CSV”時,新字符將用作分隔符。