www.webdeveloper.com
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 55

Thread: possible to upload excel file into sql server?

  1. #1
    Join Date
    Nov 2005
    Posts
    35

    possible to upload excel file into sql server?

    I was thinking of having a web page for uploading excel files to a db in sql server.

    is this possible? if so, where is a good place to start (ie reference, textbook, etc)?

    Thanks!

    EDIT: I would also like to append any uploads to the table where the files are uploaded, so that the table keeps growing.
    Last edited by st111; 11-09-2005 at 11:25 AM.

  2. #2
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    well let me ask you this. Will you have column headers in the .xls page?
    Will the headers always be the same?

    Or will this just be a random .xls workbook that has random data.

  3. #3
    Join Date
    Nov 2005
    Posts
    35
    Quote Originally Posted by lmf232s
    well let me ask you this. Will you have column headers in the .xls page?
    Will the headers always be the same?

    Or will this just be a random .xls workbook that has random data.
    Sorry, I should have been more clear...

    The .xls file WILL have col. headings. Each .xls file that is to be uploaded will be the same format, just diff. files for diff. months.

  4. #4
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    ok, here is what i did.

    I use aspupload.

    First i let the user select the file to upload, then i upload the file and set a variable = to the path of the .xls file.

    FileName = "Your File Path"

    Then i call my Sub which does just this.

    Opens the excel sheet and gets all the data
    Delete all data from a temp table
    Write the excel data to a temp table
    If no errors then i take the temp table data and write it to the live table.

    You will need 2 connections
    1 for the excel sheet conneciton
    1 for your Database table connections.

    Here is the connection string for the excel sheet
    Code:
    strExConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" 
    	set oExConn = Server.CreateObject("ADODB.Connection")
    	oExConn.Open strExConn
    	set oEx1 = Server.CreateObject("ADODB.recordset")
    HEre is a web site that lists all the conneciton types, we are using a ole db conneciton for this one
    http://www.connectionstrings.com/

    Then you need 1 connection that you use to connect to your Database.

    Then this is basically the sub i call
    Code:
    SQL = "SELECT * FROM [sheet1$]"
    set oEx1 = oExConn.Execute(SQL)
    If not oEx1.EOF then 
    	
    	'delete temp table data
    	SQL = "TRUNCATE TABLE TempData"
    	set oRS1 = oConn.Execute(SQL)
    	
    	'When you get the data from the excel sheet, loop it
                 Do WHile not oEx1.EOF
    		sqlInsert = "Instert into temptable " & _ 
                        "VALUES ('" & Trim(oEx1.Fields(0)) & "')"
    		set oRS1 = oConn.Execute(SQL) 'this is my Datbase connection
    
    		oEx1.MoveNext
    	Loop
    	
    	'we are done writing the data to the temp table, now lets copy it to the real table
    	SQL = "INSERT INTO REALTABLE " & _
    	      "SELECT * FROM TEMPTABLE "
    	set oRS1 = oConn.Execute(SQL)
    	
    ELSE
         ' WE HAVE NO DATA
    ENd if

    When itirating the data returned from the excel sheet, you get the values like this

    oEx1.Fields(0)
    oEx1.Fields(1)
    oEx1.Fields(2) etc.


    Now if you follow this example there are some things to keep in mind.
    You need to make sure that the that your temp table data types match your live table if you do the kind of insert that i did.

    This is a pretty long post and i might not of been clear on some things. Let me know how it goes and ill help you.

    Thats it

  5. #5
    Join Date
    Nov 2005
    Posts
    35
    WOW!

    Thanks a lot! I will get to this tomorrow and let you know how I make out.

  6. #6
    Join Date
    Nov 2005
    Posts
    35
    I have used an uploading system before, i assume I can use it? It uses the FileSystem, and I upload to a folder (created on-the-fly if it isn't already there).

    if i use this, do i need a temp table, or can i upload the actual .xls file to a folder?

  7. #7
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    ok i may of jumped the gun.

    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.

    Any way let me know.

  8. #8
    Join Date
    Nov 2005
    Posts
    35
    I do want to write the excel values to a database in sql server. i was just trying to understand the temp table part. instead of a temp table could i upload to a folder?

    i am not sure how to do the temp table part.

    i understand about making sure all the records get written w/ no errors, that's a good idea. i am just not sure how to begin since i have never done this before, aqnd my asp experience is limited.

    thanks!

  9. #9
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    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
    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.

  10. #10
    Join Date
    Nov 2005
    Posts
    35
    that clears things up, thanks!

    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?
    Last edited by st111; 11-10-2005 at 10:33 AM.

  11. #11
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    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.

    Good luck

  12. #12
    Join Date
    Nov 2005
    Posts
    35
    OK, i'm running into unforseen problems with my uploading.

    sooo, i tried to go from scratch using this:

    http://www.asp101.com/articles/jacob/scriptupload.asp

    i have no clue if i can just copy&paste the code and it will work (ie no extra programming on my part, such as a stored proc).

    EDIT: this is what i get:
    File Name:upload.aspFile Size:4862File Type:text/aspFile Name:upload.aspFile Size:4862File Type:text/asp

    but the file isn't uploaded. in fact, the filename is wrong..

    any ideas?
    Last edited by st111; 11-11-2005 at 02:28 PM.

  13. #13
    Join Date
    Nov 2005
    Posts
    35
    OK, now it displays the proper file attributes(ie name, size) but it doen't save the file.

  14. #14
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    Here is a sample page that i use to upload a file.

    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.

    Code:
    <%option explicit 
    
    Dim ResultHTML
    Dim Form: Set Form = New ASPForm 
    %>
    <!--#INCLUDE virtual="Include/_upload.asp"-->
    <%  
    'Do not upload data greater than 1MB. 
    Server.ScriptTimeout = 1000
    Form.SizeLimit = &H100000
    
    Const fsCompletted  = 0
    
    If Form.State = fsCompletted Then 'Completted
      ResultHTML = ProcessForm
    ElseIf Form.State > 10 then
      Const fsSizeLimit = &HD
      Select case Form.State
    		case fsSizeLimit: ResultHTML = "<br><Font Color=red>Source form size (" & Form.TotalBytes & "B) exceeds form limit (" & Form.SizeLimit & "B)</Font><br>"
    		case else ResultHTML = "<br><Font Color=red>Some form error.</Font><br>"
      end Select
    End If 
    
    if request.QueryString("Action") = "Cancel" then
    	ResultHTML = "<br><b>Upload was cancelled</b>"
    end if
    
    Function ProcessForm  
    	Dim objNewMail
    	Dim File
    	Dim FileName
    	 
    	For Each File In Form.Files.Items
    		'If source file is specified.
    		If Len(File.FileName) > 0 Then
    			MyFile = File.FileName
    			
    			HTML = HTML & "<br>&nbsp;" & File.Name & ": <b>" & File.FileName & ", " & File.Length \ 1024 & "kB</b>"
    			
    			'FILE LOCATION TO SAVE THE FILE TO (THIS IS LOCATION ON THE SERVER)
    			FileName = "\\data2\UploadFiles\" & File.FileName 
                                          File.SaveAs FileName
    
    			'THIS IS HOW I SAVE THE INFORMATION TO THE DATABASE.
    			'i call a sub and pass it the filepath and the filename.
                                           SaveFile FileName, MyFile			
    		
    		End If
    	Next
    End Function
    
    Public sub SaveFile(FilePath, FileName)
    	'save the file path and the id of the record.
    	Dim strConnection
    	Dim objConn
    	Dim objRS
    	Dim SQL
    	
    	strConnection = "Provider=sqloledb;Data Source=?;Initial Catalog=?;USER ID=?;PASSWORD=?;"  
    	set objConn = Server.CreateObject("ADODB.Connection")
    	objConn.Open strConnection
    	Set objRS = Server.CreateObject("ADODB.Recordset")
    	
    	SQL = "INSERT INTO UPLOAD " & _ 
    		  "VALUES ('" & FileName & "', '" & FilePath & "') "
    	set objRS = oConn.Execute(SQL)
    	 
    	set objRS = Nothing
    	set objConn = Nothing
    End Sub
    %>
    <HTML>
    <HEAD>
    <title>UpLoad Files Example</title> 
    </HEAD>
    <BODY>
    
    <form method="post" ENCTYPE="multipart/form-data" id=form1 name=form1>
    
    <table class=tablebody width=100%>
    	<tr>
    	<td>
    		<table width=100%>
    			<tr>
    				<td class=tablehead>Add Attachments</td>
    			</tr>
    		</table> 
    		 
    		<table class=clearbody> 
    			<tr>
    				<td><b>Attachment 1 :</b><input type="file" name="File 1" size=70></td>
    			</tr>
    			<tr>
    				<td colspan=2 align=Center>
    					<input Type=button name=SubmitButton Value="UpLoadFile" onclick="document.form1.submit()" style='width=150'>	
    				</td>
    			</tr>
    		</table> 
    	</td>
    	</tr>
    </table> 
    
    </BODY>
    </HTML>
    Last edited by lmf232s; 11-11-2005 at 02:50 PM.

  15. #15
    Join Date
    Nov 2005
    Posts
    35
    have u encountered this error, even tho the file is in the right location:

    The include file 'Include/_upload.asp' was not found.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles