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]
