I've just started trying to use mysql (debian/linux
4.0.24_Debian-10sarge1-log), which I'm finding quite frustrating.
I have a bunch of .csv files to try to import. They all have a first
line containing field names.
When I try load data ...
I get errors no matter what I try
mysql> use milestone;
Database changed
mmysql> load data local infile 'categories/milecodes.csv' into table
milecodes
-> fields terminated by ',' enclosed by '"' ignore 1 lines
-> columns (key,year,where,content,form,itemform,itemcontent)
-> ;
ERROR 1064: 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 'columns (key,year,where,content,form,itemform,itemcontent)' at
m
mysql> load data local infile 'categories/milecodes.csv' into table
milecodes
-> fields terminated by ',' enclosed by '"' ignore 1 lines
-> ;
ERROR 1146: Table 'milestone.milecodes' doesn't exist
mysql> drop table if exists milecodes;
Query OK, 0 rows affected (0.00 sec)
mysql> create table milecodes
-> (key primary key,
-> year int,
-> where enum('Eur', 'NAmer', 'Other'),
-> content char,
-> form char,
-> itemform char,
-> itemcontent char
-> );
ERROR 1064: 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 'primary key,
year int,
where enum('Eur', 'NAmer',
mysql>
I get no more joy from mysqlimport. What is wrong?
% mysqlimport --force --local --ignore-lines=1 --fields-terminated-by
',' --fields-enclosed-by '"'
--columns=key,year,where,content,form,itemform,itemcontent
mysqlimport Ver 3.4 Distrib 4.0.24, for pc-linux-gnu (i386)
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.
Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
--character-sets-dir=name
Directory where character sets are
--default-character-set=name
Set the default character set.
-c, --columns=name Use only these columns to import the data to.
Give the
column names in a comma separated list. This is
same as
giving columns to LOAD DATA INFILE.
-C, --compress Use compression in server/client protocol.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'
-d, --delete First delete all rows from table.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-f, --force Continue even if we get an sql-error.
-?, --help Displays this help and exits.
-h, --host=name Connect to host.
-i, --ignore If duplicate unique key was found, keep old row.
--ignore-lines=# Ignore first n lines of data infile.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-L, --local Read all files through the client
-l, --lock-tables Lock all tables for write.
--low-priority Use LOW_PRIORITY when updating the table
-p, --password[=name]
Password to use when connecting to server. If
password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection.
-r, --replace If duplicate unique key was found, replace old row.
-s, --silent Be more silent.
-S, --socket=name Socket file to use for connection.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
character-sets-dir (No default value)
default-character-set (No default value)
columns
key,year,where,content,form,itemform,itemcontent
compress FALSE
delete FALSE
fields-terminated-by ,
fields-enclosed-by "
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
force TRUE
host localhost
ignore FALSE
ignore-lines 1
lines-terminated-by (No default value)
local TRUE
lock-tables FALSE
low-priority FALSE
port 3306
replace FALSE
silent FALSE
socket /var/run/mysqld/mysqld.sock
user friendly
verbose FALSE
euclid: milestone/Private %
--
Michael Friendly Email: [EMAIL PROTECTED]
Professor, Psychology Dept.
York University Voice: 416 736-5115 x66249 Fax: 416 736-5814
4700 Keele Street http://www.math.yorku.ca/SCS/friendly.html
Toronto, ONT M3J 1P3 CANADA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
