Import excel into SQL 2005 working in development but not in production

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.

I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.

Any reason why? I've never seen this happen.

Here's the code. Thanks in advance.

Protected Sub BtnUpload2_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BtnUpload2.Click UploadTextDocument()End Sub Private Sub UploadTextDocument()Dim locationAs String = FileUpload1.PostedFile.FileName.ToString' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(connectionString) bulkCopy.DestinationTableName ="ExcelData" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using LBError.Text ="The spreadsheet was successfully uploaded."Catch LBError.Text ="There was an error. Check the spreadsheet for correct format."End Try End Sub

whats the exact error message ur getting?

|||

Hey Karen,

I don't get one. Debugging is off on the live application. I just get the error message at "catch".

I have a prospective client looking at it as we speak and don't want to make any changes to the web.config while they are viewing it...

|||

to catch error

use

try catch.. with and throw ex,.

one good possible reason is excel work sheet contains datatype that are not a part of datatypes of ur columns in tables. ..

there would not be any error, but the writetoserver would simply exit.. that's it..so use the old sheets u used in development server and try again.

Try printing., sheetnames and fieldcount.. in reader..

|||

Actually, I'm using the exact same sheet, same data, everything. I debugged it and still can't find any errors.

It only works from developement... I haven't tried it in a reader.

|||

How bout u try saving that file first and then transferring its contents to the database?

Regards

Karen

|||

Possible problems:

You don't have the correct version of Jet installed on the production server.

The process doesn't have read permissions at the location that the file is being written to.

try turning on remote errors in the web.config file so you can see exactly what the error is.

|||

Please provide the code topcopy the data/rows in the Excel work sheet to the sql server table.

Thanks and Regards

Ravi Shanker Maduri

Hyderabad

|||

Okay. I captured the error message. When I select the excel sheet from the folder on my desk top this message appears. The path is correct, as I said, it works fine when I do it from developent...

'C:\Documents and Settings\rednelo\Desktop\IPAddress_Mapping\test.xls'. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

|||

The problem is this line here:

Dim locationAs String = FileUpload1.PostedFile.FileName.ToString

You are using the filename that was on the client. You need the filename of the temporary file that the server created.

|||

Hi

I want to Import The Excel Data to the sql server using T-Sql Query ,i will run somany PL-sql statements for each project i want to automate it , i want to insert the default data to a table using excel sheet to the sql server table.

Please if any one know the solution for moving the excel data to the sql server using T-Sql Statement only.

Thanks and regards

Ravishanker Maduri

|||

Motley:

You need the filename of the temporary file that the server created.

All that makes sense. How would I get to the name of the temporary file?

|||

strINVSRC = Path.GetFileName(FlInvSrc.PostedFile.FileName)

|||

Hey Karen,

I tried what you have and I'm getting this error when I put in on the server. Did you run into this?

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
However, the same code in development gives me this:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

So, its not finding the file. Let me recap...

Dim locationAsString

'this works in development as I said before. So I know that was finding it at least on my local machine.

location = FileUpload1.PostedFile.FileName.ToString

What you had does not appear to find the file.

location = Path.GetFileName(FileUpload1.PostedFile.FileName)

Its somewhere between the two...

|||

Take a look at this link some one gave it to me when i had the same error message

http://forums.asp.net/t/1034337.aspx

I have done it this way..

First i am getting the name of the file...

Dim strFilePath,location as string

StrFilePath = Path.GetFileName(FileUpload1.PostedFile.FileName)

and then

location = Server.Mappath("The directory where u wanna store the file" & StrFilePath.

hope this helps..

Regards

Karen

Import data from Excel to SQL server or vice versa

hi,

I want to upload an excel file into the SQL server through Tsql Code.

please suggest me the way so that i can reduce my time.

thanks.

Create a linked server (using the ODBC provider) and you can then refer to it using TSQL.

Import data from Excel to SQL server or vice versa

hi,

I want to upload an excel file into the SQL server through Tsql Code.

please suggest me the way so that i can reduce my time.

thanks.

Create a linked server (using the ODBC provider) and you can then refer to it using TSQL.

Import Data

Hi everybody,
I want to import data from different server.

According to my system. when we sell anything to our customer we upload all the information to webserver from our local server. This job we are doing it manually right now. but i want to upload the information automatically. when my agents sell something and they submit as sell i want to send a copy of information to my webserver automatically. Is there any trigger which can insert data into different server or schedule which can import data from different server. Please help meread BOL about trigger,if u want immediate update to ur webserver.
Or u can write procedures and schedule it,check BOL schedule job.|||I try to steer clear of triggers which operate outside the scope of the database. Consider using the trigger to populate a staging table of changes, and then running a scheduled job to load those changes to your destination database. This setup is more tolerant of system disruptions.

Import csv file to MS SQL 2005

Hi Guys,

I have been trying to search for a free asp or asp.net script that will allow me to upload a .csv file and import it into an MS SQL Database. As its going to be a ProductCatalog and pricing changes nearly 2nd day. And wanting to an a script that I can put in my admin panel on my site to upload a .csv file and import it to a MS SQL Database.

I will be updating fields as well as adding new products. So the upload script would need to be able to handle those two things.

Is their any good free scripts around that people can recommend.

Thanks

Matthew

Which version of SQL server are you using?

SQL Server Integration Services will do this nicely...

|||

Using SQL 2005 Standard Edition, as the database will be used on a Website, don't want to have to keep logging into the control panel and then going and using the Web-Based SQL Management Tools.

Matthew

|||

Hello,

http://www.nigelrivett.net/ImportTextFiles.html is a script to import text files that arrive in a directory into a table.

It will process every file in the directory with the correct filemask and move the file to an archive directory on completion.
It can be used in conjunction with an ftpget SP to import files from a ftp server .

You could add some minor change to import csv file.