taking data...

[horhan1]

Hi,

How can I import the data of the MS Excel datasheet to an existing MS Access database table without using neither Excel nor Access.

Is there any ready to use ASP codes or please show me the way (articles) to do it?

Thanks in advance.

Regards!

M. Haluk Orhan

 

[DreXor]

you can use odbc drivers for each , create a connection to each, map out your field sequences so field_a of excel goes to field_c ( or whatever ) of access, then loop through the excel recordset doing inserts to the access connection

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[horhan1]

Thanks for the reply!

Could you please give more detail? or any written tecnical articles.

RegardS!

 

[RhythmAddict112]

Sample of access connection:

http://www.webwizguide.com/asp/tutorials/connecting_to_a_database_pt2.asp

So in the tutorial above you change this line

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")

to...

adoCon.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=c:\somepath\mySpreadsheet.xls;" & _
           "DefaultDir=c:\somepath"

 

[halukorhan1]

Thanks for the reply but, I know how to make a connection to Excel file.

I actually need an IE interface (wizard) to import Excel data to MS Access database table.

Or some ideas how to do it? Do I have to use both java and asp?

 

[DreXor]

well if you're not going to open excel OR access in anything but the brower via asp to pull the data, you're going to have to write an import wizard to do it, and those are in depth and not very fun. i've written a few and can point you in the right direction, but i would highly recommend finding other means, even if it's making CSV files that have the right field names already in it to handle the import

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

conceptual stuff :

denote if it's an import,update, or delete <choosing method> (not much changes between the three and the added functionality is useful)

open excel and access in asp, read schemas
make 4 list boxes, 2 for each source.
one of each source is unused fields,
second of each is paired, used fields.

basically you're selecting which fields will be used, and what that field matches to.

after the mapping is in place then it's a matter of building the statements, i recommend pushing them into a 20 or 50 dimmed array then pass it as a batch <method> to push the data around as desired.

highly recommend storing the mapped layouts to schema files that can be loaded/copied to schema.ini for use with odbc drivers and make life easier in the future for common file layouts

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!