importing .xls, .csv etc data...

[halukorhan1]

Hi,

I need an ready to use ASP script or a guide/tutorial to import the data from the .xls, .csv, .txt etc files to MS Access or MS SQL database without using the program itself.

It will be a kind of a wizard that seems like Access --> Get External Data Wizard.

Any help is needed and appreciated,

Regards!

M. Haluk Orhan

 

[shatch]

You can connect to text files and excel databases using a connection string, read each record and put it into a SQL database. Try the following connection strings

For Excel:

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

Or, for text

oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt"

with

oRs.Open "Select * From customer.csv", _
oConn, adOpenStatic, adLockReadOnly, adCmdText

as the select command

 

[halukorhan1]

Thanks for your help but there will be a lot of issues to consider...

First the user is going to select the file manually. Second the user should be able to edit the types of the excel columns (whether it is integer (number), date, text etc) ...

Regards!

 

[shatch]

OK, that can be done...

1. Assuming the user uploads the file, you write it to a set path, with a file name which you then read. You can then, from this file name get the type (excel, text) and use the relevant connection string, putting the name of the file in the correct place in the connection string

2. After connecting, read the first line (which should be the names of the fields) and allow the user to manually configure their types, which you could then hold in an array

3. After this has been done, store the data

I assume that the data is not going into a specific table of the database and you want to create a database table schema based upon the data uploaded? If this is the case i would set up a flexible database format, comprising of a format like below

Table: UserDataHeader
Fields: UserDataHeaderID int (indentity), UserID int, DataAdded datetime

Table: UserDataFields
Fields: UserDataFieldID, userDataHEaderID, FieldType, FieldName

Table: UserDataDetail
Fields: UserDataDetailID, UserDataFieldID, Value

You can then add the data to this and essentially create a virtual database for each of the users - performance wont be perfect but it should do the job.

If you do want to allow the users to put data into a sepecfic table, you should include at stage 2 above the option to map the data from the uploaded text file to table rows in your database.

Hope this helps

 

[halukorhan1]

Hi,

Thanks again for your reply.

All the data will be imported to an existing database and table (TAltProjeler).

Regards!