Home » MS Office

CSV Reader through code is not reading the first row which contain Headings


I'm using C# to get the data from the CSV file. For this, I'm using the following approach.

//Get data from csv fileprivate DataSet GetData(byte[] csvcontent)
      string strLine;
      string[] strArray;
      char[] charArray = newchar[] { ',' };
      DataSet ds = new DataSet();
      DataTable dt = ds.Tables.Add("TheData");
      MemoryStream reader = new MemoryStream(csvcontent);
      StreamReader sr = new StreamReader(reader);

      strLine = sr.ReadLine();

      strArray = strLine.Split(charArray);

      for (int x = 0; x <= strArray.GetUpperBound(0); x++)

      strLine = sr.ReadLine();
      while (strLine != null)
        strArray = strLine.Split(charArray);
        DataRow dr = dt.NewRow();
        for (int i = 0; i <= strArray.GetUpperBound(0); i++)
          dr[i] = strArray[i].Trim();
        strLine = sr.ReadLine();
      return ds;

I'm calling the above function link this:

//Extract data from csv file
              SPFile DocFile = myLibraryItem.File;
              byte[] csvcontent = DocFile.OpenBinary();
              DataSet ds = GetData(csvcontent);
              DataTable dt = ds.Tables[0];

Now, the problem is that it is not reading the first row of the CSV file which contain headings. Anyone has any idea how we can do this ?



1 Answer Found


Answer 1

Looking at the code  I think that it actually does read them, but it doesn't put the headers in as a first row, it uses the heathers to name the columns  in the datatable.


It does that here:

   strArray = strLine.Split(charArray);

   for (int x = 0; x <= strArray.GetUpperBound(0); x++)

Also, you might want to have a look at Linq2CSV, which does what you need, and a couple of other things:




HI Guys,

I have written code using C# to retrive and print string from a CSV file.

I just want to know how to delete each row once it has been printed.

row.delete();  // is this right?

Pleae find the parto of the coding below

string path = "D:\\My.csv";

                int i = 0;

                    using (StreamReader readFile = newStreamReader(path))


                        string line;

                        string[] row;


                        while ((line = readFile.ReadLine()) != null)


                            row = line.Split(',');



                        String fpsIdentifier ="" , rfpsIdentifier ="", D_Value = "";



                                fpsIdentifier = row[0].ToString();

                                rfpsIdentifier = row[1].ToString();

                                D_Value = row[2].ToString();



I need to read a csv file, that looks something like this:

“Code”,”Description”,”Price 1”, ”Price 2”, ”Price 3”, ”Price 4”, ”Price 5”,”Weighed Flag”,”Best before days”
“2012345”, “Test Product”, 12.56,0,0,0,0,1,8
“2012346”, “Test Product 2”, 1.23,0,0,0,0,0,0
“2012347”, “Test Product 3”, 3.45,0,0,0,0,0,1

and then re-export it as a csv, so it looks like this:

Plu_No, Line1, UnitPrice, Weight, Best Before,
2012345, "Test Product",12.56, 1, 8
2012346, “Test Product 2”, 1.23,0,0,0,0,0,0
2012347, “Test Product 3”, 3.45,0,0,0,0,0,1

So that means,

"Code" becomes Plu_No

"Description" becomes Line 1

"Price 1" becomes UnitPrice

"Best before days" becomes Best Before.

It also means I have to lose Price 2, Price 3, Price 4 and Price 5.

I've decided to use streamreader to read the file, but does anyone have an idea for the best way to then manipulate that data, and re-export in the format I've specified?

I was thinking of using string builder and then appending the strings, but I'm not sure if thats the best approach.




Finally got a copy or VS2010 and while exploring around came across this little nugget.... ExpandoObject

ExpandoObjects are classes that allow you to add properties and methods to them dynamically at runtime. So imagine a CSV file like this....

FirstName, LastName
John, Smith
John, Doe

You might think a Person class with two properties FirstName and LastName is needed... not any more.

Use this CsvReader class and it will build a collection of objects that has properties created from the CSV file !!


PublicClass CsvReader

    ''' <summary>''' Reads a CSV file and returns a collection of rows.''' The CSV should contain a header row, these headers are used for property names''' </summary>''' <param name="filename"></param>''' <returns></returns>PublicSharedFunction ReadCsvFile(ByVal filename AsString, OptionalByVal delimiter AsChar = ",") As IList(Of Object)
      Dim rows AsNew List(Of Object)
      Dim reader As StreamReader = File.OpenText(filename)

      Dim headers() AsString = reader.ReadLine().Split(delimiter)
      While reader.EndOfStream = FalseDim values() AsString = reader.ReadLine().Split(delimiter)
        rows.Add(CreateRow(headers, values))

      Return rows
    EndFunctionPrivateSharedFunction CreateRow(ByVal headers() AsString, ByVal values() AsString) AsObjectDim row As IDictionary(Of String, Object) = New ExpandoObject()
      For index AsInteger = 0 To headers.Length - 1
        row(Sanitize(headers(index))) = values(index).Trim
      NextReturn row
    EndFunctionPrivateSharedFunction Sanitize(ByVal PropertyName AsString) AsStringDim cleaned AsString = PropertyName.Trim
      Return cleaned.Replace(" ", "_")

The CSV file above can be read like this....

Sub Main()

    ForEach item In CsvReader.ReadCsvFile("SamplePeople.csv")
The items FirstName property (and the LastName property) is created at run time from the CSV files content... what about a CSV file like this...

Album, Artist, Genre
UFOrb, The Orb, Ambient
Insides, Orbital, Dance

Read it in like this.....use the properties Album, Artist and Genre ...

Sub Main()

    ForEach item In CsvReader.ReadCsvFile("SampleAlbums.csv")

No need to create a class ... excellent.

ExpandoObjects !!!


With great power comes great responsibility.




this.UIMap.LoginParams.UIUserNameEditText = testContextInstance.DataRow["UserName"].ToString();
this.UIMap.LoginParams.UIPasswordEditPassword = testContextInstance.DataRow["Password"].ToString();

where login method contains action for inserting data into username editbox, password and clicking on the LOGIN button

1. using the above code i m facing one problem - only username is inserted in the editbox (all the iteration for the number of rows present in csv file), but not the password resulting failure of testcase.

2. i want to execute the code for only one data row, whereas file contains 3 data rows.


Does anyone know how to delete row in a CSV file where there is a null value in a partcular column?  I am using VB.  There are three columns I am using Post Date, Account Number, and Credit Amount.  I want to delete all rows with null values in the Account Number column.  I am very new to VB, took one class. 

I am using the Microsoft.VisualBasic.FileIO.TextFieldParser

This is what I have so far...








PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles fixfileButton.Click



Using MyReader AsNew Microsoft.VisualBasic.FileIO.TextFieldParser _






'Specify that reading from a comma-delimited file'

MyReader.TextFieldType = FileIO.








Dim currentRow AsString()



WhileNot MyReader.EndOfData




currentRow = MyReader.ReadFields()



ForEach currentField AsStringIn currentRow







Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException



"Line " & ex.Message & _



"is not valid and will be skipped.")






















Hi guys,


I have excel sheet with 4000 rows. I need code using C# for following.


1. Read coulmn B and D from beginning and print the value untill end of file.




The LINQ VB code below is to read the first record data for database table ProductTable that has 3 colounms (or 3 fields): TableCol1, TableCol2, TableCol3. The reading data is stored at variable MyArrayFirstRow.


‘-------start LINQ VB code in Visual Studio Web Developer 2008-------‘

Dim MyArrayFirstRow AsNew ArrayList()                

Dim dc = New AzDataClassesDataContext()

Dim Az = From p In dc.ProductTable Select p       

ForEach p In Az




    Exit For     ‘because only to record first row


‘-------end of LINQ VB code in Visual Studio Web Developer 2008-------‘


LINQ VB code above is ok for database table that has a few colounms like in example (3 colounms). But for database table that has many colounms (for example 100 colounms), then above code should not be appropriate way.


Does someone has proper template LINQ VB code to read database table for many colounms (for example 100 colounms)? The code should be work on VS Web Developer.


I am looking forwards your suggestion.



 entry101130132until 260
 skyway  slex skyway  slex 
exitspamt spvattotspamt2spvat2tot2spamt spvattotspamt2spvat2tot2
Untill 260





















I have a VB procedure that does a  TransferSpreadsheet acImport,,TableName,Filename,Fieldnames,True . Fieldnames is a check box that is either true or false if the input file has field names in the first row. When the spreadsheet data is imported into a new table I have found that one or more fields have indexes created. Can anyone tell me where this is coming from or what is causing it? If I import without the fieldnames, no indexes are created. This is simple spreadsheet data and I don't want indexes on any field in the table as the program may or may not do field deletes or table def manipulation. At this point I have tested a procedure to delete the indexes. Anyone have info to shine on this problem.




Access 2003. I am using a crosstab query to generate a report that shows the count of certain records. First I created the query that returns all of the records I need to count:

PARAMETERS [Enter 4 digit year:] Text ( 255 );
SELECT qryHearings.HearingDate, qryCharges.ChargeCode, qryCharges.Injury, DatePart("m",[HearingDate]) AS MO, DatePart("yyyy",[HearingDate]) AS YR, qryCharges.Finding
FROM qryHearings INNER JOIN qryCharges ON qryHearings.HearingID = qryCharges.HearingID
WHERE (((qryCharges.ChargeCode)="04" Or (qryCharges.ChargeCode)="52" Or (qryCharges.ChargeCode)="08") AND ((DatePart("yyyy",[HearingDate]))=[Enter 4 digit year:]) AND ((qryCharges.Finding)="G"));

Then I put it in the cross tab query:

TRANSFORM Count(qryStaffAssaults.ChargeCode) AS ChargeCode
SELECT qryStaffAssaults.ChargeCode, qryStaffAssaults.Injury, Count(qryStaffAssaults.ChargeCode) AS YR_Total
FROM qryStaffAssaults
GROUP BY qryStaffAssaults.ChargeCode, qryStaffAssaults.Injury
PIVOT qryStaffAssaults.MO In (1,2,3,4,5,6,7,8,9,10,11,12);

The result looks like:

ChargeCode Injury YR_Total  1  2   3 etc...
04               True              4  1   2   1
08               False           22  10 10  2

My problems are these:
1. If there are no records for a ChargeCode, no row is returned at all. How can I get a row returned with a "0" count?

2. The row headings under charge code will be meaningless to users and I have to replace them with a description like "Number of staff assaults with serious blah blah..." What is the best way to do this? I tried setting the crosstab query as the record source of a subreport, then using labels for the row headers on the main report that line up with the rows of the subreport, but if a row isn't returned because of the above issue, nothing lines up. Also, some of the labels will be multiple lined creating a discrepancy in row height between the row heading label and the row in the subreport. Is there another way to accomplish this?

Many thanks,



Hello! I would like to be able to read rows from an excel file one by one, and update each row as i iterate through them.

Something like this below, except that DbDataReader only reads data, it is't able to write (obviously).

command.CommandText = "SELECT * FROM ["
 + sheet + "]"
DbDataReader reader = command.ExecuteReader()

//For each row in excel document
while (reader.Read()) {   //1. Read row data
  //2. Do some calculations based on the data
  //3. Write back new data

How can I achieve this?

My current solution uses "UPDATE X SET A = b WHERE X = y & Y = z" and has a time complexity of O(n^2). Using the above example would be an insane performance increase.

Also, I don't have Excel installed and don't plan to.



I have a column ("Notes") in a sqlexpress database as nvarchar(1000).  It will basically hold notes. It is pulled froma richtextbox control.


Using vb, if I add a record to the table, all the data for all columns, including notes saves just fine.  If I go into SSMS, the data is in there as expected.  Though in the Notes column, the data is preceded by System.Windows.Forms.RichTextBox, Text: . 

When I try and read in the data, it errors out on the Index Column.  I get an 'Index Out of Range'.  if I try and cast it to a string, it does the same thing.  SHould a nvarchar that large be a different datatype in VB? SHould it be different because it's coming from a richtextbox control?


Thanks for the help!


Hi you giy helped me to get this to work. Thank you I just have one more question that I do not seem to be able to find anywhere.

How can I get coulmn names (headings) in the Excel sheet here?

Thank you

Here is my code:


Declare @bcp varchar(150)

SELECT @bcp = 'bcp " Select [CompanyName] from NOTTHWIND.dbo.Customers " queryout C:\AestEd2.xls -T -c -S' + @@servername

exec Master..xp_cmdshell @bcp




Hi, I am getting this error and I have no idea how to fix it.


The output char buffer is too small to contain the decoded characters, encoding 'Unicode (UTF-8)' fallback 'System.Text.DecoderReplacementFallback'.

Parameter name: chars



This is my code:


BinaryReader br = new BinaryReader(File.OpenRead(ofd.FileName));
        br.BaseStream.Position = 0x37E;
        if (br.ReadByte() == 0x01) listStartOffset = 0xD000;
        elseif (br.ReadByte() == 0x02) listStartOffset = 0xE000;
        tableReaderOffset = listStartOffset;
        br.BaseStream.Position = listStartOffset;
        char[] firstFileHolder = br.ReadChars(28);
        string firstFile = newstring(firstFileHolder);
        string fileName = String.Empty;
        while (true)
          fileName = "";
          //The offset of the reader
          br.BaseStream.Position = tableReaderOffset;
          //Read FileNamefor (int i = 0; i <= 28; i++)
            br.BaseStream.Position = tableReaderOffset + i;
            fileName += br.ReadChar().ToString();//<-----------The error is here

Any help would be appreciated, thanks.

If you need any more info, just ask.


With the default .csv export and the added pipe delimited .txt version, they both include the field names as the 1st row.  I need to export to these file types only the data.

What needs to be done to create a export option that DOESN'T include the header row?




I was wondering if someone could help me.
I searched the Internet, but could find good examples/tutorials.
Well I want to open a .CSV file, then display it in a DataGrid, and I want to sort them.
So I was thinking about Open the .CSV file and then adding it to a SQL database.
But I'm not sure how to start, so that why I was looking for some examples/tutorials for open .CSV.

I'm pretty new with VB 2008, but in the past I did a lot PHP and SQL.



	DataSet ds = new DataSet();

	OleDbConnection conn = conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data.csv;Extended Properties="Excel 12.0 Xml;HDR=YES");


	OleDbDataAdapter oda = new OleDbDataAdapter("select * from [sheet1$]", conn); 





catch(Exception ex)




I want to  import csv file into datagrid by using c#.net, now i can get the file path, but i dont know how to read csv file and bind to datagrid to display?

i think i got error at this code : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data.csv;Extended Properties="Excel 12.0 Xml;HDR=YES");

anyone help~



Is it possible to read CSV file  using Entiry Framework 4 such that it should give me an entity that I can use it normally within my application.




Is it possible to read CSV file  using Entiry Framework 4 such that it should give me an entity that I can use it normally within my application.




I am reading a .csv file using Oledbdatareader. As

            string file = "C:\\CMPNAME.csv";
            string dir = Path.GetDirectoryName(file);
            string excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
   dir + @";Extended Properties=""Text;HDR=No;FMT=Delimited""";
            OleDbConnection conn = new OleDbConnection(excelConn);
            string query = "SELECT * FROM [" + file+"]";

            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())

It was failing at "OleDbDataReader reader = cmd.ExecuteReader();" by giving error as "The Microsoft Jet database engine could not find the object 'C:\CMPNAME.csv'.  Make sure the object exists and that you spell its name and the path name correctly."(System.Data.OleDb.OleDbException was unhandled)

Can any one help me.

Thanks in Advance



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure