I am looking for a way to insert all my csv files into MySQL without having to write several LOAD DATA INFILE statements.
I have many csv files I need to insert and the files themselves are very large.
I have tried *.csv, but this does not work.
-
For situations like this I would use python library d6tstack which makes it easier to deal with multiple files, data schema changes, separators, null values etc. See d6tstack SQL examplescitynorman– citynorman2018-10-14 23:03:51 +00:00Commented Oct 14, 2018 at 23:03
Add a comment
|
2 Answers
See these previous answers:
https://stackoverflow.com/questions/8538995/how-to-import-multiple-csv-files-into-a-mysql-database
https://stackoverflow.com/questions/6552042/mysql-loading-multiple-files-into-a-table
Essentially, no. But it's easy to script this to get a similar result (e.g. one command to import multiple files using a loop).
CW answer originally left in the question by its author
I wrote a script which worked:
for f in /var/lib/mysql/[insert name of database here]/*.csv
do
mysql -e "LOAD DATA INFILE '"$f"' INTO TABLE [nameoftable]
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"
-u [username] --password= [password] [name of database]
echo "Done: '"$f"' at $(date)"
done