Import data from multiple CSV files to an Excel sheet

Question

I need to import data from 50 similar csv files to a single excel sheet. Is there any way to get only selected columns from each file and put them together in one sheet.

Structure of my csv files: A few columns exactly same in all the files. (I want them to be in excel) then one column with same column name but different data which I want to place next to each other with different names in the excel sheet. I do not not want all other remaining columns from csv files.

In short,

1. read all csv files,
2. get all the columns which are common to all csv & put in excel sheet.
3. Now, take one column from each file which has the same header but different data and put one after the other in excel sheet with named from the csv file name.
4. Leave remaining rest of the columns.
5. Write excel sheet to a excel file.

Initially I thought it can be easily done, but considering my programming skill in learning stage it is way difficult for me. Please help.

Answer 1

Microsoft Text Driver allows you to read CSV data in a DataSet, making data manipulation easy.

This Stack Overflow question is a good starting point.

Answer 2

Fastest way could be using FileHelpers to read CSV into a DataTable :

http://filehelpers.sourceforge.net/FileHelpers.CommonEngine.CsvToDataTable_overload_4.html

and then with EPPlus export that DataTable in excel, use method DataTableToExcelXlsx from this snippet:

https://stackoverflow.com/a/9569827/351383

With EPPlus you don't have to have Excel installed on machine that is executing this code, and you can use it on server (ASP.NET)

Answer 3

With a very simple coding, I was able to read the files. Now, the only thing we need to do is to make this code a bit fancy to loop thorough all the CSV files in the folder given and collect data. Once we read the data, it can be filtered and put to an excel as we want. Of course, excel can import CSV itself, but it is not that practical to do this every time. And again we can add the code to application to use in flexibility, exactly what I am trying to do.

public static System.Data.DataTable GetDataTable(string strFileName)

{
        System.Data.OleDb.OleDbConnection dbConnect = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + ";Extended Properties = \"Text;HDR=YES;FMT=TabDelimited\"");
        dbConnect.Open();
        string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
        System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, dbConnect);
        System.Data.DataSet dSet = new System.Data.DataSet("CSV File");
        adapter.Fill(dSet);
        dbConnect.Close();
        return dSet.dbTables[0];
 }