Upload .xls file to SQL Server via ASP.NET app

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Upload .xls file to SQL Server via ASP.NET app

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-05 : 16:39:35

I got so far as to take a file from my local machine to another directory on my C: drive:

Dim getmyFile As HttpPostedFile = myfile.PostedFile
If IsNothing(getmyFile) Then
Label2.Text = "Please select a file to upload"
Label2.Text = "Line 78"
Else
If getmyFile.ContentLength = 0 Then
Label2.Text = "Cannot upload zero length file"
Else
Dim ServerFileName As String = Path.GetFileName(myfile.PostedFile.FileName)
getmyFile.SaveAs("C:\TestSaving\" & ServerFileName)
Label2.Text = "Successful upload to C:\TestSaving\" & ServerFileName
End If
End If

...............now, what I need to do is take an .xls file and allow users to upload that into a specific table.
Any suggestions or samples/articles are more than welcome!

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-06 : 01:09:32

See if this helps you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-06 : 19:23:19

Madhivanan!
Thanks so much for the reply! I think this will almost get us there 100%!

So, to export data from Excel to existing SQL Server table, the code would be:

Insert into Deliverables(
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
)

...right?
We're passing the database used via a session object (there are 8 to choose from), so we'll know which table and which database each time.
I'm assuming this would basically just need a cmd.ExecuteNonQuery() line and we're done.....right?

Thanks for the help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 01:11:41

That should be

Insert into Deliverables
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

I think you are on the correct way

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-08 : 10:56:26

I'm getting "Incorrect syntax near ')'

for this string:
strSQL = "Insert into ActivityTest Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=YES'; 'SELECT * FROM [Sheet1$]')"

Thanks for the help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 01:12:10

Did you execute this in VB?
It seems correct. Check it again
Try to give column names in the query

strSQL = "Insert into ActivityTest(columnlist) Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=YES'; 'SELECT * FROM [Sheet1$]')"

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 09:22:01

I changed the string to the following since I've gotten another response as to that being the syntax issue. That error goes away, but I get another one.

strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')"
............"," instead of ";"....and now I get:

Error Updating Table: Invalid object name 'ActivityTest'. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I know the connection and table name are correct.
Is it b/c the .xls file is still on the client's machine that the error is occurring? How would I get it over to the db server then?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 09:39:56

It is because the Excel is opened or used by other application

You can handle this


Declare @err int
--your insert statement
Select @err=@@Error
If @Err=7399
--The file is in use

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 09:49:35

I'm trying to use this:

Dim err As Integer
strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"
strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;"
strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')
Label3.Text = strSQL.ToString()
Dim cmd As New SqlCommand(strSQL, sCon1)
Try
cmd.ExecuteNonQuery()
err=@@Error
If err = 7399 Then Label2.Text = "File in use"

....getting syntax error on @@Error...this is a SPOC I assume...how would I set this up...I'm using VB

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 09:56:54

The code I used will work if you execute it in Query Analyser.
As you are doing it in VB, you can get the number from Err.Number

On Error Goto ErrCode
strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"
strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;"
strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')
Label3.Text = strSQL.ToString()
Dim cmd As New SqlCommand(strSQL, sCon1)
cmd.ExecuteNonQuery()
exit sub

ErrCode : if Err.Number=7399 then msgbox "The file is in use",VBInformation

If err = 7399 Then Label2.Text = "File in use"

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 09:57:00

Got it:

err = "Select @@Error"

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 10:02:48

Just used:

Try
cmd.ExecuteNonQuery()
err = "Select @@Error"
If err = 7399 Then
Label4.Text = "File in use"
Else
Label4.Text = err.ToString()
End If

...and no error was returned.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 10:06:00

>>...and no error was returned.

Open the Excel file and run that code

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 10:11:38

If I run:

Dim err As Integer
Try
cmd.ExecuteNonQuery()
err = "Select @@Error"
If err <> 0 Then
Label4.Text = err.ToString()
Else
Label4.Text = "No Error...line 91!"
End If

...I can't get label4.Text to populate at all

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 10:13:41

If I open the .xls file, and then run the app...I get the following run-time error:
"The process cannot access the file "C:\TestSaving\exceltest.xls" because it is being used by another process. "

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 10:17:12

So you need to handle this in VB
Get the Err.Number and give alert message accordingly

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 10:27:24

I thought this would have caught it when the .xls file was open, but it doesn't. I get the run-time error page:

Dim strSQL As String
Dim err As Integer
strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"
strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls
strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')"
Label3.Text = strSQL.ToString()
Dim cmd As New SqlCommand(strSQL, sCon1)
Try
cmd.ExecuteNonQuery()
err = "Select @@Error"
If err <> 0 Then
Label4.Text = err.ToString()
Else
Label4.Text = "No Error...line 91!"
End If
Catch ex As Exception
Label2.Text = "Line 82 Error Updating Table: "
Label2.Text &= ex.Message
Finally
sCon1.Close()
End Try

...can you see anything I missed?

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 11:53:58

madhivanan,

I was under the impression the code would take the .xls file straight from the user's machine to the table. Looks like I'm wrong.

Do I need to do the following:
1. Copy the .xls file from user's machine to web server
2. Copy the .xls file from the web server to the db server

Can't I just go from the user's machine to somewhere on the db server?

I'm trying to figure that part out, but am having no luck.

Thanks!

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-09 : 17:53:54

I've pulled an .xls file into a staging table on my SQL Server from a client side upload.
I display the information of:
Size as datatype real
Data as datatype image
ContentType as datatype nvarchar

I need to take this information and extract the rows from this .xls sheet into another table where existing data resides?

How can I do this?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 01:04:01

In your select statement select only those columns and insert them to SQL Server table

Madhivanan

Failing to plan is Planning to fail

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-08-10 : 13:32:43

I know I can physically take the .xls file from the user's machine to SQL Server from the following routine:

Dim iLength As Integer = CType(myfile.PostedFile.InputStream.Length, Integer)
If iLength = 0 Then Exit Sub 'not a valid file
Dim sContentType As String = myfile.PostedFile.ContentType
Dim sFileName As String, i As Integer
Dim bytContent As Byte()
ReDim bytContent(iLength) 'byte array, set to file size

'strip the path off the filename
i = InStrRev(myfile.PostedFile.FileName.Trim, "\")
If i = 0 Then
sFileName = myfile.PostedFile.FileName.Trim
Else
sFileName = Right(myfile.PostedFile.FileName.Trim, Len(myfile.PostedFile.FileName.Trim) - i)
End If

Try
sCon1.Open()
myfile.PostedFile.InputStream.Read(bytContent, 0, iLength)
With cmdInsertAttachment
.Parameters("@FileName").Value = sFileName
.Parameters("@FileSize").Value = iLength
.Parameters("@FileData").Value = bytContent
.Parameters("@ContentType").Value = sContentType
.ExecuteNonQuery()
End With
Catch ex As Exception
Label4.Text = ex.Message
Finally
If Label4.Text = "" Then
Label4.Text = "Upload successful!"
End If
sCon1.Close()
End Try

***************
How could I change this to allow me to add the rows of the .xls file to the table..........and not just get metadata?

Thanks!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources