Re: Import data from an excel spreadsheet into an mysql database

[Matthew Adams]

Jay Alverson Wrote:
-------------------------------------------------------
> Toby White-Beebe Wrote:
> --------------------------------------------------
> -----
> > Hello Richard,
> >
> > I have been successful in importing the CSV
> file,
> > only after I stripped off the trailing field
> > delimiters ',,,,' off of each line to be
> imported
> > ? I did use the standard Import/File
> > Location/ANSII/CSV using Load Data/Replace Data
> > with File, Ignore Duplicate Rows, Fields
> > Terminated by (,) without parands... everything
> > else is default values.
> >
> > I, on the otherhand, am having minor issues
> with
> > importing Currency Fields and have the values
> > appear, with or without the ($) ? The field
> value
> > is set at Decimal/8,2/not null/MIME type
> > (text/plain)/Browser Transformation
> (text/plain:
> > formatted) ?
> >
> > Can anyone help me set up the field properly to
> > show ANY numeric value ?
>
> Toby can you provide us with your table's DESCRIBE
> output ?
>
> Thanks.
>
> I've been using this but haven't tried it with
> currency yet...(on either side)...
>
> dim xlpath, sqlstr, connStr, adoConn
>
> xlpath =
> "C:\Writing\Winbatch\2007-Tutorial\Excel\Excel-Ran
> dom-Numbers-Test.xls" ' source workbook
>
> sqlstr = "INSERT INTO . SELECT * from "
>
> ' tell it where the source XL workbook is...
> connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & xlpath & ";Extended Properties=" &
> chr(34) & "Excel 8.0;" & chr(34)
> 'msgbox connStr
>
> Set adoConn = CreateObject("ADODB.Connection")
> adoConn.open(connStr)
> ' execute the export SQL...
> adoConn.execute(sqlstr)
>
> Set adoConn = Nothing


You should try using DAO.DBEngine.36 , it's so much faster than ADODB and not depricated (ADO/ADOX was created for access 2k and 2k2, but then dropped again for updated DAO (The old library) for 2k3 and 2k7 (It took 13 hours to process with ADOX all the tables in a database in access 2k format with 49 tables and a few hundred queries, and 2(+-1) minutes with DAO to do the same (on a 1.2ghz celeron :|..))