Import CSV file to mariadb

Question

Lately, I am facing problems importing from CSV files. I am using

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

I am using this command

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;

After searching and trying I found that I can only load File from tmp folder. i.e.

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

But it didn't work on other paths.

And secondly, I found that even If the CSV file is present in tmp folder; If it contains a lot of fields then again MariaDB would fail to load. The main problem is that LOAD DATA command does not give any type of error or even warning; except if the file does not exist. Other than that nothing is shown. And nothing is imported.

I only succeeded to import very simple CSV from tmp folder

What I Suspected is that

1. MariaDB had been updated and in this new version there are some flags or configuration options that prohibit MariaDB from importing CSV files from other than tmp folder and

2. MariaDB would fail to load CSV because of some unknown problem, Maybe some special character (which I made sure nothing is in there).

3. There must be some option that makes MariaDB produce verbose error and warning log. Which I didn't know. Except for /var/log/mysql/error.log file. which does not contain any info containing failed to load CSV.

Any help would be appreciated.

Below is the first record of CSV. Actual CSV contains 49 fields and 1862 records (but the below sample contains only one record)

"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

BTW am NON-Technical :-O

While using Mariadb version 10.5.13-3.12.1 am able to import CSV files into Tables have set up.

Except with dates,

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

There am still struggling to import text-format-dates AND to convert text-dates into the (YYYY-MM-DD) date format.

end.