Import csv to sql bash

Question

I am trying to write a bash script to drop all tables in a database then import all csv files in a given directory. I am having a couple of issues though:

Importing 1 csv with the script gives the following error:

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BULK
INSERT paf_addresses
FROM '../sql/paf-sample.csv'
WITH
(
FIELDTERMINATOR = ' at line 1

How to tell the mysql part to run for every file in a set directory

My script is below.

#!/bin/bash

detail="propertystork"
AWK=$(which awk)
GREP=$(which grep)

TABLES=$(mysql -u $detail -p$detail $detail -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
  mysql -u $detail -p$detail $detail -e "drop table $t"
done

mysql -u $detail -p$detail -h localhost $detail <<-_END-OF-SCRIPT_

BULK
INSERT paf_addresses
FROM '../sql/paf-sample.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
_END-OF-SCRIPT_

peterm · Accepted Answer · 2014-01-09 02:23:51Z

1

The error is caused by the fact that there is no BULK INSERT statement in MySQL.

Instead there is LOAD DATA INFILE

LOAD DATA INFILE '../sql/paf-sample.csv'
INTO table_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'

answered Jan 9, 2014 at 2:23

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sofia Rose Over a year ago

I was working off this blog post from 2008 which said there was, or have I misinterpreted it?

peterm Over a year ago

You're confused BULK INSERT is for SQL Server not for MySQL which are two completely different database management systems

Sofia Rose Over a year ago

Okay thanks for your help :) that solved problem 1. any ideas on part 2?

peterm Over a year ago

The logic of the script is wrong. If you drop tables you won't be able to load data from csv files. You'll have to create tables first. That would work only if you'd have sql dump files instead of cdv files.

Collectives™ on Stack Overflow

Import csv to sql bash

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related