Re: mysqlimport csv file import problem
On Sun, 27 Aug 2006, Jim Seymour wrote: Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, Hi Jim, that needs some preprocessing, but 'sed' is your friend. You could use some shell script doing the work for you: #!/bin/sh BLANK=' ' TAB=' ' WHITESPACE=${BLANK}${TAB} DATE=$(cat mydatafile.csv | sed \ -e 3 !d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/) DATA=$(cat mydatafile.csv | sed \ -e /[A-Z]/ d \ -e /-/ d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/ | tr '\012' ',' | sed \ -e s/,*$//) echo INSERT INTO mytable VALUES(${DATE},$DATA); exit 0 # end of shell script The resulting queries can be piped into the mysql client. HTH, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport csv file import problem
On Mon, 2006-08-28 at 13:47 +0200, Thomas Spahni wrote: On Sun, 27 Aug 2006, Jim Seymour wrote: Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, Hi Jim, that needs some preprocessing, but 'sed' is your friend. You could use some shell script doing the work for you: #!/bin/sh BLANK=' ' TAB=' ' WHITESPACE=${BLANK}${TAB} DATE=$(cat mydatafile.csv | sed \ -e 3 !d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/) DATA=$(cat mydatafile.csv | sed \ -e /[A-Z]/ d \ -e /-/ d \ -e s/^[$WHITESPACE]*// \ -e s/.*/''/ | tr '\012' ',' | sed \ -e s/,*$//) echo INSERT INTO mytable VALUES(${DATE},$DATA); exit 0 # end of shell script The resulting queries can be piped into the mysql client. Thanks Thomas, From what I had read it looked like I would have to do something like this. This will save me a lot of time. Thanks Again, -- Jim Seymour [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
mysqlimport csv file import problem
Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, -- Jim Seymour [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part