How would I export a DataTable to a text file and define the string length and the start position of each column ?

GD_GRAY
GD_GRAY used Ask the Experts™
on
My issue is I need to build a text file with a specific format. Each row (line) in the text file can only be 111 characters long and each row (line) in the text file will represent a single row from the DataTable.

DataTable:
Column1 | Column2 | Column3 | Column4
My            |Phone #   |is                |8000000000
My            |Phone #   |is                |9990000000


TextFile:

Column1 starts at position 0, Column2 starts at position 2, Column3 starts at position 40, Column4 starts at position 90,

MyPhone#                                is                                                8000000000          
MyPhone#                                is                                                9990000000          


I am able to use
public void ExportDataTabletoFile(DataTable datatable, string delimited, bool exportcolumnsheader, string file)

{
    StreamWriter str = new StreamWriter(file, false, System.Text.Encoding.Default);
    if (exportcolumnsheader)
    {
        string Columns = string.Empty;
        foreach (DataColumn column in datatable.Columns)
        {
            Columns += column.ColumnName + delimited;
        }
        str.WriteLine(Columns.Remove(Columns.Length - 1, 1));
    }
    foreach (DataRow datarow in datatable.Rows)
    {
        string row = string.Empty;
 
        foreach (object items in datarow.ItemArray)
        {
 
            row += items.ToString() + delimited;
        }
        str.WriteLine(row.Remove(row.Length - 1, 1));
 
    }
    str.Flush();
    str.Close();
 
}

Open in new window


And call it like
ExportDataTabletoFile(GetTable(), " ,", false, file);

Open in new window


but that is all I can come up with.

result:

My,Phone#,is,8000000000
My,Phone#,is,9990000000

Can any one expand on this for me.

Thanks.....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Retired
Distinguished Expert 2017
Commented:
Hi GD_GRAY;

The below code snippet is part of your code modify to write a formatted line. The format specifier such as {1,-37}, will format the second item in the array with index 1 to always have 37 character position and left justified, - means left justified. Now your sample output did not have a delimiter and seeming each column is a fixed width you do not need a delimiter. You can also apply this same idea with the header names.

// Get each row of the DataTable
foreach (DataRow datarow in datatable.Rows)
{
    // Holds the formatted string of the row
    string row = string.Empty;
    // Holds the column values of the row     
    object[] col = datarow.ItemArray;
    // Format the line of the file with the correct field width
    row = String.Format("{0,2}{1,-37}{2,-50}{3,-10}", col );

    // Write the formatted line to the file
    str.WriteLine(row.Remove(row.Length - 1, 1));

}
str.Flush();
str.Close();

Open in new window

Top Expert 2016

Commented:
you will have to build your strings by yourself
Do you want
my,phone,                    is,           ######## or
column1start = 0
column2start=4
column3start=40
column4start-90
output = column1.text + ","
spaces = column2start - strlen(output)
spacer = ""
for (I=0;i<spaces;i++){spacer += " ";}
output= output + spacer + column2.txt + ",";
spaces = column3start - strlen(output);
spacer = ""
for (I=0;i<spaces;i++){spacer += " ";}
output= output + spacer + column3.txt + ",";
spaces = column4start - strlen(output);
spacer = ""
for (I=0;i<spaces;i++){spacer += " ";}
output= output + spacer + column4.txt;
write(output);

Author

Commented:
Wow......

Thank you !

Author

Commented:
Soto Sir,

Any way to get the string format values from a list, or a datatable column ?
Fernando SotoRetired
Distinguished Expert 2017

Commented:
To your followup question, "Any way to get the string format values from a list, or a datatable column ?", the format specifier is just a string and can be stored in a list or any object that can store a string in. When you are ready to use it retrieve it from where you are storing it and place it in a string variable and use that string variable where the specifier is now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial