Import CSV into datatable

Did you declare the columns of dtMain by yourself ?

hi pmac,
- have u try to expand your column/field size for postcode field in the table? might be it being rejected because of the size of the column is not sufficient

- and what is the datatype for postcode? if its number then again, it might also being rejected because you have (-) character in '12345-6789'
- therefore you might want to change the column datatype for postcode OR do a cleanup on the postcode value during the import procedure itself.

Dhaest:No I have not declared the columns of the data table by myself.
OP_Zaharin:The data table I am using is of the type datatable. So I don't really have too much control over the datatype of the columns do I ?

Let me know if you need anything else from my end,
Thanks,
Aditya

p_mac,


You have full control over the DataTypes of the datatable

datatabel.Columns.Datatype

The dataTypes are defaulted when inserting the First Line of your File ==> so its defaulted to a number

Set the dataTypes yourself before starting the import ;-)

regards

poor beggar

How can I set the data types before starting the import? My data table is of the type data table. I have not defined the columns since the csv being imported could have variable columns.

Thanks,
Aditya

- do correct me if i'm wrong, the sample below illustrate the creation of a datatable in VB. therefore it is possible that u can create the same in C# whereby you create a datatable, then define the columns and the datatype for that column.
- therefore you can change the datatype for postcode to String to enable it accepting '12345-6789' as a value.

Dim Table1 As DataTable
Table1 = New DataTable("Customers")
'creating a table named Customers
Dim Row1, Row2, Row3 As DataRow
'declaring three rows for the table
Try
Dim Name As DataColumn = New DataColumn("Name")
'declaring a column named Name
Name.DataType = System.Type.GetType("System.String")
'setting the datatype for the column
Table1.Columns.Add(Name)
'adding the column to table
Dim Product As DataColumn = New DataColumn("Product")
Product.DataType = System.Type.GetType("System.String")
Table1.Columns.Add(Product)

I don't know how many columns the csv being uploaded is going to contain until it is actually uploaded.

Q1 : where do you initialise the "dtMain" Variable that you're returning ?


You must alter the dtmain using the schema of the dataAdapter used to read your csv ;-)
this because in your adapter YOU say it must all be text ==> see extended properties

use the folowing before the Fill

objAdapter1.FillSchema(dtMain, SchemaType.Source);
This will override the dtmain schema and will use the schema of the adapter



The folowing Link Might help with a Good Example
http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/dda4017a-c1a7-4ee3-90dd-5bdfd31f007a

I added the line you suggested and it did not work. Here is what I am doing

I initialize dtMain just before I called the Import method so something like this
DataTable dtMain = new DataTable();
 dtUpload = this.ImportCSVIntoDataTable(this.usr_Upload, fileName, dtMain);

   public DataTable ImportCSVIntoDataTable(string path, string fileName, DataTable dtMain)
    {
        string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Text;";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM " + fileName, objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;


        objAdapter1.FillSchema(dtMain, SchemaType.Source);
        objAdapter1.Fill(dtMain);
        int count = dtMain.Rows.Count;

        objConn.Close();
        return dtMain;
    }

Ok, But then still the Original dtMain scheme is not changed

why?

Cause you change the scheme of the local Table inside the method
This changed Table is returned into dtUpload? and not into the dtMain?

I took the import code out of the method and it still did not work.

Hi,

Specify your delimiter in your connection string.
e.g.
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +";Extended Properties=Text;FMT=Delimited(,)";

Alos see:
http://www.connectionstrings.com/textfile
http://www.codeproject.com/KB/cs/UsingJetForImport.aspx