Importing CSV file into MySQL DB - Newbie Question

Importing CSV file into MySQL DB - Newbie Question

am 05.03.2006 20:09:41 von derek.doerr

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\bin>mysqlimport.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/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Importing CSV file into MySQL DB - Newbie Question

am 13.03.2006 23:48:21 von sheeri kritzer

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=3DMyISAM DEFAULT CHARSET=3Dlatin1;

and then made a text file like your simple1.csv and ran the following:

mysqlimport --lines-terminated-by=3D\n --fields-terminated-by=3D, --local
--user=3Droot --password=3Drootpass 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 wa=
s:

mysqlimport --fields-terminated-by=3D, --local --user=3Droot
--password=3Drootpass 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=3D'"' (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 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\bin>mysqlimport.exe
> --lines-terminated-by=3D\r --fields-terminated-by=3D, --local --user=3Dro=
ot
> 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/mysql?unsub=3Dawfief@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org