MySQL Forums
Forum List  »  Microsoft Access

Re: Import data from an excel spreadsheet into an mysql database
Posted by: Matthew Adams
Date: December 12, 2008 05:13PM

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 :|..))

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Import data from an excel spreadsheet into an mysql database
7969
December 12, 2008 05:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.