CSV file -> Create Table

[nzgirl]

Hey.. I'm looking at FileSystemObject for the whole CSV file -> sql thing. Haven't delved deeply yet but thought I'd ask for opinions ...

I want to upload a CSV file and create a table from it. I can do this using enterprise manager and import data.
Is there a similar 'easy' way in ASP or do I need to read the file and count the rows and then create a table and then insert the data?

(ps: the CSV can be in any format and have any number of rows -once its in a tmp table I plan to get the data I want from it....)

Ideas? Opinions?

Thanks

 

[lesleint]

Have you looked into using a Dynamic stored procedure? Are the fields going to be different everytime? If not it seems that it would be pretty simple.

 

[nzgirl]

no I haven't.. I'll have a look... Thanks
I heard rumour of an upload procedure but can't find it on the net. Found a few I'd have to pay for but they seem to be geared towards Images. I want to create a table based on the columns in the CSV file.. and they can be different everytime.

Any more ideas which I'm searching?

Thanks

 

[lesleint]

http://www.aspupload.com

works great $149

Let me look for a stored procedure for yeah or write one.

 

[nzgirl]

Thanks
Guess what... my DBA now says.. oh yeah.. we have that loaded... (minor grumble that I didn't get told sooner.. but then again maybe I wasn't asking the right Q).. so off to read the User Manual for it!
Any hiccups you've found?
Thanks again!!

 

[nzgirl]

:-(
So went away over Easter and I can't see how aspupload will help. Yes I can use it to grab a file from a client. But after that I need to get the file into a database table.
I usually do this manually.. so I'm kinda at a loss where to start with an ASP program...
any hints? tips? pointers?

Thanks

 

[nzgirl]

Got it reading from file to screen by using some of these Faqs:

http://www.aspfaqs.com/aspfaqs/ShowCategory.asp?CatID=4

And I'd search for the whole file to Array idea here if search was up...
My Thought is that I'll have to read into an Array based on "endof line" char and then read each of those elements into an Array based on ","

Does that sound like I'm on the right track?

Thanks

 

[dilettante]

Lots of possibilities here.

One is to simply accept the upload, write it to disk, and then spawn a run of your database's bulk loader facility.

Another way is to accept the upload, write it to disk, and use the Tabular Data Control to read and parse the data back in. The TDC exposes a Recordset property that is nearly a complete ADO Recordset. I'd use the GetRows( ) method on this TDC Recordset to suck the data into a 2D variant array variable. Then I'd open the database table via ADO and do a batch update, interating through the 2D array (2nd dimension is the row dimension, watch out!) and using the AddNew( ) ADO method to plop the data from each row into the open table. Finally call BatchUpdate( ) on the thing and remove the CSV file.

If these can be big CSV files it might be better to spawn a separate WSH script to load the data and remove the CSV file.

Alternatively, your ASP page could accept the upload and then send the blob to an MSMQ message queue. You can have a program running as a service or maybe just an "always running program" (hmm) that pulls messages out of the queue and does the work already described to batch-load the data. MSMQ messages can be up to about 4MB I think. Larger blobs of data will require chunking.

Read up on the TDC, and on the full set of parameters to AddNew( ). MSMQ is "way cool" too.

How else are people doing bulk-loads in ASP?