Add extra column and print the file name in it using C#

[Dhruvi_Arumugam]

Hi Everyone,

Require help in a use case, below is the code of C# which merges data from multiple CSV files, in the following code I also want a condition that, a column with heading FileName should be added in the file at index 0 and the filename of which data is being merged that filename should be printed in that column. Can anybody please guide.

var allCsv = Directory.EnumerateFiles(@"C:\Users\Documents\UiPath\Files1", "*.csv", SearchOption.TopDirectoryOnly);
string[] header = 
{ 
    File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l))
};

 // Get CSV Data
 var mergedData = allCsv.SelectMany(csv => File.ReadLines(csv).SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1)); 

 // skip header of each file
 File.WriteAllLines(@"C:\Users\Documents\UiPath\test.csv", header.Concat(mergedData));

@ppr @Yoichi Can you guys also help here?

Thanks

[Yoichi]

Hi,

Can you share specific sample : input and expected output?

Regards,

[ppr (Peter Preuss)]

@Dhruvi_Arumugam

which merges data from multiple CSV files

are the datacolumn structures all the same for all CSV files?

@Dhruvi_Arumugam

a column with heading FileName should be added in the file at index 0 and the filename of which data is being merged that filename should be printed in that column

please elaborate more on this, How many columns does have the CSV files?

[Dhruvi_Arumugam]

Hi @Yoichi @ppr yes the structure is same.

I will share samples input output shortly.

Thanks!!

[ptrobot]

Test with the following code:

var delimiter = ",";
var allCsv = Directory.EnumerateFiles(@"C:\Users\Documents\UiPath\Files1", "*.csv", SearchOption.TopDirectoryOnly);
string[] header = 
{ 
    "FileName" + delimiter + File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l))
};

 // Get CSV Data
 var mergedData = allCsv.SelectMany(csv => File.ReadLines(csv).SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1).Select(ll => (csv.Contains(delimiter) ? "\""+csv+"\"" :  csv) + delimiter + ll)); 

 // skip header of each file
 File.WriteAllLines(@"C:\Users\Documents\UiPath\test.csv", header.Concat(mergedData));

[Dhruvi_Arumugam]

Hi @ppr @Yoichi @ptrobot ,

Very apologies for this much delay, my system got broke down thus i was not able to communicate. Can you please help me now with some solution.
I am providing the examples

Input - I have uploaded the files in Zip format as the platform was not allowing me to upload .csv files, please extract the zip you will get 3 .csv input files
SampleInput1.zip (637 Bytes)

Output - As described above in the output file extra column is added at index 0 with names of file from where the data is taken.
SampleOutput.xlsx (8.6 KB)

Please help !

Thanks

[Dhruvi_Arumugam]

Hi @ptrobot,

I tried you code it is working thankyou, but in the first column the whole path of the file is coming with the name can you please help as I only need the file name and not the complete path.

Thanks

[ptrobot]

You can use Path.GetFileName() to get only the filename:

// Get CSV Data
var mergedData = allCsv.SelectMany(csv => File.ReadLines(csv).SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1).Select(ll => (csv.Contains(delimiter) ? "\""+Path.GetFileName(csv)+"\"" :  Path.GetFileName(csv)) + delimiter + ll));

[Dhruvi_Arumugam]

Sure, trying this

[Dhruvi_Arumugam]

Hi @ptrobot,

Also I am trying to print the count of .csv files hence writing

Console.WriteLine(allCsv.Count);

But this is giving error, can you please help.

Thanks

[Yoichi]

Hi,

How about the following?

var allCsv = Directory.EnumerateFiles(System.IO.Path.Combine(System.Environment.CurrentDirectory,"SampleInput1"), "*.csv", SearchOption.TopDirectoryOnly);
string[] header =
{ 
    "File Name,"+File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l))
};
 // Get CSV Data
  var mergedData = allCsv.SelectMany(csv => File.ReadLines(csv).SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1).Select(l=>System.IO.Path.GetFileNameWithoutExtension(csv)+","+l)); 

 // skip header of each file
 File.WriteAllLines(System.IO.Path.Combine(System.Environment.CurrentDirectory,"test.csv"), header.Concat(mergedData));

Sample20220509-3.zip (3.2 KB)

Regards,

[ptrobot]

Count is a method so you need to add () in C#:

Console.WriteLine(allCsv.Count().ToString());

[Dhruvi_Arumugam]

Thanks a lot @Yoichi , i will try this and let you know shortly

[Dhruvi_Arumugam]

Thanks a lot @ptrobot for your solutions, I had one more question if the files are similar but in .xlsx format will this code work if not then what do i need to do to make it working.

Thanks!!

[ptrobot]

Unfortunately the code won’t work for Excel files. If you want to use the same code, you need to convert them to CSV files first.

[Dhruvi_Arumugam]

Is there any way we can do for excel files as well by code?? If required I can open a new thread for that as well

[Dhruvi_Arumugam]

Hi @Yoichi,

Thanks a lot its working!!

If one of the csv file gives any exception then the process will halt, can you please help in this code with some try catch activity as well so as it can go to the next file and print or capture the file names with exception in any collection object?

Thanks!

[Dhruvi_Arumugam]

@ptrobot @Yoichi I cannot mark 2 solutions in this forum so bad about that. Thanks a lot for sharing your knowledge.
I will open a new thread for my other questions in this code