2

I want to import CSV data into mysql database.

Additionally, I dont want to create a table first and then import. That means, if my csv file name is test.csv, and its contents are like 

customer_ID,name
10000000,name1
10000001,name2

I should be execute 

mysql> use database {database_name}
mysql> {some_command} test.csv

And I should be able to create a table with name test and it having headers customer_ID and name and it should be populated according to contents of test.csv. Specifically some_command is the magic bullet I am looking for.

Any idea how this can be done in MySQL. While I searched I did not find a way to import CSV data without creating a table first.

Improve this question
2
  • Use Phpmyadmin Which provides the same functionality as you require Commented Mar 28, 2014 at 5:31
  • I am sorry I have never used Phpmyadmin before so I did not try it before. As I go through the documentation, I see that even after using Phpmyadmin, there is no way I can script my requirements. i.e I have to drag drop a CSV file somewhere which I see as a impediment to automatic things. Say I have 20 CSV files, I have to do this manually for each of the files. I am looking for something more basic and scriptable. Commented Mar 28, 2014 at 8:46

2 Answers 2

Reset to default
5

There is no way to do this with MySQL own tools. If you look closer it makes sense that you have to create your table first, you have to specify datatypes for the fields of your csv file, possible indexes and so on. MySQL can´t predict which datatype you want to use, so here is some manual work to do.

On the other hand you can use external scripts to archive what you want, a quick and dirty example would be:

#!/bin/bash
# create table for import
head -1 test.csv | awk '{split($0,f,","); printf "create table test (%s INT, %s VARCHAR(50));",f[1],f[2]}' | mysql -u USER -pPASS SCHEMA
# import csv
mysql -uUSER -pPASS SCHEMA -e "LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE testcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES"

Above "script" lefts plenty of room for optimization, it should only give you a basic idea how to archive what you need with common Linux tools.

Improve this answer
3
  • I understand that it may do the job for me. But it is like scripting what we are doing manually. Main point of concern is create table test (%s INT, %s VARCHAR(50));. Say if there are three header column instead of 2, then this script would not work. I hope I am clear in what I am looking for. Given any csv file, I should be able to generate a table by simply reading the file. Commented Mar 28, 2014 at 8:39
  • 2
    Yes, i totally understand what you mean here, but there is no way you can do this with mysql alone. E.g. how can mysql know which datatype would be the appropiate one? A csv file can be seen as a datafile, what you lack is the proper schema defintion for the table. You can of course script this dynamically, but there is no way to do this without external tools. Commented Mar 28, 2014 at 8:48
  • Second that. You have to issue a create table... somehow, so that the .frm file gets created. I'm not sure though, if the load data... really is necessary. If you specify engine=csv in your create table... statement, you can just put the csv file next to the frm file (same name of course), if I remember correctly. Better check that again here: dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html Commented Mar 28, 2014 at 9:03
2

I've used both MS Access and Toad to create table structure during data file import. Both tools will give you the chance to tweak the column size/types during the import process. Toad will create the table directly on MySQL, when I used MS Access, I imported to a local table, then exported to MySQL via ODBC connection.

Big time saver if your input file contains header rows that can be used as column names for your newly created table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.