[Eug-lug] Importing a .csv file into MySql

[Fred James]

Ron LeVine wrote:

> I am helping a friend with "OSCommerce" which is a PHP/MySQL based 
> eCommerce solution. She gets a .csv file from her supplier with all 
> the products available and rather than hand enter/modify 20,000 
> products, it would be nice to just have MySQL just import them.
>
> The file is a CSV export from Excel in the form of:
>
>
> SKU
> 	ITEMID
> 	DESCRIPTION
> 	 VENDOR 
> 	CAT
> 	PRICE
> 	Weight
> 	SIZE
> 	UNIT
> 	SUB-CATEGORY 
> 	STATUS
> 	 FR
> 	LA
> 	AT
> 	CH
> 	DA
> 	HO
> 	KA
> 	NJ
> 	TA
> 	MI
> 	PO
> 	LR
>
>
> The MySQL DB record structure has some of these catagories, but not 
> all and has some catagories that are not listed here that are used for 
> site maintenance.
>
> So, I need to parse the file for the info that is needed and stick it 
> into the relavant records. Doing a wholesale batch import isn't really 
> feasable.
>
> Thanks again,
> Ron
>
(omissions for brevity)

Ron LeVine
Since lines of data can sometimes have blanks, a good approach would be 
to export the Excel file to a CSV file using a field separator character 
that is unlikely to be used within a field - I often use the pipe (|).
Then  an AWK (gawk) script can parse the line for fields ... for example 
(in a BASH shell script) let us assume you only wanted fields 1 and 3 ...

while read Line
do
    Field1=`echo $Line | gawk -F\| '{ print $1 }'`
    Field3=`echo $Line | gawk -F\| '{ print $3 }'`
   (insert SQL statement here to insert/update record to table)
done < $inputfilename

Hope this helps
Regards
Fred James

PS:  I am not sure of the syntax in MySQL, but in SQL*Plus (the Oracle 
version) the SQL statement could be embedded at the point indicated 
above like this ...

sqlplus $USER_NAME/$PASSWORD at SID << EOF
      insert into shema.tablename (column1, column2) values 
('${Field1}', '${Field2}');
exit
EOF

(where SID is site ID (database name), and the second EOF must start in 
column 1 of the code file).

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://euglug.org/pipermail/euglug/attachments/20070822/00789611/attachment.htm