Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using StreamReader to fetch the value

Posted on 2013-06-28
13
Medium Priority
?
403 Views
Last Modified: 2013-07-01
Using StreamReader, I am trying to fetch the values from CSV file to a DataTable.

It can be DataTable or any Collection Class or Structure to which the value has to be assigned.

In my below syntax after While () Statement, i wrote an array - foreach syntax to get value by value and assign to a datacolumn. My syntax is completely incorrect and so  i am not able to get the values and assign to the datatable. How should i read the CSV file and get the value to the datacolumn

 public static DataTable GetInvoiceHeader(string strFileName)
        {
            DataTable dtInvoiceHeader = new DataTable();
            dtInvoiceHeader.Columns.Add("Invoice_Date", typeof(DateTime));
            dtInvoiceHeader.Columns.Add("Invoice_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("Account_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("DEA_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("Sales_Rep", typeof(long));
            try
            {
                using (StreamReader sr = new StreamReader(strFileName))
                {
                    string line;
                    line = sr.ReadLine();
                    while ((line = sr.ReadLine()) != "Invoice Date,Invoice Number,Account Number,DEA Number,Sales Rep,,,,,,,,,,")
                    {                       
                    }
                    // Split on these
                    char[] commadelimiter = new char[] { ',' };   
                    
                    string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);
                    foreach (string strValue in strArray)
                    {
                        MessageBox.Show(strValue);
                    }
                }
                    
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dtInvoiceHeader;
        }

    }

Open in new window


I am expecting the output to be. these values are there in txt fie

Invoice_Date   =   5/30/2013
Invoice_Number = 1000
Account_Number = 0
DEA_Number =BM00000000
Sales_Rep = 928
FileStream.txt
0
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1000 total points
ID: 39285795
Remove This:
foreach (string strValue in strArray)
                    {
                        MessageBox.Show(strValue);
                    }

Add this in it's place:


DataRow dr = dtInvoiceHeader.NewRow();

dr["Invoice_Date"] = strValue[0];
dr["Invoice_Number"] = strValue[1];
//etc.

dtInvoiceHeader.Rows.Add(dr);

//next line.
0
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 500 total points
ID: 39285814
Small correction to ged325's suggestion:  You need to convert your types since you are working with string values, but your DataTable has types other than string.

e.g.

dr["Invoice_Date"] = Convert.ToDateTime(strValue[0]);
dr["Invoice_Number"] = Convert.ToInt64(strValue[1]);
//etc.

Open in new window

0
 

Author Comment

by:chokka
ID: 39285847
Thanks but the issue is my syntax is fetching the header field.

I am expecting something like - StreamReader.ReadNextLine

Because only in the next line we have the actual field values for those Headers
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:chokka
ID: 39285860
I fixed that issue by this syntax

 line = sr.ReadLine() + 1;

But still i am facing TypeCast Error for DateTime

 public static DataTable GetInvoiceHeader(string strFileName)
        {
            DataTable dtInvoiceHeader = new DataTable();
            dtInvoiceHeader.Columns.Add("Invoice_Date", typeof(DateTime));
            dtInvoiceHeader.Columns.Add("Invoice_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("Account_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("DEA_Number", typeof(long));
            dtInvoiceHeader.Columns.Add("Sales_Rep", typeof(long));
            try
            {
                using (StreamReader sr = new StreamReader(strFileName))
                {
                    string line;
                    line = sr.ReadLine();
                    while ((line = sr.ReadLine()) != "Invoice Date,Invoice Number,Account Number,DEA Number,Sales Rep,,,,,,,,,,")
                    {                       
                    }
                    // Split on these
                    line = sr.ReadLine() + 1;

                    char[] commadelimiter = new char[] { ',' };   
                    
                    string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);
                    foreach (string strValue in strArray)
                    {
                        DataRow dr = dtInvoiceHeader.NewRow();

                        dr["Invoice_Date"] = Convert.ToDateTime(strValue[0]);
                        dr["Invoice_Number"] = Convert.ToInt64(strValue[1]);
                        dr["Account_Number"] = Convert.ToInt64(strValue[2]);
                        dr["DEA_Number"] = Convert.ToInt64(strValue[3]);
                        dr["Sales_Rep"] = Convert.ToInt64(strValue[4]);
                        dtInvoiceHeader.Rows.Add(dr);
                    }
                }
                    
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dtInvoiceHeader;
        }

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39285867
What does a line of text from the file look like?
0
 

Author Comment

by:chokka
ID: 39285888
Foreach iteration for Array is not helping me. I change DataColumn datatype to String in order to test the functionality

What's happening is that, Every time foreach loop is sorting only
strValue is picking strArray[0] for first round of iteration

strValue is picking strArray[1] for second round of iteration.

Value is assigned uniformly to all the datacolumns and creating multiple rows of record.

In the end, it throws Index out of bound error.



 public static DataTable GetInvoiceHeader(string strFileName)
        {
            DataTable dtInvoiceHeader = new DataTable();
            dtInvoiceHeader.Columns.Add("Invoice_Date", typeof(string));
            dtInvoiceHeader.Columns.Add("Invoice_Number", typeof(string));
            dtInvoiceHeader.Columns.Add("Account_Number", typeof(string));
            dtInvoiceHeader.Columns.Add("DEA_Number", typeof(string));
            dtInvoiceHeader.Columns.Add("Sales_Rep", typeof(string));
            try
            {
                using (StreamReader sr = new StreamReader(strFileName))
                {
                    string line;
                    line = sr.ReadLine();
                    while ((line = sr.ReadLine()) != "Invoice Date,Invoice Number,Account Number,DEA Number,Sales Rep,,,,,,,,,,")
                    {                       
                    }
                    // Split on these
                    line = sr.ReadLine() + 1;

                    char[] commadelimiter = new char[] { ',' };   
                    
                    string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);
                    foreach (string strValue in strArray)
                    {
                        DataRow dr = dtInvoiceHeader.NewRow();

                        dr["Invoice_Date"] = strValue;
                        dr["Invoice_Number"] = strValue;
                        dr["Account_Number"] = strValue;
                        dr["DEA_Number"] = strValue;
                        dr["Sales_Rep"] = strValue;
                        dtInvoiceHeader.Rows.Add(dr);
                    }
                    
                }
                    
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dtInvoiceHeader;
        }

    }

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39285981
What's happening is that, Every time foreach loop is sorting only
strValue is picking strArray[0] for first round of iteration

strValue is picking strArray[1] for second round of iteration.
Well yeah...that's how you wrote it  = )

A for(each) loop isn't going to help you here. You'll need to set the indexes to index the array manually (i.e. sans loop). ged325 showed you this in his first post.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 500 total points
ID: 39287200
Change this

foreach (string strValue in strArray)
                    {
                        DataRow dr = dtInvoiceHeader.NewRow();

                        dr["Invoice_Date"] = strValue;
                        dr["Invoice_Number"] = strValue;
                        dr["Account_Number"] = strValue;
                        dr["DEA_Number"] = strValue;
                        dr["Sales_Rep"] = strValue;
                        dtInvoiceHeader.Rows.Add(dr);
                    }

to

DataRow dr = dtInvoiceHeader.NewRow();
for(int j = 0, j<strArray.Length - 1, j++)
 {
  dr[j] = strArray[j];
 }
dtInvoiceHeader.Rows.Add(dr);

This assumes that order of columns in file and datatable is same.
0
 

Author Comment

by:chokka
ID: 39290544
Thank you CodeCruiser.

I fixed the syntax by

 for (int i = 0; i < (strArray.Length + 1) - strArray.Length; i++)
                    {  }

Open in new window



From the .csv file, i am collecting two rows of record as follows

Invoice Detail Report,,,,,,,,,,,,,,
Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension
1,1,,EACH,ADVAIR DISKUS,NDC#:00173-0696-00,STR:250-50MCG,FM:ARIN,SZ:60 EA,0,,6/30/2013,0,$82.58 ,$82.58
1,1,,EACH,ADVAIR DISKUS,NDC#:00173-0695-00,STR:100-50MCG,FM:ARIN,SZ:60 EA,0,,6/30/2013,0,$65.29 ,$65.29

Syntax which i wrote picks only first row of record and exit out of the loop. Syntax is not picking the second row of record.

I believe, i am making a mistake in my While loop. I need to fix it. I also attached the original csv file to this posting.


 public static DataTable GetInvoiceDetail(string strFileName)
        {
            DataTable dtInvoiceDetail = new DataTable();
            dtInvoiceDetail.Columns.Add("Ship_Qty", typeof(string));
            dtInvoiceDetail.Columns.Add("Order_Qty", typeof(string));
            dtInvoiceDetail.Columns.Add("Code", typeof(string));
            dtInvoiceDetail.Columns.Add("UM", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank1", typeof(string));
            dtInvoiceDetail.Columns.Add("Description", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank2", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank3", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank4", typeof(string));
            dtInvoiceDetail.Columns.Add("CIN", typeof(string));
            dtInvoiceDetail.Columns.Add("Due_Date", typeof(string));
            dtInvoiceDetail.Columns.Add("DEA_Class", typeof(string));
            dtInvoiceDetail.Columns.Add("Unit_Price", typeof(string));
            dtInvoiceDetail.Columns.Add("Extension", typeof(string));
            try
            {

                using (StreamReader sr = new StreamReader(strFileName))
                {

                    string line;
                    line = sr.ReadLine();
                 

                    while ((line = sr.ReadLine()) != "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension")
                    {
                    }
                    // Split on these
                    line = sr.ReadLine() + 1;

                    char[] commadelimiter = new char[] { ',' };

                    string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);

                    for (int i = 0; i < (strArray.Length + 1) - strArray.Length; i++)
                    {
                        DataRow dr = dtInvoiceDetail.NewRow();

                        dr["Ship_Qty"] = strArray[0];
                        dr["Order_Qty"] = strArray[1];
                        dr["Code"] = strArray[2];
                        dr["UM"] = strArray[3];
                        dr["Blank1"] = strArray[4];
                        dr["Description"] = strArray[5];
                        dr["Blank2"] = strArray[6];
                        dr["Blank3"] = strArray[7];
                        dr["Blank4"] = strArray[8];
                        dr["CIN"] = strArray[9];
                        dr["Due_Date"] = strArray[10];
                        dr["DEA_Class"] = strArray[11];
                        dr["Unit_Price"] = strArray[12];
                        dr["Extension"] = strArray[13];
                        dtInvoiceDetail.Rows.Add(dr);
                    }

                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dtInvoiceDetail;
        }

    }

Open in new window

Test-6619289--5-30-2013.csv
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39290697
 
            using (StreamReader sr = new StreamReader(strFileName))
            {
                string line;
                line = sr.ReadLine();


                while ((line = sr.ReadLine()) != "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension")
                {
                }

                while (!sr.EndOfStream)
                {
                    // Split on these
                    line = sr.ReadLine() + 1;

                    char[] commadelimiter = new char[] { ',' };

                    string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);

                    for (int i = 0; i < (strArray.Length + 1) - strArray.Length; i++)
                    {
                        DataRow dr = dtInvoiceDetail.NewRow();

                        dr["Ship_Qty"] = strArray[0];
                        dr["Order_Qty"] = strArray[1];
                        dr["Code"] = strArray[2];
                        dr["UM"] = strArray[3];
                        dr["Blank1"] = strArray[4];
                        dr["Description"] = strArray[5];
                        dr["Blank2"] = strArray[6];
                        dr["Blank3"] = strArray[7];
                        dr["Blank4"] = strArray[8];
                        dr["CIN"] = strArray[9];
                        dr["Due_Date"] = strArray[10];
                        dr["DEA_Class"] = strArray[11];
                        dr["Unit_Price"] = strArray[12];
                        dr["Extension"] = strArray[13];
                        dtInvoiceDetail.Rows.Add(dr);
                    }
                }
            } 

Open in new window

0
 

Author Comment

by:chokka
ID: 39290714
Thank you ged325

While (!sr.EndOfStream) picks all the following row of records.

Ideally, i need to pick only  those two row of records under the row :  "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension"


Using End of Stream, all the rows of records till the last row of records in the spreadsheet is picked.

 while ((line = sr.ReadLine()) != "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension")
                {
                }

                while (!sr.EndOfStream)
                {
                    // rest of code in here.
                }

Open in new window

0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 1000 total points
ID: 39290991
At this at the end of the while loop before going to the next line.

if (strArray[0] == "Report Total:")
  break;
0
 

Author Comment

by:chokka
ID: 39291096
Syntax is fixed. Thank you ged325,Kaufmed and codecruiser


public static DataTable GetInvoiceDetail(string strFileName)
        {
            DataTable dtInvoiceDetail = new DataTable();
            dtInvoiceDetail.Columns.Add("Ship_Qty", typeof(string));
            dtInvoiceDetail.Columns.Add("Order_Qty", typeof(string));
            dtInvoiceDetail.Columns.Add("Code", typeof(string));
            dtInvoiceDetail.Columns.Add("UM", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank1", typeof(string));
            dtInvoiceDetail.Columns.Add("Description", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank2", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank3", typeof(string));
            dtInvoiceDetail.Columns.Add("Blank4", typeof(string));
            dtInvoiceDetail.Columns.Add("CIN", typeof(string));
            dtInvoiceDetail.Columns.Add("Due_Date", typeof(string));
            dtInvoiceDetail.Columns.Add("DEA_Class", typeof(string));
            dtInvoiceDetail.Columns.Add("Unit_Price", typeof(string));
            dtInvoiceDetail.Columns.Add("Extension", typeof(string));
            try
            {

                using (StreamReader sr = new StreamReader(strFileName))
                {

                    string line;
                    line = sr.ReadLine();
                 

                    while ((line = sr.ReadLine()) != "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension")
                    {
                    }

                    while (!sr.EndOfStream)
                    {
                        // Split on these
                        line = sr.ReadLine() + 1;

                        char[] commadelimiter = new char[] { ',' };

                        string[] strArray = line.Split(commadelimiter, StringSplitOptions.None);
                        if (strArray[0].TrimEnd() == "Report Total:")
                            break;

                        for (int i = 0; i < (strArray.Length + 1) - strArray.Length; i++)
                        {
                            DataRow dr = dtInvoiceDetail.NewRow();

                            dr["Ship_Qty"] = strArray[0];
                            dr["Order_Qty"] = strArray[1];
                            dr["Code"] = strArray[2];
                            dr["UM"] = strArray[3];
                            dr["Blank1"] = strArray[4];
                            dr["Description"] = strArray[5];
                            dr["Blank2"] = strArray[6];
                            dr["Blank3"] = strArray[7];
                            dr["Blank4"] = strArray[8];
                            dr["CIN"] = strArray[9];
                            dr["Due_Date"] = strArray[10];
                            dr["DEA_Class"] = strArray[11];
                            dr["Unit_Price"] = strArray[12];
                            dr["Extension"] = strArray[13];
                            dtInvoiceDetail.Rows.Add(dr);
                        }
                        
                    }
                    

                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dtInvoiceDetail;
        }

    }

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question