export data from CSV to datatable in c#

Question

I am using below code to export data from a csv file to datatable. As the values are of mixed text i.e. both numbers and Alphabets, some of the columns are not getting exported to Datatable.

I have done some research here and found that we need to set ImportMixedType = Text and TypeGuessRows = 0 in registry which even did not solve the problem. Below code is working for some files even with mixed text.

Could someone tell me what is wrong with below code. Do I miss some thing here.

if (isFirstRowHeader)
{
    header = "Yes";
}

using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                    ";Extended Properties=\"text;HDR=" + header + ";FMT=Delimited\";"))
{
    using (OleDbCommand command = new OleDbCommand(sql, connection))
    {                       
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {                           
            adapter.Fill(table);
        }
        connection.Close();
     }
 }

I think above all else, we would need to see your exception. — Justin Russo
– Justin Russo, Commented Jul 28, 2015 at 6:17
There is no exception. Datatable gets generated with no data in some columns — Sandy
– Sandy, Commented Jul 28, 2015 at 6:35

nidheesh · Accepted Answer · 2019-01-09 05:48:07Z

for comma delimited file this worked for me

public DataTable CSVtoDataTable(string inputpath)
   {

    DataTable csvdt = new DataTable();
    string Fulltext;
    if (File.Exists(inputpath))
    {
       using (StreamReader sr = new StreamReader(inputpath))
        {
            while (!sr.EndOfStream)
            {
                Fulltext = sr.ReadToEnd().ToString();//read full content
                string[] rows = Fulltext.Split('\n');//split file content to get the rows
                for (int i = 0; i < rows.Count() - 1; i++)
                {
                    var regex = new Regex("\\\"(.*?)\\\"");
                    var output = regex.Replace(rows[i], m => m.Value.Replace(",", "\\c"));//replace commas inside quotes
                    string[] rowValues = output.Split(',');//split rows with comma',' to get the column values
                    {
                        if (i == 0)
                        {
                            for (int j = 0; j < rowValues.Count(); j++)
                            {
                                csvdt.Columns.Add(rowValues[j].Replace("\\c",","));//headers
                            }

                        }
                        else
                        {
                            try
                            {
                                DataRow dr = csvdt.NewRow();
                                for (int k = 0; k < rowValues.Count(); k++)
                                {
                                    if (k >= dr.Table.Columns.Count)// more columns may exist
                                    { csvdt .Columns.Add("clmn" + k);
                                        dr = csvdt .NewRow();
                                    }
                                    dr[k] = rowValues[k].Replace("\\c", ",");

                                }
                                csvdt.Rows.Add(dr);//add other rows
                            }
                            catch
                            {
                                Console.WriteLine("error");
                            }
                        }
                    }
                }
            }
        }
    }
    return csvdt;
}

Justin Russo · Accepted Answer · 2015-07-28 15:36:43Z

0

The main thing that would probably help is to first stop using OleDB objects for reading a delimited file. I suggest using the 'TextFieldParser' which is what I have successfully used for over 2 years now for a client.

http://www.dotnetperls.com/textfieldparser

There may be other issues, but without seeing your .CSV file, I can't tell you where your problem may lie.

The TextFieldParser is specifically designed to parse comma delimited files. The OleDb objects are not. So, start there and then we can determine what the problem may be, if it persists.

If you look at an example on the link I provided, they are merely writing lines to the console. You can alter this code portion to add rows to a DataTable object, as I do, for sorting purposes.

edited Jul 28, 2015 at 15:36

answered Jul 28, 2015 at 13:08

Justin Russo

2,2141 gold badge22 silver badges27 bronze badges

4 Comments

Sandy Over a year ago

my CSV file contains 1600000 lines which is huge. Will this work for such huge file.

Sandy Over a year ago

I may have many such files in one run. I loop through each and every file and fetch only the required records based on the filter columns provided

Justin Russo Over a year ago

Sandy, to answer your first question, in a situation like that, the only way to tell if it works or not, is to try. I see no reason why the number of rows would be a factor.

Justin Russo Over a year ago

Sandy, to answer your second statement, you only filter after you create the "datatable" correct? Which I'm assuming means you populate the datatable from the records and then you select from the datatable based on your filter criteria using a Linq statement. You can do the same thing with the "TextFieldParser," because the end result is to put the rows into a datatable the same way.

Collectives™ on Stack Overflow

export data from CSV to datatable in c#

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related