Best Practices Importing Data Files to SQL 1

[objuan]

I am looking at approaches to getting data from files (eg., xls, csv) that are uploaded by external users of a data driven site, imported into a SQL database, and then displayed to public. I am looking at the FileSystemObject as an approach to read the data out and then move to SQL with basic SQL statements. This seems kind of bulky, and I was wondering if there is a more efficient way to go about the process. Happy to provide more detail info if it is unclear. Have used Tek-tips before to find very useful info...this is the first time to post a question.

Thanks in advance.

 

[TomasCrossroads]

Hello,
I'm into the exact same problem the last days. Here's what I have so far:
- After uploading the source data file you can simply open it as a recordset (for txt, csv and mdb at least - i have problems with xls because you need a named range in the worksheet to act as table name). Once you have a recordset it's no big deal writing it out to SQL.

You need to use a different connection string according to the file type that was uploaded.

for example, for txt and csv:

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\database&quot & ";Extended Properties='text;FMT=Delimited'"
'or
sConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & server.mappath("\database&quot & ";Extensions=asc,csv,tab,txt;"

sSql = "select * from filename.csv"

So there's no need to read the file yourself with filesystemobjects.

The problem with excel files:

You can open an excel file as a recordset like this:

sConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\database\myfile.xls&quot & ";Extended Properties='Excel 8.0;HDR=Yes'"

'If is "HDR=Yes" the provider will not include the first row of the selection into the recordset.
'If is "HDR=No", the provider will include the first row of the cell range into the recordset

sSql = "SELECT * FROM myNamedRange"

I found information on this url about reading excel files:

http://sites.encompassnow.com/idude/articles/using_excel_file_datasources.asp

The point different ways to open the excel file, but only thing I could get to work was opening a named range.
Now of course when the excel file is uploaded you don't know what named ranges (if any) are present...

I'm trying to find a way to open a sheet as recordset instead of a named range. If you have any luck, please let me know. Other solution could be to open the excel file first with means of the excel.application object and somehow create a named range for all data on the first sheet. But I didn't find out yet how to code that in ASP.

Greetings

 

[Tarwn]

In both cases it seems that you two are uploading the file, saving it to the server, then trying to move it to the database. Wouldn't it be simpler to write the binary data to a BLOB field and then save the filename into another field (as well as MIME type and so on for easier use).
This data is what your file upload component would normally write to the file on your server, but if you cut out the middleman then I think it would probably save a great deal of time in that you would only be reading the file once (upload and assign to variable then write to db) rather than read, write, open new file, read, write to db.

Not sure if this is actually feasible, haven't written an upload component in at least year, but it may be a thought for you guys to consider.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

 

[objuan]

Tarwn,

Thanks for the insight...you hit on the point that I am wrestling with in terms of efficiency to get from point A to point B.(uploading the file, parsing the data, and getting it into SQL) Perhaps, a little more detail would be useful. First, the upload component is SAFileUp which comes included with the particular hosting service that the site uses. There are two users in my scenario...a "customer" who uploads detailed product information in a semi-fixed format. This detailed information is associated with more general product info stored in SQL. The product detail (amount of data/line items) varies from product to product, but generally stays in the same format. Right now the idea is for the "customer" to upload the file, and then a site admin would go in and do the tranfer process. However, this is not written in stone if there is a more efficent way to do it (eg., customer uploads, and data is moved to SQL, site admin reviews and approves, and it goes live). Sorry for rambling, but thought additional info might help.

Thanks again to all for your help and expertise!