Hi Derek,
You never gave us a SHOW CREATE TABLE simple1, which would have
helped. To replicate your problem, I did the following, on a linux
box (it looks like you're using Windows), using mysql
5.0.18-standard-log:
CREATE DATABASE cars;
use cars;
CREATE TABLE `simple1` (
`one` char(10) default NULL,
`two` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
and then made a text file like your simple1.csv and ran the following:
mysqlimport --lines-terminated-by=\n --fields-terminated-by=, --local
--user=root --password=rootpass cars simple1.csv
and got:
cars.simple1: Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
I'd guess I got a warning and you didn't because of the version of
MySQL. when I checked out the table, indeed I found that I had only
one row.
So I tried again, figuring that the binary knew what the end of the line was:
mysqlimport --fields-terminated-by=, --local --user=root
--password=rootpass cars simple1.csv
cars.simple1: Records: 3 Deleted: 0 Skipped: 0 Warnings: 1
aha! 3 records this time!
select * from simple1;
+--+-+
| one | two |
+--+-+
| test1 | test2 |
| test11 | test3 |
| | NULL|
+--+-+
3 rows in set (0.00 sec)
I can guess that I got a warning because there was no comma-separated
list on the 3rd line, so it put the first value (blank) into the first
field of the 3rd record, but had nothing to put in the 2nd value, so
it put NULL.
You don't need double quotes in the file. However, if you are
importing someone else's file or a previous export, you can put a
--fields-enclosed-by='' (that is, single-quote double-quote
single-quote) tag to tell mysqlimport that it shouldn't look at the
double quotes.
hope this helps!
Sheeri
On 3/5/06, Derek Doerr [EMAIL PROTECTED] wrote:
I have a CSV file that I want to import into a MySQL DB table. The file
contains 15 fields. The able to import into will contain those same 15
fields, plus an auto-generated Primary Key fields.
Since this is the first time I'm working with mysqlimport, I created a
small test table to start with - simple1, containing two varchar
fields - field1 and field2.
I'm trying to import a small test file into simple1, to get the hang of
using mysqlimport. The test file contains 2 records and 3 lines - the
3rd line is blank:
test1,test2
test11,test3
I run the import as follows:
C:\Program Files\xampp\mysql\binmysqlimport.exe
--lines-terminated-by=\r --fields-terminated-by=, --local --user=root
cars c:\dev\test\simple1.csv
The import report shows: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
The data that ends up in the DB, however, only contains 1 record:
\test1\,\test2\
\test11\
(1) how do I get mysqlimport to import both records, properly parsing
the fields - two fields per record?
(2) do I need to wrap the imported records in double-quotes? Why do the
double-quotes show up in the MySQL DB table?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
