0

I got a problem, and after some hours of research I just want to die.

Is there a way to import lots of CSV data into one MySQL database but creating new tables with the file name of the CSV data?

Example: If I import data1.csv into db the table should be named data1 with all the data from data1.csv.

Thanks for your suggestions and answers.

ThaFlaxx
  • 71
  • 7
  • https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html – ldgorman May 23 '17 at 14:56
  • This looks as though it covers it... https://stackoverflow.com/questions/9998596/create-mysql-table-directly-from-csv-file-using-the-csv-storage-engine. Esp. csvkit seems to be able to do it. – Nigel Ren May 23 '17 at 15:04
  • @Idgorman It is not possible to import more than only one CSV data at a time in MySQL-Workbench – ThaFlaxx May 23 '17 at 15:11

1 Answers1

1

There is no built in tool/method/command/query to accomplish what you desire within MySQL alone.

What will be required is 2 parts.

1st. of course your MySQL DB where the table will be created.

2nd. some 3rd party program that can interact with your DB. Eg. (Java, JavaScript, Python, even Unix shell scripting)

Following is a sudo example of what will be needed.

What this program will have to do is relatively simple.

It will require a couple inputs:
DataBase IP, Username, Password (these can be parameters passed into your program, or for simplicity of testing hard coded directly into the program)

The next input will be your file name. data1.csv

Using the inputs the program will harvest the 'data1' name as well as the first row of the data1.csv file to name each column.

Once the program collects this info, it can Connect to the DB and run the MySQL statement for CREATE TABLE TableName (RowName1 VARCHAR(255), RowName2 VARCHAR(255), ect...)

Finally it can do a MySQL command to import he *.csv file into the newly created table. eg.

LOAD DATA LOCAL INFILE 'C:/Stuff/csvFiles/Data1.csv' 
INTO TABLE `SchemaName`.`Data1` 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

Hope this helps clear up your options an approach a little.

DarbyM
  • 1,173
  • 2
  • 9
  • 25