将多个CSV文件中的数据导入Excel工作表

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.

我需要将50个类似的csv文件中的数据导入到单个Excel工作表中。有没有办法只从每个文件中获取选定的列并将它们放在一个工作表中。

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.

我的csv文件的结构:所有文件中的几列完全相同。 (我希望它们在excel中)然后一列具有相同的列名但不同的数据,我想在excel表中以不同的名称彼此相邻放置。我不希望csv文件中的所有其他剩余列。

In short,

read all csv files,

阅读所有csv文件,

get all the columns which are common to all csv & put in excel sheet.

获取所有csv所共有的列并放入excel表中。

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.

现在,从每个具有相同标题但不同数据的文件中取一列,然后将一个接一个地放在excel表中,并使用csv文件名命名。

Leave remaining rest of the columns.

留下剩余的剩余列。

Write excel sheet to a excel file.

将Excel工作表写入excel文件。

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

最初我认为它可以很容易地完成,但考虑到我在学习阶段的编程技巧对我来说很难。请帮忙。

3 个解决方案

#1

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

Microsoft Text Driver允许您读取DataSet中的CSV数据,从而简化数据操作。

This Stack Overflow question is a good starting point.

Stack Overflow问题是一个很好的起点。

#2

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

最快的方法是使用FileHelpers将CSV读入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:

然后使用EPPlus导出Excel中的DataTable,使用此代码段中的方法DataTableToExcelXlsx:

https://*.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)

使用EPPlus,您不必在执行此代码的计算机上安装Excel,并且可以在服务器上使用它(ASP.NET)

#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.

通过非常简单的编码,我能够读取文件。现在,我们唯一需要做的就是让这段代码有点花哨,遍历给定文件夹中的所有CSV文件并收集数据。一旦我们读取了数据,就可以将其过滤并根据需要放到excel中。当然,excel可以自己导入CSV,但每次都这样做是不切实际的。我们可以再次将代码添加到应用程序以便灵活使用,这正是我想要做的。

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];
 }

#1