C# Reading Fixed Length File - Trimming Space in the values

Question

When I read fixed length file, The value is always without space.

Exmple: The folder c:\temp contains 2 files

fs.txt

ITMHMC12-163 -0000153430.30
ITMHMC12-164 -0000000745.18

schema.ini

[fs.txt]
ColNameHeader=False
Format=FixedLength
DateTimeFormat=yyyymmdd
Col1=RecordTypeSCFBody Text Width 3
Col2=InvoiceNumber Text Width 10
Col3=Amount Text Width 14

C# code to read the file...

string fileName = @"C:\temp\fs.txt";
string dir = Path.GetDirectoryName(fileName);

DataTable dataTable;

using (OleDbConnection conn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
        "Data Source=" + dir + ";" +
        "Extended Properties=\"Text;\""))
{
conn.Open();

using (OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + fileName, conn))
{

    dataTable = new DataTable();
    adapter.Fill(dataTable);
}
conn.Close();
}

Console.Write(dataTable.Rows[0][1].ToString()); <-- this line **

-->this line gives me "HMC12-163", but I expect "HMC12-163 ". Note the space!

Appreciate your help.

Many thanks! -Deb

Answer 1

There don't appear to be any documented settings to control this behavior. You can explicity pad to the correct number of spaces in code or SQL if the trailing spaces are important:

const int maxInvoiceLength = 10;
string fileName = @"C:\temp\fs.txt";
string dir = Path.GetDirectoryName(fileName);

DataTable dataTable;

using (OleDbConnection conn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
        "Data Source=" + dir + ";" +
        "Extended Properties=\"Text;\""))
    {
        conn.Open();

        string query = String.Format("SELECT RecordTypeSCFBody, LEFT(InvoiceNumber + SPACE({0}), {0}), Amount FROM {1}", maxInvoiceLength, fileName);
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
        {
            dataTable = new DataTable();
            adapter.Fill(dataTable);
        }
    }

    Console.Write(dataTable.Rows[0][1].ToString());