SQL Server :: Read Contents Of Excel File To DataSet
Jan 12, 2011
I have a problem that I have been trying to solve the past two days, but I cannot figure it out...I have a folder which will have 60-70 Excel Files or CSV Files (they are right now). There are no headers in them, but they all have the same # of columns and the columns are in the same order in each fileI need to read each file in the folder, grab the data from the file and put it in a Database file. So all files will be read and then the data from them I guess put in a DataSet and then put into a Database (SQL).
I would like to know your libraries recommendations to read and write Excel applications on ASP .NET website. I have tried GemBox and NPOI, because they don't required Excel to be installed on the server, but they have some limitations on handling Excel files that are marked to read only.
using below code im reading excel sheet data by specifying the sheet name as [Project1$] but, how do i call the excel sheet without specifying the sheet name so, that it will ready any files.
I have a requirement to read the excel sheet contents(with first row as header) and to populate it in a Datagrid. so as to manipulate the excel sheet data. But i get an error saying "Could not find installable ISAM". after a big struggle i came to know that the culprit is the "connection string". I tried with various connection strings and now i get the error " Format of the Initialization string does not conform to specification starting at index 121".
I want to read write excel file in my application without installing the office on my server. Means my server don't have any excel (MS office) installation. Is it necessory to install the office on server to excute the program of read or write the excel file.
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 want to read a pdf file which contains empid and code for 100 nos.. in front end I'll give specific empid..then the corresponding code has to be displayed in the textbox by reading pdf.. I know this can be done by itesxtsharp.dll and regex..
I am trying to get a .txt file to be read by my server application. My problem is that i cannot get it to read user information. I would like for my application to do something like if there is a "#", to read the next 2 lines down for more information.
I have created an excel sheet from datatable using function. I want to read the excel sheet programatically using the below connectionstring. This string works fine for all other excel sheets but not for the one i created using the function. I guess it is because of excel version problem.
OleDbConnection conn= new OleDbConnection("Data Source='" + path +"';provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";);
which i can create an excel sheet such that it is readable again using above query. I cannot use Microsoft InterOp library as it is not supported by my host. I have even changed different encoding formats. Still it doesnt work
I have excel file.I want to fill dataset from that excel file & Have to fill the database. I m able to read the excel file,but there is one problem with column whose datatype is Date.
[Code]....
I have attached the excel file (Pic1).
In Pic2,I have attached the snapshot of dataset that is filled.
In C# ASP.NET 3.5 web application, I need to export multiple datatables (or a dataset) to an Excel 2007 file with multiple sheets, and then provide the user with 'Open/Save' dialog box, WITHOUT saving the Excel file on the web server.
I have used Excel Interop before. I have been reading that it's not efficient and is not the best approach to achieve this and there are more ways to do it, 2 of them being: 1) Converting data in datatables to an XML string that Excel understands 2) Using OPEN XML SDK 2.0.
It looks like OPEN XML SDK 2.0 is better, please let me know. Are there any other ways to do it? I don't want to use any third-party tools.
If I use OPEN XML SDK, it creates an excel file, right? I don't want to save it on the (Windows 2003) server hard drive (I don't want to use Server.MapPath, these Excel files are dynamically created, and they are not required on the server, once client gets them). I directly want to prompt the user to open/save it. I know how to do it when the 'XML string' approach is used.
I a now Porting a data from Uploaded Excel File to database. In that, I need to check "Sheet name" Of that Excel file Whether it is Sheet1 or Sheet2 or something else...
I have a web app where user uploads a file.ile is saved in server locally and I want to read some data from it.Currently my development server has office 2003.So do I need office 2007 in order to be able to read an excel from office 2007 ?
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wrkBook; // = new Microsoft.Office.Interop.Excel.Workbook();
I want to read and write excel file . it can do with oledb , there is a limitation maximunm characters < 255 solution is mono data type , now create table become second sheet (when open excel it is shown as hidden.)
I have developed an application where I need to create a page to read data from Excel File and then update it to the database. The code that I wrote works only when the application runs from my machine. When I deployed the application to the server it gave an error- 'C:PRFileIimport_File.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. here is mu code -
protected void InsertData(object sender, EventArgs e) { OleDbConnection oConn = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PuertoRicoFile\Puertorico_Iimport_File.xls ; Extended Properties=Excel 8.0 "); OleDbCommand oCmd = new OleDbCommand("select * from [Puertorico_Data$]", oConn); oConn.Open(); //Here [Puertorico_Data$] is the name of the sheet in the Excel file where the data is present OleDbDataReader oleDbReader = oCmd.ExecuteReader(); string AgentIP = ""; string TagName = ""; string Status = ""; string Severity = ""; int EventCount = 0; string EarliestEvent = ""; string LatestEvent = ""; while (oleDbReader.Read()) { AgentIP = (string)oleDbReader[0]; // valid(oleDbReader, 0);//Here we are calling the valid method TagName = (string)oleDbReader[1]; //valid(oleDbReader, 1); Status = (string)oleDbReader[2]; //valid(oleDbReader, 2); Severity = (string)oleDbReader[3]; //valid(oleDbReader, 3); EventCount = Convert.ToInt32(oleDbReader[4]); //oleDbReader[0]; //valid(oleDbReader, 4); EarliestEvent = (string)oleDbReader[8]; //validate(oleDbReader, 8); EarliestEvent = EarliestEvent.Substring(0, 19); LatestEvent = (string)oleDbReader[9]; //validate(oleDbReader, 9); LatestEvent = EarliestEvent.Substring(0, 19); InsertDataIntoSql(AgentIP, TagName, Status, Severity, EventCount, EarliestEvent, LatestEvent); } SqlConnection mySQLconnection = new SqlConnection(ConfigurationManager.ConnectionStrings["IssNetworkReportingSystemConnString"].ConnectionString); mySQLconnection.Open(); SqlCommand cmdSql = new SqlCommand("AddMonth_Event_TotalInfo_for_Puertorico", mySQLconnection); cmdSql.CommandType = CommandType.StoredProcedure; dbReader = cmdSql.ExecuteReader(); dbReader.Close(); cmdSql = new SqlCommand("AddQtr_Event_TotalInfo_for_Puertorico", mySQLconnection); cmdSql.CommandType = CommandType.StoredProcedure; dbReader = cmdSql.ExecuteReader(); dbReader.Close(); oConn.Close(); lblMsg.Text = "Data Imported Sucessfully"; lblMsg.ForeColor = System.Drawing.Color.Green; } public void InsertDataIntoSql(string agentIP, string tagName, string Stat,string severity,int eventCount, string eEvent, string lEvent) {//inserting data into the Sql Server SqlConnection mySQLconnection = new SqlConnection(ConfigurationManager.ConnectionStrings["IssNetworkReportingSystemConnString"].ConnectionString); mySQLconnection.Open(); SqlCommand sqlSelect = new SqlCommand("SELECT SeverityID from Severity where SeverityDesc=@SeverityDesc",mySQLconnection); sqlSelect.CommandType = CommandType.Text; sqlSelect.Parameters.Add("@SeverityDesc", SqlDbType.NVarChar).Value = severity; dbReader = sqlSelect.ExecuteReader(); int severityId = Convert.ToInt32(dbReader.Read()); dbReader.Close(); SqlCommand sqlInsert = new SqlCommand("Insert into IdsData_Puertorico(AgentIP,TagName,Status,SeverityID,EventCount,EarliestEvent,LatestEvent)" + " values (@AgentIP,@TagName,@Status,@SeverityID,@EventCount,@EarliestEvent,@LatestEvent )" ,mySQLconnection); sqlInsert.CommandTimeout = 0; //timeout unlimited sqlInsert.CommandType = CommandType.Text; sqlInsert.Parameters.Add("@AgentIP", SqlDbType.NVarChar).Value = agentIP; sqlInsert.Parameters.Add("@TagName", SqlDbType.NVarChar).Value = tagName; sqlInsert.Parameters.Add("@Status", SqlDbType.NVarChar).Value = Stat; sqlInsert.Parameters.Add("@SeverityID", SqlDbType.Int).Value = Convert.ToInt32(severityId); sqlInsert.Parameters.Add("@EventCount", SqlDbType.Int).Value = Convert.ToInt32(eventCount); sqlInsert.Parameters.Add("@EarliestEvent", SqlDbType.DateTime).Value = DateTime.Parse(eEvent); sqlInsert.Parameters.Add("@LatestEvent", SqlDbType.DateTime).Value = DateTime.Parse(lEvent); sqlInsert.CommandType = CommandType.Text; sqlInsert.ExecuteNonQuery(); mySQLconnection.Close(); }
Iām trying to read images from an excel file using OleDbDataReader. My excel file has 6 columns of data, the first 5 are all text but the last is image. While Iām reading the record, It's doing fine on the first 5 columns but return me with a for the sixth column.
