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
