.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

reading excel file problem

Posted By:      Posted Date: August 27, 2010    Points: 0   Category :ASP.Net


i have 200 rows in my excel file. im using OleDbConnection to read the excel file.

The problem is that it will read all the blank rows from row 200 onwards. Is there a configuration im missing ? or is there a way to import all rows that has data? Here's some of my code.

string excelConnectionString =
               "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filePath + ";" +
                "Extended Properties=Excel 8.0";

OleDbConnection excelConnection =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO'");

OleDbCommand cmd = new OleDbCommand("Select * from [list$]",excelConnection);

View Complete Post

More Related Resource Links

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Reading an excel file



i am trying to read an excel file.

when i read the entire file, it works fine.

but when i try to read a single column, i get the following exception message:

"Could not find installable ISAM".

the code i am using is:

string  connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  


"Data Source="+ fileName + ";" +


"Extended Properties=Excel 12.0;HDR=Yes";

OleDbConnection oledbConn = new OleDbConnection(connString);




// Open connection



// Create OleDbCommand object and select data from worksheet Sheet1

OleDbCommand cmd = new OleDbCommand

Problem when using Activator.CreateObject and reading App.Config file

Hello, this is the scenario. I have an application that load at runtime severals dll. these dll are actually "adapters" that wraps calls to web services, database and so on. The app.config of the "main" application stores in config sections all information necessary to each adapter (i.e.Web ServiceURL..) and I have created inside each adapter a class to ready those values the loading at run time the DLL has been implemented by storing all necessary types into a dictionary of generics and when I need to instanciate it I call the Activate.CreateObject and I pass the specific type (I means the name of the Adapter class) The problem is that the adapter class itself is not able to get the config value because ConfigSection has not been initialized. If I write: IInterfaceAdap adapt = new myAdapter() -> I read the config data IInterfaceAdap adapt = (IInterfaceAdap)Activator.CreateObject (type) -> now way to read the data stored in the App.Config. I think that I undertood the problem but I'm not able to find a workaround to read data store in the app.config!! I need help please!!  

reading excel file without saving to disk first

Having an issue.  I need to be able to read an excel file from a file upload control but I can not save the file to disk first, it must be done in memory.string excelConnectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; excelConnectionstring += filePath.Replace("/", "\\"); excelConnectionstring += ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"; OleDbConnection con = new OleDbConnection(excelConnectionstring); OleDbDataAdapter da = new OleDbDataAdapter();Above is my code for reading the data file if it IS saved to disk, but again, I have to be able to do this without saving the file to disk, it must be done in memory.  I have not been able to find any sample code anywhere on how to do this from memory, everything seems to force the file be uploaded, saved to disk, and then read in the connection string, which again I can not do.Any advise would be great, thanks in advance.  I'm really in a bind here.

Reading from Excel file : win form

Hi All, I have the following requirement  There are 10 Excel files in a folder.Each excel file contains only 1 column which contains severeal IDs listed. Now, I want to read those IDs in each & every excel file. How do I get the count of number of excel files present in that folder?? After getting the count, how do I read each Id present in that file?? Any help will be greatly apprecited.. Thanks, Alex

data type problem in import data from excel file


Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)

Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1


Problem with Linked servers from sql 2005 to Excel file


I am probably doing something stupid BUT I am creating a linked server to an excel file, I have done this on sql2005sp2x64 using the ace ole 12 provder, and replicated on another machine running sql2005sp2x32 using the jet4 provider and I am having the same problem on both.  I have turned allow inprocess option on on both providers.  I as an administrator on the machine where the database and datafile are residing have no problem accessing the linked server and the file underneath however any enduser that is not in the local administrators group gets the error   Cannot initialize the data source object of OLE DB provier "Either provider I have tried" for linked server    SQL Server Error 7303.  I have checked file permissions, and they are open to the world, I have tried both use existing authentication and use no authentication when accessing the linked server.  and I have tried endless different combinations of providers and provider strings.  I have went as far as having the enduser create a linked server to an excel file on his local machine (which he is admin on and I am not)   and he can access the file .. and I can not.  so I am just assuming there is some sort of security setting somewhere .. that I am just missing.



StreamReader.BaseStream.Read() problem in Random access reading of file


I am trying to randomly access a file but not fully understanding this class.

I have

 // Seek to the beginning of the field

System.IO.StreamReader srFile = new System.IO.StreamReader(_FileName.PathAndFileName);

srFile.BaseStream.Seek(FieldOffset, SeekOrigin.Begin);

// Read the field

int FieldLength = Field.FieldLength;  

int BUFFER_SIZE = (int)RecordLength;

byte[] bBuffer = new Byte[BUFFER_SIZE];

srFile.BaseStream.Read(bBuffer, 0, (int)FieldLength);

string FieldValue;
FieldValue = bBuffer.ToString();

but bBuffer is numbers like:

[0] 32

and the value of FieldValue is "System.Byte[]"

I'm trying to get text as this is a text file.


SSIS 2008: Problem reading dynamic file during execution



I have a SSIS 2008 project that reads a bunch of files from a file (always the same file) from Machine A.
My package is running on Machine B and this is where the information is being imported to, my Reporting DB (sql 2008) is also located in Machine B. 
I have a dynamic log file that stores the execution errors every time the package is executed and its name is made up of the timestamp of when package starts executing. The "DelayValidation" option for this connection is set to "TRUE". When i execute my Package from Visual Studio 2008, it runs without a problem.
The problem is when i deploy my package into SQL Server 2008 and try running it through DTEXEC. I always get the following error:


Error: 2010-11-03 14:57:29.06
   Code: 0xC001401E
   Source: MyImportPackage Connection manager "ImportLog"
   Description: The file name "\\ip-address\d$\Logs\ImportManager\20101103_145726.txt" specified in the connection was not valid.

Need help - reading hyperlinked page in excel file using c#


Ok, I understand how to use a connection string object to read excel sheets. What I don't know is how to follow a hyperlink inside excel, grab matching data from the linked page and return it to my database. Essentially, the spreadsheet has two columns:

Title  |  Class#

Class# has the hyperlink which links to an internal web site. The connected page has a course description and any pre-required classes that must be taken before you can take this class. This is where I'm rather stumped as I've not had to do this type of thing before. Any code, links or pointers would be appreciated. Many thanks.

Problem reading an XML file with a DTD attached


 Hello Everyone;

I have a very big issue that I could use some help on. I have a Handler that receives a transmission of an XML data through a HTTP Post. This works fine now but I have an issue reading the XML data and parsing it down. The following is a sample of the XML data that I have no control over. It comes from a third party so changing the data will not happen.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xiamSMS SYSTEM "xiamSMSMessage.dtd">
     <deliverRequest id="1">
           <from>John Doe</from>
           <to>Tim Burton</to>
           <content type="text">TEST DATA</content>

I have tried using an XMLTextReader to load the XML and parse it, but this seamed to fail on the DocType line of the XML due to the DTD file reference. After some more research I switched to the following code believing that it will solve the failing on the DocType of the DTD file.

XmlReaderSettings smsXMLset = new XmlReaderSettings();

Problem Loading Data from Excel file


hi all

i am loading Data From one my Excel file to DataTable. My Excel file has data in Following Format:

Vendor ID Invoice Number Invoice Date  Entity Invoice Amount
Remarks GL Account
5586   e5500222117 5/14/10  Entiti1                       3.000
some 210000
5586   5500231216 6/11/10  Entiti1                       4.000
hello 210000

Reading Excel File from document library in sharepoint 2010


On a button click,I want to read a excel file placed in document library in sharepoint 2010.The button is in a visual web part.

I am giving the connection string as:



connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=http://server_name/Doc librar

Reading and Writing Data to/from Excel File


I need to read and write data from excel sheet. Firstly for example I need to use the concept of accessing workgroup, sheet and then cells whatever I want.

For example I need to use cell A1+B1 and put the result in C1

alternatively I can use the same function above and put data in some other sheet of same excel file.

Please reply the answer either using C#.NET or asp.net using C#.NET

Problem in Date Format While Exporting To Excel Destination From Flat File Source In SSIS



I have a flat file(.csv) which contains data(strings incuding date in yyyy-mm-dd format).This i am using in Flat File Source and want to export those data to an Excel Destination.Before exporting to Excel Destination i have created a Derived Column component where i am using an express to get day,month,year part and creating a date sting in dd/mm/yyyy format then conveting it to DT_DATE datatype and then exporting to Excel Component.But while exporting to Excel this date format is getting changed to mm/dd/yyyy format.

The expression i used in derived column is

(DT_DATE)(SUBSTRING(date,9,2) + "/" + SUBSTRING(date,6,2) + "/" + SUBSTRING(date,1,4))

where date is a column from Flat File Source.

Even I changed the Locale to English(United Kingdom) in Flat File Connection Manager for the above .csv file.But still it didnt work.

I searched a lot in google and failed to get any solution on this.

Can anyone help me in solving this issue ?

Creating Excel file from C#

I used below coding for create a excel sheet.I got from on web site.
In below coding there is no problem.Its create the excel file.If i run second time,its ask to overwrite the current excel file.If I said "Yes" then it ok ,but If I select cancel button,its generate the Error message.The exception is

Convert excel file into xml


Hai, I have two columns with id,name in the excel sheet.I want to convert into xml file in the below format

<?xml version="1.0" encoding="utf-8"?>

<guide xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Boat id="1"> <name> Copper Kettle </name> </Boat> <Boat id="2"> <name> Copper Penny </name> </Boat> <Boat id="3"> <name> Copperhead </name> </Boat> <Boat id="4"> <name> Coppertone </name> </Boat> <Boat id="5"> <name> Copy Boy </name> </Boat> </guide>

suggest me with sample code in ASP.NET. Thanks

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend