将CSV文件导入mariadb

Question

最近，我面临从CSV文件导入的问题。我在用

MariaDB   : 10.3.32-MariaDB-0ubuntu0.20.04.1
On Ubuntu : Ubuntu 20.04.3 LTS

我正在使用以下命令

LOAD DATA LOCAL INFILE '/path_to_file/data.csv' INTO TABLE tab
FIELDS TERMINATED BY ',' 
 OPTIONALLY ENCLOSED BY '"'
 ESCAPED BY '"'
 LINES TERMINATED BY '\n'
IGNORE 1 LINES;

经过搜索和尝试，我发现我只能从tmp文件夹加载文件。即

SELECT load_file('/tmp/data.csv');

但它在其他道路上行不通。

其次，我发现即使CSV文件存在于tmp文件夹中；如果它包含许多字段，那么MariaDB也将无法加载。主要问题是LOAD DATA命令不提供任何类型的错误或甚至警告；除非文件不存在。除此之外，什么都没有显示出来。也没有进口的东西。

我只成功地从tmp文件夹导入了非常简单的CSV。

我怀疑的是

1. MariaDB已经更新，在这个新版本中有一些标志或配置选项禁止MariaDB从tmp文件夹和

以外的其他地方导入CSV文件。

1. MariaDB将无法加载CSV，原因可能是一些未知的问题，可能是一些特殊的字符(我确定这里没有任何内容)。

1. 必须有一些选项可以使MariaDB生成详细的错误和警告日志。但我不知道。除了/var/log/mysql/error.log文件。，它不包含任何包含未能加载CSV.

的信息。

任何帮助都将不胜感激。

下面是CSV的第一个记录。实际CSV包含49个字段和1862条记录(但下面的示例只包含一条记录)

"S.No","Training Code","Intervention Type (NRM/Emp. Skill 
Training)","Training Title/Activity","Start Month","Ending 
Month","No. of Days Trainings","Start Date Training","End Date 
Training","Name of Person","Father 
Name","CNIC","Gender","Age","Education","Skill Level","CO Ref 
#","COName","Village Name","Tehsil Name","District","Type of Farm 
production","Total Land (if applicable)","Total Trees (if 
applicable)","Sheeps/goats","Buffalo/Cows","Profession","Person's 
Income Emp. Skill (Pre-Intervention)","Income from NRM (Pre- 
Intervention)","HH Other Sources of Income","Total HH Income","Type 
of Support provided","Tool Kit/Inputs Received or Not","Date of 
Tool Kit receiving","Other intervention , like exposure market 
trial, followup support, Advance Training etc","Production (Pre- 
Intervention)","Production (Post-Intervention)","Change in 
Production","Unit (kg, Maund,Liter, etc)","Income gain from 
production (Post-Intervention)","Change in Income (NRM)","Income 
gain by Employment -Emp.Skill (Post Intervention)","Change in 
Income (Emp. Skill)","Outcome Trend","Employment/Self- 
Employment/Other","Outcome Result","Remarks","Beneficiaries Contact 
No.","Activity Location"
1,"AUP-0001","NRM","Dates Processing & 
Packaging","Sep/2018","Sep/2018",2,"25/Sep/2018","26/Sep/2018", 
"Some name","Barkat Gul",1234567891234,"Male",34,"Primary","Semi- 
Skilled","AUP-NWD-073","MCO Haider Khel Welfare Committee","Haider 
Khel","Mir Ali","North 
Waziristan","Dates",,20,,,"Farming",,5000,"Farming",5000,"Training, 
Packaging Boxes","Yes","10/10/2018",,180,320,140,"Kg",8000,3000,,,
"Positive","Self Employed","Value addition to the end product 
(Packaging increase the Price per KG to 25%)",,,"Field NW"

Answer 1

顺便说一句是非技术的:-O

在使用Mariadb版本10.5.13-3.12.1时，可以将CSV文件导入表中。

除了日期之外，

https://dba.stackexchange.com/questions/283966/tradedate-import-tinytext-how-to-show-date-format-yyyymmdd-of-20210111-or-2021?noredirect=1#comment555600_283966

仍然难以导入文本格式-日期和将文本日期转换为(YYYY DD)日期格式。

结束。