2011-11-13 21:32

Here is the little sh script I made to do that. The LOAD DATA INFILE command exists but is not capable of creating the table structure.

#!/bin/sh

MYSQL_ARGS="--defaults-file=/etc/mysql/debian.cnf"
DB="mbctest"
DELIM=";"

CSV="$1"
TABLE="$2"

[ "$CSV" = "" -o "$TABLE" = "" ] && echo "Syntax: $0 csvfile tablename" && exit 1

FIELDS=$(head -1 "$CSV" | sed -e 's/'$DELIM'/` varchar(255),\n`/g' -e 's/\r//g')
FIELDS='`'"$FIELDS"'` varchar(255)'

#echo "$FIELDS" && exit

mysql $MYSQL_ARGS $DB -e "
DROP TABLE IF EXISTS $TABLE;
CREATE TABLE $TABLE ($FIELDS);

LOAD DATA INFILE '$(pwd)/$CSV' INTO TABLE $TABLE
FIELDS TERMINATED BY '$DELIM'
IGNORE 1 LINES
;
"

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

(See comment: “Posted by John Swapceinski on September 5 2011 5:33am”)

2011-11-13 21:32 · Tags: , ,

3 Comments

  1. thanks for this tutorial.. i need it for my project

    Reply

  2. Thank you for this tutorial, I am using you method to import a larger csv with a long seperate header file, this will save me some time.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>