If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Are you just trying to upload the file to the server?
In that case there are many upload programs out there and thats really
straight forward.

Or (this is the 1 i thought you were talking about)
Do you want to take the contents of an excel sheet and write the values to a database?
This is the 1 i thought i was posting for.
In this case what i do is upload the file to the server (so i can manuplate it from the server) and then i take its contents and writes it to a table.

The only reason i use a temp table is so that i can varify that the entire excel sheet data gets writen to the table and does not error out.

Lets say i write the data straight to the live table and it errors out half way through. Now i have written half the records to the live table, and now i need to upload the other half. So i guess you would have to delete records from the excel sheet and do it again to get the rest of the records or something else like that because if you try to run this again on the excel sheet with out getting rid of the records that were uploaded then now you have duplicate records.

Explain again what it is your after as im afraid i may of jumped the gun and gave you a solution that is not the solution.

Even if you just want to upload the file and write the file path to a DB, thats alot easier then this.

Well you will be uploading the file to a folder. Let me explain.
1). The first thing that needs to happen, is that you need to upload the file to the server. You can have this file uploaded anywhere you want on the server.

In your code to upload, you can have the path to the location you want the file uploaded to, hardcoded. So for the sake of this example, lets say it was uploaded to this location
C:\inetpub\wwwroot\_Files\MyTest.xls

Get it, got it, good........

2). Next we need to open the excel file like its a Database table.

This is where the connection string comes into play. In your connection string you need to give it the path of the file you just uploaded. So in your code you need to pass the connection string this string or use a variable to set it. But you will set the path to the file as
C:\inetpub\wwwroot\_Files\MyTest.xls

3). Now that you have your excel connection string, lets select all the data from the excel sheet like we would do with a normal table in a database.

SELECT * FROM [sheet1$]

Think of this as creating a database connection and selecting all the data in the Table only this is an excel sheet.

4). Now that you have your recordset of data, you now need to loop that data and write this data to a Temp Database table (which is nothing more than a copy of the real Database table) or to your Live Table that you will use to display the data which i have been calling a Live Table when a user requests it.

Temp Table is nothing more then a table in the database. I call it a Temp table because that is the purpose it servers. It will temporally hold the data until i write it to the Live Table.

Live Table is just what im calling the table that i will read when a user requests this information.

Temp Table Name = T_ExcelData
Live Table Name = ExcelDate

Again my reason for this is this. Say your excel sheet has 10,000 records. As we are writing the data to the live table it breaks and only put in say 3,635 records into the real database. There are still about 6,500 records that did not make it to the database. So since you were writing this straight to the Live Table you will now have to delete those 3,635 recrods from the excel sheet and attempt to do this process all over again.

Where as if you were just writing these records to a temp table and it broke like it did above, then since its a temp table and i always delete all this data before i attempt to write the records, i can just rerun the script and attempt to upload all the records again becuase it will overwright everything that was there. Now that it made it to the Temp table it easy to write it to the live table.

* Both the Temp table and Live Table have the same datatypes and the same column names. They are mirror images of their selfs. This allows for an easy copy from 1 table to the other with a statement like this

INSERT INTO LIVETABLE
SELECT * FROM TEMPTABLE

Get it, got it, good.

Hope that helps explain what a temp table really is, nothing more than an exact copy of the table you want the data to sit in. Temp table is nothing more than a name that i am calling it because it will temporally hold data until im dont with it.

so i don't really need to upload to a folder before uploading to a temp table, correct?

maybe have the uploading to a temp table (in sql server i assume) then when that completes successfully i display a screen to the user where it says that the data has been validated and is ready to be inserted into the master table. then have a submit button for the user to click when they want it to finsih (temp data to master data).

does that make sense?

EDIT: the uploaded data will need to be appended to the master table, this would not be a problem, correct?

so i don't really need to upload to a folder before uploading to a temp table, correct?

No, you do need to upload the file to the server, so that you can connect to the file. If the file is located on your C: drive on your machine, then the server will not be able to connect to the file. So YES you do need to upload the file.

maybe have the uploading to a temp table (in sql server i assume) then when that completes successfully i display a screen to the user where it says that the data has been validated and is ready to be inserted into the master table. then have a submit button for the user to click when they want it to finsih (temp data to master data).

Yes this is not a bad idea you could do it like this. This way you upload it to a temp table and then notify the user that its ready to insert into the new table. I see no problem with that. They example i provided, just does it all at the same time. But there is no reason why you could not do it like this.

EDIT: the uploaded data will need to be appended to the master table, this would not be a problem, correct?

No this is not a problem. With this statement
INSERT INTO LIVE TABLE
SELECT * FROM TEMP TABLE
This will just copy all the data from the temp table and add it to the Live Table. This data will appened to the master table, thus not overwriting anything and just adding these records to the rest of them.

You will see that when you have selected a file and click the upload button, it will call the ProcessForm which will loop and upload all your files.

After the file is uploaded it will call a sub SaveFile which is a sub i created that just writes the filepath and file name to my database.

See if this helps.

This also assumes that you have the _upload.asp file located in a folder called Include.
Change this line
<!--#INCLUDE virtual="Include/_upload.asp"-->
To point to where ever you have the file located.