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

[Jay Alverson]

Quote

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-Random-Numbers-Test.xls"  ' source workbook

sqlstr = "INSERT INTO [ODBC;FileDSN=C:\Writing\Winbatch\MYSQL\VBS-MySQL.dsn].[exceltable] SELECT * from [Random-Number-Worksheet-16$A1:E10]"

'	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

Thanks, Jay