Michael Friendly wrote:
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
Don't just try things in hopes of success. Look up the correct syntax in the
manual and use it. It's usually quicker and less frustrating that way.
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)'
You have a syntax error, as the message is telling you. The word "columns" is
not part of the correct syntax, so you should leave it out. See the manual for
details <http://dev.mysql.com/doc/refman/4.1/en/load-data.html>.
You also have a problem with your first column name, which I describe below.
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
Does it?
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,
You shouldn't use "key" as a column name, as it's a reserved word. It's
possible (if you quote it with backticks *every* time), but not a good idea.
Also, every column needs a type, including the primary key. Typically, you use
an unsigned, auto_increment INT column for the primary key. You probably need
something like
CREATE TABLE milecodes
( id INT UNSIGNED NOT NULL PRIMARY KEY,
year INT,
...
The manual gives the complete CREATE TABLE syntax
<http://dev.mysql.com/doc/refman/4.1/en/create-table.html> and full details on
column types <http://dev.mysql.com/doc/refman/4.1/en/data-types.html>.
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...
Well, there's the syntax. Your command line, quoted above, has
mysqlimport [OPTIONS]
It appears that you specified neither the database nor the textfile.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
