instead of a temp table could i upload to a folder?
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
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
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.