Import CSV into datatable

I am using the following code to import a csv into a datatable

  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.Fill(dtMain);
            int count = dtMain.Rows.Count;
           
            objConn.Close();
            return dtMain;

I have a zipcode column in the csv file. The import works fine as long as the zip code is 5 digits in length. If the zip code is something like 12345-6789 after the import is completed it shows up as a blank in the zipcode column for that particular record.

Any thoughts on how I can avoid this ?
Thanks,
Aditya
LVL 1
pmac38CDSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
Did you declare the columns of dtMain by yourself ?
0
OP_ZaharinCommented:
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
0
OP_ZaharinCommented:
- 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.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

pmac38CDSAuthor Commented:
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
0
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
0
pmac38CDSAuthor Commented:
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
0
OP_ZaharinCommented:
- 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)

0
pmac38CDSAuthor Commented:
I don't know how many columns the csv being uploaded is going to contain until it is actually uploaded.
0
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
0
pmac38CDSAuthor Commented:
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;
    }
0
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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?

0
pmac38CDSAuthor Commented:
I took the import code out of the method and it still did not work.
0
OP_ZaharinCommented:
pmac,
- can't help u  much further on c# but i'm sharing some c# example on specifying the string datatype. hope this helps:

http://www.akamarketing.com/blog/256-csv-datatable.html
http://www.daniweb.com/software-development/csharp/threads/118203 > scroll down to the third last code...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tpaynCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.