chokka
asked on
Using StreamReader to fetch the value
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
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
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;
}
}
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I fixed that issue by this syntax
line = sr.ReadLine() + 1;
But still i am facing TypeCast Error for DateTime
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;
}
What does a line of text from the file look like?
ASKER
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.
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;
}
}
What's happening is that, Every time foreach loop is sorting onlyWell yeah...that's how you wrote it = )
strValue is picking strArray[0] for first round of iteration
strValue is picking strArray[1] for second round of iteration.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you CodeCruiser.
I fixed the syntax by
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-50 MCG,FM:ARI N,SZ:60 EA,0,,6/30/2013,0,$82.58 ,$82.58
1,1,,EACH,ADVAIR DISKUS,NDC#:00173-0695-00, STR:100-50 MCG,FM:ARI N,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.
I fixed the syntax by
for (int i = 0; i < (strArray.Length + 1) - strArray.Length; i++)
{ }
From the .csv file, i am collecting two rows of record as follows
Invoice Detail Report,,,,,,,,,,,,,,
Ship Qty,Order Qty,Code,UM,,Description,,
1,1,,EACH,ADVAIR DISKUS,NDC#:00173-0696-00,
1,1,,EACH,ADVAIR DISKUS,NDC#:00173-0695-00,
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;
}
}
Test-6619289--5-30-2013.csv
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);
}
}
}
ASKER
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 (!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,,
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.
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
}
}
ASKER
I am expecting something like - StreamReader.ReadNextLine
Because only in the next line we have the actual field values for those Headers