Mysql – Import data from a CSV file without creating a table first in MySQL

MySQL

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.

Best Answer

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.

Related Solutions

Mysql – Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after thesqldump import

I think I have a plausible explanation you find very intriguing.

When you loaded the MyISAM table, only one index got loaded into the .MYI file in a lopsided manner.

Which index would that be ? THE PRIMARY KEY. Why ?

Indexes usually use BTREEs. They are designed to collect keys into a single BTREE node and balance the BTREE internally until the BTREE is full. On the insert into the full node, the full tree node is split and the keys inside are divided.

Most often a BTREE node will split is when you load the keys in order. For proof, look at the worst case BTREE --- the balanced binary tree.

I have several posts in the DBA STackExchange where I mention how a balanced binary only has node with one key. I also mention how a leaf pages must rebalance about 45% of the time when inserting a new key. Here are those posts:

Aug 03, 2011 : When should I rebuild indexes?
Jun 28, 2012 : Benefits of BTREE in MySQL
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL?
Jun 04, 2013 : Why would increase in innodb_buffer_pool_size slow down MySQL?

My working theory is that random inserts into the table may actually make the .MYI smaller by prevent tree node splits as much as possible.

In order to make this happen, you will have to something quite unusual. Ready for this ?

ALTER TABLE generic_dummy_table_name ORDER BY datetime;

ALTER TABLE generic_dummy_table_name ORDER BY user,datetime;

That right, you can reorder the physical rows of the table. This may make the indexes a different size by inducing BTREE splits on another index.

Try reordering the table. Then, mysqldump it and reload it. You may find that the .MYI may get smaller or bigger. You cannot really predict it.

Next time, try reloading the MyISAM table with a very large bulk insert buffer.

Just run

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 1024;

then connect to mysql and reload the mysqldump.

GIVE IT A TRY !!!

MySQL Import – How to Import Multiple CSV Fields into One MySQL Field

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type:

mysql> LOAD DATA INFILE '/tmp/alarms.csv' 
       INTO TABLE alarms 
       COLUMNS TERMINATED BY ',' 
       IGNORE 1 LINES 
       (@Date, @Time, ALARM_01, PT001, TEMP_AMB) 
       SET DateTime = STR_TO_DATE(CONCAT(@Date, ' ', @Time), '%d/%m/%Y %H:%i:%s');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM alarms;
+----+---------------------+----------+--------+----------+
| id | DateTime            | ALARM_01 | PT001  | TEMP_AMB |
+----+---------------------+----------+--------+----------+
|  1 | 2014-08-14 14:23:21 |        0 | 130.00 |    20.00 |
|  2 | 2014-08-14 14:23:25 |      101 | 128.09 |    20.10 |
+----+---------------------+----------+--------+----------+
2 rows in set (0.00 sec)

Your fields and datatypes may vary slightly, with no effect on the import query.

Best Answer

Related Solutions

Mysql – Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after thesqldump import

GIVE IT A TRY !!!

MySQL Import – How to Import Multiple CSV Fields into One MySQL Field

Related Question