get datatable row data & column in comma,pipe separated format

Question

i have C# DataTable in format as below

Name    Value1  Value2  Value3
A          5       5    0
B          6       3    1
C          4       9    9

Expected format in string should be as

string sDataTableOutput = "A,B,C|Value1,5,6,4|Value2,5,3,9|Value3,0,1,9";

I want Column Header with data (except for first column)

i try hard to write below code but couldnt get it done

DataTable dtGraphOutput[0] = //Get DataTable Data ; 

foreach (DataColumn dcOutput in dtGraphOutput[0].Columns)
{
    foreach (DataRow drOutput in dtGraphOutput[0].Rows)
    {                                        
        sTicketTypeCount += ((sTicketTypeCount == "") ? "" : ",") + dcOutput.ColumnName + Convert.ToString(drOutput[dcOutput]);
    }
    sTicketTypeCount += ((sTicketTypeCount == "") ? "" : "|");
}

Its giving me output as

NameA,NameB,NameC|,Value15,Value16,Value14|,Value25,Value23,Value29|,Value30,Value31,Value39|

Tim Schmelter · Accepted Answer · 2013-08-19 11:28:51Z

1

This is LINQ and gives you the desired result:

var rows = dt.AsEnumerable();
var firstCol = string.Join(",", rows.Select(r => r.Field<string>(0)));
var otherColumns = dt.Columns.Cast<DataColumn>().Skip(1)
    .Select(dc => string.Format("{0},{1}", 
        dc.ColumnName, 
        string.Join(",", rows.Select(r => r.Field<int>(dc)))));
string sDataTableOutput = string.Format("{0}|{1}", 
    firstCol, 
    string.Join("|", otherColumns));

Result: A,B,C|Value1,5,6,4|Value2,5,3,9|Value3,0,1,9

edited Aug 19, 2013 at 11:28

answered Aug 19, 2013 at 10:51

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Shaggy Over a year ago

error The best overloaded method match for 'string.Join(string, string[])' has some invalid arguments and cannot convert from 'System.Data.EnumerableRowCollection<string>' to 'string[]'

Tim Schmelter Over a year ago

@SagarDumbre: Are you're using .NET 3.5? Then you cannot use String.Join with an IEnumerable<T> and you have to use the one that takes an array. For example: string.Join("|", otherColumns.ToArray()));. The same on the other string.Joins.

Shaggy Over a year ago

Which to use then i have option 3.5 or 4 or 4.5

Tim Schmelter Over a year ago

@SagarDumbre: You need at least .NET 4 if you want to use the overload with an IEnumerable<T> which is more efficient. Then the above code compiles and works directly without any modification.

Shaggy Over a year ago

I have changed it to 4 Now it gives me error in runtime Unable to cast object of type 'System.Int32' to type 'System.String'.

|

King King · Accepted Answer · 2013-08-19 11:27:34Z

1

string sDataTableOutput = string.Join("|", 
                           dtGraphOutput[0].Columns
                                           .OfType<DataColumn>()
                                           .Select((c,i) =>
                                              string.Join(",",
                                              dtGraphOutput[0].Rows
                                                              .OfType<DataRow>()
                                                              .Select((r,j)=>
                                                          (j == 0 && i != 0 ? c.ColumnName + "," : "") + r[c.ColumnName])
                                                              .ToArray())).ToArray());
                                                                             .

edited Aug 19, 2013 at 11:27

answered Aug 19, 2013 at 10:57

King King

63.5k16 gold badges118 silver badges140 bronze badges

4 Comments

Shaggy Over a year ago

why does it give me multiple error some of it are

'System.Data.DataColumn' does not contain a definition for 'Field' and the best extension method overload 'System.Data.DataRowExtensions.Field<T>(System.Data.DataRow, string)' has some invalid arguments

King King Over a year ago

@SagarDumbre sorry, I typed the c instead of r, just updated.

Shaggy Over a year ago

error Unable to cast object of type 'System.Int32' to type 'System.String'

King King Over a year ago

@SagarDumbre that's because your column values are Int32, just edited.

Feri · Accepted Answer · 2013-08-19 10:50:51Z

0

Try this:

DataTable dtGraphOutput[0] = //Get DataTable Data ; 

foreach (DataColumn dcOutput in dtGraphOutput[0].Columns)
{
    sTicketTypeCount +=dcOutput.ColumnName
    foreach (DataRow drOutput in dtGraphOutput[0].Rows)
    {                                        
        sTicketTypeCount += ","+ dcOutput.ColumnName + Convert.ToString(drOutput[dcOutput]);
    }
    sTicketTypeCount += ((sTicketTypeCount == "") ? "" : "|");
}

if you have problem with the last "|" you can remove it with a simple if

answered Aug 19, 2013 at 10:50

Feri

1,1217 silver badges23 bronze badges

3 Comments

Shaggy Over a year ago

it doesnt give desired output i want final string as A,B,C|Value1,5,6,4|Value2,5,3,9|Value3,0,1,9

Shaggy Over a year ago

slight change of what i wrote in question i.e. NameA,NameB,NameC|,Value15,Value16,Value14|,Value25,Value23,Value29|,Value30,Value31,Value39|

Feri Over a year ago

if you mean that it gives you "NameA" instead of "A". you can change the name of column to "A". if not give the result of my codes.

Collectives™ on Stack Overflow

get datatable row data & column in comma,pipe separated format

3 Answers 3

7 Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related