Using Excel ODBC Driver

[MattWoberts]

I am trying to use the Excel ODBC driver to import Excel data into a database, but for some reason if the cells that I am importing are set to "General" format then the ODBC driver fails to pick them up and I get an empty string.

Has anyone had similar problems or any ideas/resources that may help...?

 

[ide]

The type of imput data different from type of your table field.
I have not used it ever, but i presume when you import a table you get a recordset from excel (rstExl).
Create a recordset from your db table (rstTbl) and try to insert data via a converting function like:
Function TypeCon(typeNum, MyTxt)
Select Case typeNum
Case dbInteger
TypeCon = CInt(MyTxt)
Case dbText
TypeCon = CStr(MyTxt)
'Case ...
'TypeCon = ...
End Select
End Function

and use this somehow:
...
while not rstExl.eof
rstTbl.addnew
for i=1 to rstTbl.fields.count 'or for i=0 to ... ?
rstTbl.fields(i) = TypeCon(rstTbl.fields(i).type, _
rstExl.fields(i).value)
rstTbl.update
next i
rstExl.movenext
wend
...

I hope it works and helps you
ide

 

[MattWoberts]

Thanks, I'll try somrthing similar and let you know how it goes.