我正在尝试用这个查询把CSV文件导入到MariaDB的一个表中：

load data local infile 'C:\\Users\\Kevin\\Downloads\\TCGToday.csv'
into table tcgtoday
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
ignore 1 lines;

导入时，生成的表只包含CSV文件中的偶数行。

问题似乎出在每行最后一个字段后没有作为字段结束标记的逗号。

我在另一个程序中打开该文件，进行查找替换，在每行末尾添加了一个逗号，这样就能够正确导入。

我想看看是否有办法在查询中直接导入这类文件，而不需要进行手动预处理，因为这是一个需要经常执行、想要自动化的过程。

完整的.csv文件大约有13,000行，但下面给出前几行以示格式。

TCGplayer Id,Product Line,Set Name,Product Name,Title,Number,Rarity,Condition,TCG Market Price,TCG Direct Low,TCG Low Price With Shipping,TCG Low Price,Total Quantity,Add to Quantity,TCG Marketplace Price,Photo URL
"365897","Magic","7th Edition","Counterspell","","67","C","Lightly Played","1.88","","2.7600","1.4500","0","0","3.7600",""
"8424598","Magic","Aetherdrift","Aetherdrift - Finish Line Bundle","","","","Unopened","67.14","","69.9900","69.9900","0","0","93.7500",""
"366994","Magic","Alliances","Arcane Denial (Sword)","","33","C","Lightly Played","2.01","","3.2100","1.9000","0","0","3.9400",""
"1141277","Magic","Antiquities","Mishra's Factory (Spring)","","","R","Heavily Played","37.75","","49.9200","49.9200","0","0","52.8500",""
"350571","Magic","Antiquities","Tetravus","","","R","Heavily Played","19.00","","18.8100","18.8100","0","0","26.4400",""
"354878","Magic","Apocalypse","Battlefield Forge","","139","R","Heavily Played","5.54","","7.2800","7.2800","0","0","7.7500",""

解决方案

Being a windows file, the lines terminated by '\n' should be: lines terminated by '\r\n', to have a carriage return \r + newline \n, whereas a Linux file would only need a newline \n for the lines terminated by difference.