MySQL Forums :: MySQL Workbench - Database Migration :: CSV import with mysqlimport


Advanced Search

CSV import with mysqlimport
Posted by: Scott Holland ()
Date: July 11, 2011 08:49AM

I am looking to code an import of a csv into table 'highs'

below are the first the lines of the csv I am downloading.
***************************************************************
"SYM","NAME","LAST","CHANGE","PERCENT","HIGH","LOW","VOLUME","TIME",
"AAN","Aaron's Inc.","29.16","+0.03","+0.10%","29.34","28.80","405541","07/08/11"
"ACIW","Aci Worldwide","35.94","-0.05","-0.14%","36.09","35.68","144820","07/08/11"
"ACO","Amcol International Corp.","39.08","-0.22","-0.56%","42.72","38.53","61869","07/08/11"
"ADLR","Adolor Corp.","2.63","+0.31","+13.36%","2.65","2.21","1034700","07/08/11"
"AEA","Advance America Cash Advance Centers","8.03","+0.11","+1.39%","8.05","7.78","674083","07/08/11"
"AKR","Acadia Realty Trust","21.35","+0.05","+0.23%","21.43","21.03","256784","07/08/11"
"ALKS","Alkermes","19.52","+0.29","+1.51%","20.00","19.18","1718740","07/08/11"
"AMZN","Amazon.Com","218.28","+1.54","+0.71%","218.32","213.25","3708386","07/08/11"
"ALKS","Alkermes","19.52","+0.29","+1.51%","20.00","19.18","1718740","07/08/11"
********************************************************************************

I am trying to import the data into the table highs
***********************************************************
| highs | CREATE TABLE `highs` (
`sym` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`last` double NOT NULL DEFAULT '0',
`change` double NOT NULL DEFAULT '0',
`percent` double NOT NULL DEFAULT '0',
`high` double NOT NULL DEFAULT '0',
`low` double NOT NULL DEFAULT '0',
`volume` double NOT NULL DEFAULT '0',
`time` time NOT NULL DEFAULT '00:00:00',
`id` mediumint(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
****************************************************************
When I run command
**********************************************************
mysqlimport --local --fields-terminated-by=',' --fields-enclosed-by="\"" --lines-terminated-by=\r\n -p stock_data /home/sholland/highs.csv
************************************************************
I have the following returned.
************************************************************
stock_data.highs: Records: 10 Deleted: 0 Skipped: 0 Warnings: 43
************************************************************
and below is the table. What am I doing wrong?
+----------------------------+-------------------+-------+--------+---------+-------+---------+--------+----------+-----+
| sym | name | last | change | percent | high | low | volume | time | id |
+----------------------------+-------------------+-------+--------+---------+-------+---------+--------+----------+-----+
| US Exchanges 52 Week Highs | 07_09_2011
"SYM" | 0 | 0 | 0 | 0 | 0 | 0 | 00:00:00 | 1 |
| ational Corp." | 39.08 | -0.22 | -0.56 | 42.72 | 38.53 | 61869 | 7 | 00:00:00 | 3 |
| ational" | 92.00 | 0.15 | 0.16 | 92 | 91.96 | 3226981 | 7 | 00:00:00 | 8 |
| er Corp." | 64.06 | -0.23 | -0.36 | 64.48 | 63.31 | 733768 | 7 | 00:00:00 | 58 |
| ationalinc." | 29.22 | -0.14 | -0.48 | 29.8 | 28.42 | 22019 | 7 | 00:00:00 | 35 |
| ational Inc." | 15.23 | -0.01 | -0.07 | 15.25 | 15.22 | 203437 | 7 | 00:00:00 | 71 |
| ational N.V." | 43.18 | -0.28 | -0.64 | 43.54 | 42.77 | 66091 | 7 | 00:00:00 | 13 |
| Union Company" | 42.15 | -0.12 | -0.28 | 42.53 | 41.92 | 3322909 | 7 | 00:00:00 | 39 |
| etx Holding Corp" | 38.89 | 2.9 | 8.06 | 39.53 | 34.23 | 2682527 | 7 | 00:00:00 | 60 |
| Refining" | 20.61 | 1.06 | 5.42 | 20.67 | 18.81 | 6281036 | 7 | 00:00:00 | 161 |
+----------------------------+-------------------+-------+--------+---------+-------+---------+--------+----------+-----+

Options: ReplyQuote


Subject Written By Posted
CSV import with mysqlimport Scott Holland 07/11/2011 08:49AM
Re: CSV import with mysqlimport Rick James 07/13/2011 12:58AM
Re: CSV import with mysqlimport jack albente 08/05/2011 10:14AM
Re: CSV import with mysqlimport jeni jeni 08/08/2011 12:10AM
Re: CSV import with mysqlimport jack albente 08/12/2011 08:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.