CODEWITHSUNDEEP
mysqlsqlbashcsvzsh
Sofia Rose
Sofia Rose

Reputation: 213

Import csv to sql bash

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:

  1. 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

  2. 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_

Upvotes: 1

Views: 6320

Answers (1)

peterm
peterm

Reputation: 92795

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'

Upvotes: 1

Related Questions