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/[EMAIL PROTECTED] << 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).
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
