Reading numbers from CSV as strings

Question

Possible Duplicate:
How in .Net do I Import Values from a CSV in the format I want using OleDB?

I have a csv file which I read using OleDbCommand.

The CSV contains columns that are numbers that are left padded with 0. When I read the columns, I get the results without any leading 0 characters: It is now a number.

These values in the CSV:

UserNumber    LocationNumber
00001234      00023456
00891234      00000456

becomes

UserNumber    LocationNumber
1234      23456
891234      456

In the resulting datatable filled by my OleDbDataAdapter.

Is there any way to prevent this?

PS, Padding is not an issue, but due to the nature of the work being done, not really a good solution as the CSV files have no set format.

@Chandu - No, I don't. This is used by our clients to import data at their sites. Our clients get these Csv files from SAP exports and other sources beyond our and their control. — callisto, Jul 17 '12 at 11:27
I think the other post referred by @Stuart addresses your problem. Check that — Chandu, Jul 17 '12 at 11:29
Why don't you parse CSV files yourself without using OleDb? It is rather easy. — Dmitry Osinovskiy, Jul 17 '12 at 11:30
@Stuart Dunkeld + Chandu: I had a look at that post, and while it does contain good info, it does not provide an answer to my problem. As the CSV files are not created under my control, I have no way of building a rich Schema file that include column data. — callisto, Jul 17 '12 at 11:40

score 1 · Accepted Answer · answered Jul 17 '12 at 12:15

It seems to me like you won't mind though just reading the whole file as strings. You can just build your Schema.ini file like that pretty easily.

Using your original connection:

DataTable GetSchemaTable() 
{
    try
    {
        OleDbDataAdapter da; // init adapater with your own connection
        DataSet ds = new DataSet();
        System.Data.DataTable[] dtTables = da.FillSchema(ds, SchemaType.Source);
        return dtTables[0];
    }
    catch (System.Exception se)
    {
        throw new System.Exception("Problem retrieving Schema: " + se.Message);
    }
}

void CreateSchemaIni(string csvDirectory, string csvFileName) 
{       
    DataTable dt = GetSchemaTable();
    FileStream fsOutput = new FileStream(csvDirectory + "\\schema.ini", FileMode.Create, FileAccess.Write);
    StreamWriter srOutput = new StreamWriter(fsOutput);
    string s1, s2, s3;
    s1 = "[" + csvFileName + "]";
    s2 = "ColNameHeader=False";
    s3 = "Format=CSVDelimited";
    srOutput.WriteLine(s1 + '\n' + s2 + '\n' + s3 + '\n');
    StringBuilder strB = new StringBuilder();
    for (int i = 1; i <= dt.Columns.Count; i++)
    {
        strB.Append("Col" + i.ToString());
        strB.Append("=F" + i.ToString());
        strB.Append(" Text\n");
        srOutput.WriteLine(strB.ToString());
        strB = new StringBuilder();
    }
    srOutput.Close();
    fsOutput.Close();
}

Reading numbers from CSV as strings

1 Answers1