Linux Format forums

leke · Posted: Tue Dec 29, 2009 2:58 pm Post subject: MySQL BULK INSERT problem

Hi, this is the first time I've tried the BULK INSERT. I am getting this error message trying to insert a flat file into MySQL.

Bazza · Posted: Tue Dec 29, 2009 5:23 pm Post subject:

Hi leke...

It says a syntax error...

The error report says...

"
......near 'BULK INSERT TokiPonaDicTable

FROM '/home/leke/toki-pona_english.txt'

' at line 1
"

So take a look at your:-

toki-pona_english.txt

for `'`or `"` in at least the first line as this/these could be
causing the problem(s).

Something like Barry's for example may need to be something
like Barry\'s to prevent strange inverted comma errors and/or
words/characters that may cause Keyword/Esc_Character
etc errors.

Just guessing but worth a try...
_________________
73...

Bazza, G0LCU...

Team AMIGA...

johnhudson · LXF regular Joined: Wed Aug 03, 2005 2:37 pm Posts: 767

I've never used BULK INSERT and I cannot immediately find a reference to it in info mysql. However, all the references to INSERT assume that you provide a value list before you provide the data.

AFAIK the only way you can import a data file without a value list is where your table layout exactly matches that of the CSV file (when you can use LOAD DATA INFILE 'filename' . . .)

leke · Posted: Wed Dec 30, 2009 8:59 pm Post subject:

ahh, I originally googled this this problem for getting flat file info into a MySQL DB. I noticed the example used a CSV file, but thought I maybe I could use any text file if it was formatted in a usable way. It's like:
entry1 :: entry1
entry2 :: entry2
...and so on.
I noticed some of my entries are comma separated so replacing :: with , won't work, but my DB table is set up to have an id auto increment and 2 columns.

It's also interesting how you said it wasn't in the documentation. Maybe this example I found was for SQL (I have no idea if there are differences between SQL and MySQL -- I should google that too).

I'll check out LOAD DATA INFILE in the mean time and see if I can do it with this.

I also didn't think I had to escape quotation marks because i was working directly in the MySQL client. I'll give that a try too.

johnhudson · LXF regular Joined: Wed Aug 03, 2005 2:37 pm Posts: 767

The mysql default field separator in LOAD DATA INFILE is tabs; you can alter this with with FIELDS TERMINATED BY; if there are commas in the fields, you can enclose them with single or double quotes and add ENCLOSED BY.

As the mysql roadmap map says, they are moving towards ANSI SQL compliance but AFAIK they aren't there yet.