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());

Answer 2

For fixed length formatted text files, you should be specifying that in your extended properties:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=No;FMT=Fixed";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
To specify each columns length use your Schema.ini file.

Hence your code becomes:

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