Linux/MySql/DatenImport

http://dev.mysql.com/doc/mysql/de/mysqlimport.html

der Pfad des files data.txt war unter /var/lib/mysql/test/ ???

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE mail_adressen FIELDS TERMINATED BY ';' ;

mysql> select id,adresse,valid from mail_adressen where adresse like '%grim%';
+------+-------------------------------------+-------+
| id   | adresse                             | valid |
+------+-------------------------------------+-------+
|  331 | grimsmann@aol.com                   |  NULL |
|  947 | grimm@rewi.hu-berlin.de             |  NULL |
| 2380 | grimmer@hrz.uni-kassel.de           |  NULL |
| 2620 | christian.grimm@fh-weihenstephan.de |  NULL |
| 3674 | grimm@warlies-grimm.de              |  NULL |
| 3675 | warlies@warlies-grimm.de            |  NULL |
+------+-------------------------------------+-------+
6 rows in set (0.02 sec)

http://dev.mysql.com/doc/mysql/en/load-data.html

LOAD DATA INFILE '/home/macdet/anja/preps.cvs' INTO TABLE preps FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r";

mysqlimport --local test imptest

mysqlimport --local --fields-terminated-by ";" test preps.cvs

die dinge die am häufigsten verabreicht wurden

select sum(Preis) as power , Preis,f,count(*) as anzahl from preps group by Preis,f having count(*)>1 order by anzahl;

http://dev.mysql.com/doc/mysql/en/mysql-command-options.html

http://dev.mysql.com/doc/mysql/en/order-by-optimization.html

http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html

Posted by [name withheld] on October 21 2004 1:43pm [Delete] [Edit]

For Fixed-Row input, FIELDS TERMINATED BY and FIELDS ENCLOSED BY being null means they must be explicitly set to , otherwise the defaults will be assumed and only the first field will be read. (Obvious perhaps, but overlooked by some.) Posted by jparm56 on January 29 2005 10:49pm [Delete] [Edit]

To eliminate ERROR 13: Can't get stat of "filename.txt" add local: mysql> load data local infile "filename.txt" into table tblname; Posted by [name withheld] on March 4 2005 12:16am [Delete] [Edit]

The syntax text above: "FIELD [TERMINATED BY '\t']" appears to disallow multi-character field terminators, such as "|<ft>|". However, multi-character field terminators are indeed allowed by LOAD DATA INFILE.

Warnings

Bomb Diggy <techguy_chicagoyahoo.com> wrote: > I can't seem to get ahold of the 6 warnings being > generated when I import a csv file. > > My ~/.my.cnf file says this: > > [client] > fields-terminated-by=, > fields-enclosed-by=\" > #debug=log.txt > #debug=d,info,error,query,general,where:1,load.trace > debug=warning,load.trace > > Nothing I do in the 'debug' param seems to actually > output any information. > > Here's my command: > > mysqlimport -v -h [host] [database] > [table_and_file_name].csv -u [username] -p > > Here's my version info: > > mysqlimport Ver 3.4 Distrib 4.0.18, for pc-linux > (i686) > > Here's my output to STDOUT/STDERR: > > Connecting to [hostname] > Selecting database [database] > Loading data from LOCAL file: [table_and_file_name.csv > into [table_name] > [database].[table_name]: Records: 3 Deleted: 0 > Skipped: 0 Warnings: 6 > Disconnecting from [hostname] > > I want to see those 6 warnings. I've tried > command-line '--debug=foo' as well. > > Thanks. > >

--debug=foo'

http://linuxcommand.org/man_pages/mysql1.html

http://dev.mysql.com/doc/mysql/en/mysql-command-options.html

http://www.codeproject.com/Purgatory/mygroupconcat.asp

http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

http://www.little-idiot.de/mysql/mysql-110.html

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Between quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...

I usually run this command from the folder containing the YOUR_TABLE.csv file.

If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)

If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""

http://dev.mysql.com/doc/mysql/en/mysqlimport.html

If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows command/DOS prompt, try the following:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Between quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...

I usually run this command from the folder containing the YOUR_TABLE.csv file.

If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)

If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""

I hope this helps someone,

LOAD DATA INFILE '/home/macdet/anja/preps.cvs' INTO TABLE preps FIELDS TERMINATED BY ';';

LOAD DATA INFILE '/home/macdet/anja/preps.cvs' INTO TABLE preps FIELDS TERMINATED BY ';' ignore 5 lines;

Quick checks after you've got some warnings (in MySQL 3.x it is not possible to see warning descriptions)

1) Find out if there are records Skipped.

2) Mostly when you got a warning and the record is Inserted correctly it is because of one of the following reasons:

A) Integers in the load_data_file.txt are nothing, i.e: 2 commas ,, (nothing) on a Integer place will give a warning, correct these to: ,0, Even when setting a default value of 0 AND Allow_NULL for the Integer field will still give you warnings when inserting ,, (nothing). However, the data is imported

A carriage return at the end in the load_data_file.txt will give you as many warnings as columns. I.e.: When you have a carriage return at the end of your file and you want to use 'load data' to insert it in a table of 34 columns, you will get 34 warnings!

C) When you have a PRIMARY Key (i.e. Integer) in your table (even with Auto-increment!) and insert a NULL value, it will give you a warning. This is because a primary key (Auto-Increment) field can never be ALLOW_NULL. However the data is imported.

One other important thing to know: Warnings are on a PER field/record basis, NOT max. 1 per line. So even if you have a text file with 1 line in it, you can still have 10 warnings when you do a 'load data'.

Note: For example for exporting/importing FROM DBII. Old DBII systems don't know the NULL Value, so you can only export a "NULL" instead (with quotes). However MySQL will insert NULL as well as "NULL" for both Integer,Character and Date fields. It won't even show you a warning