VS 2010 - Reading Excel Data Into Dataset Loses Data
Apr 22, 2014
I have a client that provides a .xls file on their site that we have no control over that we download the file and then read it to update inventory. The first column is the SKU and in cell A2 it has the value 1028. This outputs fine but when I get to A8 the value is '1051-LV' and in my output it is blank. I am thinking in my dataset it reads A2 value and that becomes the type for the remainder of the rows. But what if that is wrong? How do I fix so it doesn't set the datetype to say int when it could be a string type?
Below is my code and works but the output of certain rows sku values are blank and are not like that in the excel file.
Code:
Protected Sub btnGetExcelFile_Click(sender As Object, e As System.EventArgs) Handles btnGetExcelFile.Click
litOutput.Text = ""
Dim ws As New WebPost
ws.URL = "http://www.domain.com/dropship/feed.xls"
'downloads excel file based on url
Dim bExcel() As Byte = ws.RequestBinaryData("", True)
What I am thinking is this? Is it possible for me to upload the data from an excel file to dataset of my application first, so that the user can view the data in a gridview to review it first, before the user strike the save button, to save it in the database. So, that in case there is a problem, the gridview will high light all the data with an error. So the user can easily pull out the excel and correct the data before saving it in the database.
I have a scenario where I have to read an excel file to dump records in a mysql database. The code generates an exception Sheet1 not found. I have pasted the code below. Please let me know the solution to the exception. I have given the correct read/write/modify settings to my excel file.
if (fileUpload1.PostedFile.FileName.EndsWith(".xls") || fileUpload1.PostedFile.FileName.EndsWith(".XLS")) { string fileName = Server.MapPath(fileUpload1.PostedFile.FileName); string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0"; // Create the connection object OleDbConnection oledbConn = new OleDbConnection(connString); try { // Open connection oledbConn.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]" , oledbConn); // Create new OleDbDataAdapter OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; // Create a DataSet which will hold the data extracted from the worksheet. DataSet ds = new DataSet(); // Fill the DataSet from the data extracted from the worksheet. oleda.Fill(ds, "RestaurantExcelData"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Response.Write("Data: " + ds.Tables[0].Rows[i].ItemArray[0].ToString() + ""); } } catch(Exception e1) { } finally { // Close connection oledbConn.Close(); } } else { Response.Write("File Not Excel (xls||XLS)"); }
But it shows me an errormessage when I run this query:
string query = "Select * from [IO_Definition$]";
IO_Definition is the name of the spreadsheet in my excel file. I also added the excel file to the App_Data folder of the website.
The error is:
The Microsoft Jet database engine could not find the object 'IO_Definition$'. Make sure the object exists and that you spell its name and the path name correctly.
The thing is, when I write the absolute path of the excel file in the connectionString it does work. Is there anyway I can make it work without writing the absolute path?
I am attempting to read data from an uploaded spreadsheet using ACE.OLEDB. The code, which is running fine on dev and test machines for XL2003/2007 reports "Exernal table is not in the expected format" error on connecting on the production server for XL2007 only.The code is
Dim connectionString As String = "provider=Microsoft.Ace.OLEDB.12.0;" _ & "Data Source='" & ImportData.FullName & "';Extended Properties=Excel 12.0;" LogWebActivity.LogThis("Entering POPSUKD, ConStr=" & connectionString, LogWebActivity.LogDetailLevel.DetailAndData) Dim con As New System.Data.OleDb.OleDbConnection(connectionString) LogWebActivity.LogThis("Dimmed con", LogWebActivity.LogDetailLevel.Debugging) Try Dim cmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & WorksheetName & "$]", con) Dim adapter As New System.Data.OleDb.OleDbDataAdapter(cmdSelect) Dim dS As New Data.DataSet LogWebActivity.LogThis(cmdSelect.CommandText, LogWebActivity.LogDetailLevel.Debugging) con.Open() LogWebActivity.LogThis("Opened Connection", LogWebActivity.LogDetailLevel.Debugging) adapter.Fill(dS, WorksheetName) LogWebActivity.LogThis("Filled DataAdapter", LogWebActivity.LogDetailLevel.Debugging) _SKUS = dS.Tables(WorksheetName) LogWebActivity.LogThis("Set SKUS", LogWebActivity.LogDetailLevel.Debugging)
I have made an application to read data from an excel file and save to database "Access". It's working perfect except one thing that..There are 4 columns in excel file as :
Shop no owner address alternate member address permanent member address
For all fields i have kept same datatype in database as "text". As in above image In Column A "shop no" in some rows there are same shop no with A,B,C classes. Now when I am uploading this excel file it saving all data, but in Column A cells which having only numbers would be saving to database eg: 185, 187, 188. And the cells which has both number and class eg: 185 A, 185 B would be reading as null values.
I am working on a comparison tool that will take an excel file and a .csv file and find differences. The user is going to be selecting the two files from their local machine via the .NET FileUpload control. The issue I am running into is that I don't want to write the files to the server first so using something like below won't work for me, as "Book1.xls" or even " + FilePath + " in place of it won't work. I need to be able to read the posted files into memory, then create maybe datatables of them, then do my comparison code.
[Code]....
I have some code that reads the PostedFile into a stream but I don't know how I can use that stream with the above code. Then I have some that can read the .csv file, but if it is an excel file, I get a bunch of Excel garbage along with the data...
[Code]....
How can I read into clean data from a FileUpload control of an Excel document?
I am using reading data from excel file and inserting into sql server using following article, it seems to be working correctly except when I do page refresh normally, it again execute the the btnUpload_click. I want that once the record imported and if user refresh the page again it should ask for enter file name again. let me know how do I resolve this? [URL]
I am usig ADO.NET to fetch a Dataset and then exporting the data to excel using Response.Write with response type set as excel. However when I run the application I am getting the following error in the browser."Cannot view XML input using XSL style sheet. Please correct the error and then click theRefresh button, or try again later."Anybody has any idea on why do we get this error and any pointers to resolve this error? By the way the same code does works on another machine, it's just not working on few machines.
Return myDataReader I have a function which is retuning a datareader to various sections of my website which is then being bound to dropdown lists, gridviews, etc. Normally the last line in a function when is the data reader and connection getting closed? Is it best practive to return a data reader or a dataset/datatable (or something even better than these two)?
I can directly use to read and display Excel or CSV files using asp web control(GridView, sqldatasource etc). I do NOT want to upload the file first to an sql database before reading them, I just want to read the files directly. I am using asp.net 2.0 and c#.
I need to read an Excel work book with 2 sheet which have more than 60,000 records. The application is an ASP.Net application so the performance matters.
Which approach should I take? Sohuld I do it using Microsoft.Office.Interop.Excel or should I do it using OLEDB in ADO.Net?
Codebehind: creating dataset, getting data from sql with adaptor and filling into dataset object, then setting detailsview's datasource. I add 2 button to above template to able to edit data at asp page, and added event handlers:buttons:
[Code]....
My question is, what to do inside the detailsview update event function to able to edit and update data. My method can be wrong too.
I create a gridview dynamically to fit the contents of a datatable...This form will have other dataatbles thrown at it but for the moment I am only working on one to work out the details of the process.I will post all my code below...the problem is this. The code successfully geenrates the gridview the way I want however...whenever I select a row all the cells including the header cells dissapears [code]...
i have retrieved data from the database and stored in dataset.i have to add an additonal row to it,update the data in data set and delete data from the data set..
In the above mentioned article at "[URL]" instead of selecting the existed table in dbserver is it possible to create a dynamic database table based upon the structure of the excel file to be loaded??
