CODEWITHSUNDEEP
c#sql-servercsvssis
TheEsnSiavashi
TheEsnSiavashi

Reputation: 1255

The best way to load several csv files as a single data set

I have a folder with many csv files with the same format (same table structure but different data). I want to have all of the data from these csv files as a single data set inside my SSIS package. My current solution is this: Create a helping table in SQL Server and use a For Each Container to load all the files into the table. Then load this table as the single data set you want in the SSIS package.

However, I would highly prefer a method that does not depend on creating such an extra table in my SQL Server. I was thinking that there might be a better way of doing this using C# and Script component. Does anybody has any suggestions?

Upvotes: 2

Views: 1301

Answers (2)

ASH
ASH

Reputation: 20342

This should do it for you.

var allCsv = Directory.EnumerateFiles("Src-Path", ".*csv", SearchOption.TopDirectoryOnly);
string[] header = { File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l)) };
var mergedData = allCsv
    .SelectMany(csv => File.ReadLines(csv)
        .SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1)); // skip header of each file
File.WriteAllLines("Dest-Path", header.Concat(mergedData));

http://www.sqldataplatform.com/Blog/Post/49/How-to-Combine-CSV-Files-Using-a-Simple-C--Script

Note, you don't even need C# for something as simple as this! You can actually use the Command Prompt for something that is completely standardized.

Open Command Window. (Press "Window Key" and "R", then type command and enter.

Type copy c:\*.csv c:\File.csv and press enter

This will combine all of the csv files that are in your root c:\ directory into one file called File.csv.

You can change the file names and paths as necessary.

Upvotes: 0

USC.Trojan
USC.Trojan

Reputation: 401

How about:

var allCsv = Directory.EnumerateFiles("Src-Path", ".*csv", SearchOption.TopDirectoryOnly);
string[] header = { File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l)) };
var mergedData = allCsv
    .SelectMany(csv => File.ReadLines(csv)
        .SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1)); // skip header of each file
File.WriteAllLines("Dest-Path", header.Concat(mergedData));

Just note that you have to add using System.Linq;

Upvotes: 2

Related Questions