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

[Ron LeVine]

Thanks guys. I have a friend locally who is willing to give this a go.

Thanks again for all the info.
Ron

On 8/22/07, Fred James <fredjame at fredjame.cnc.net> wrote:
>
>  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 feasabl=
e.
>
> 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=3D`echo $Line | gawk -F\| '{ print $1 }'`
>     Field3=3D`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).
>
>
> _______________________________________________
> EUGLUG mailing list
> euglug at euglug.org
> http://www.euglug.org/mailman/listinfo/euglug
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://euglug.org/pipermail/euglug/attachments/20070822/88c4f875/attac=
hment.htm