how to merge multiple text file and convert in to csv excel format

Sujeet Raman

Hi, I am implimenting some functionality but I am stuck on some logic..All documentation related to multiple text file merge is related to save that to folder.but i dont want to save that after merge.i need to convert to csv and merge.like below code merge and save into disk.

using (var output = new StreamWriter("D:\\TMP\\output"))
{
foreach (var file in Directory.GetFiles("D:\\TMP", "*.*")){
using (var input = new StreamReader(file))
{
output.WriteLine(input.ReadToEnd());}}
}

here are my requirements..

1.want to search some directory and get specific named .txt files(getting files by partial names)

2.I want to merge these multiple text files in to one text file

(remove repeating heading from each txt file ie: only one heading is needed.In all txt files headings are same but i need to avoid duplicate heading)

3.this merged txt file i want to convert in to .csv file and save in to my destination path.This csv file would be the end result

What I have tried:

string folderPath = @"C:/Temp/";
string destfile=@"C:/CSVFOLDER/sample.csv";
StreamWriter csvfile;
string[] lines, cells;
DirectoryInfo dir= new DirectoryInfo(folderPath);
FileInfo[] files = dir.GetFiles("unit*", SearchOption.TopDirectoryOnly); //txt file starting with unit i have to take from folder

foreach (var item in files)
{
    lines = File.ReadAllLines(file); //how to fix string issue here?-i cant do this with file type
    csvfile = new StreamWriter(destfile);
    for (int x = 0; x < lines.Length; x++)
    {
        cells = lines[x].Split(new Char[] { '\t', ';' });
        for (int j = 0; j < cells.Length; j++)
            csvfile.Write(cells[j] + "\t");
        csvfile.WriteLine();
    }
    csvfile.Close();
}

Cannot happening merge & convert also.Please help

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
Then you can check the different options of OrderBy because you are getting files on same time, there is no naming convension.
var sortedFiles = files.OrderBy(f => f.CreationTime).ToList();
Another alternate is, write a small utility, as you receive file move into other location with customize name and afterthat you can play with files as you can.
Sujeet Raman4y ago
i cannot add serial numbers for the files which is created some outside process.any alternate for GetFiles() function?
Muhammad Imran Ansari4y ago
That's default behaviour of GetFiles() function. If you can add seriel number before file name while creating in folder then that is possible.
1.unitxys97u
2.unitxys3445
3.unitxys9f55gd
4.unitxys97sdfr43u

You need to change wild card to get files like
DirectoryInfo dir = new DirectoryInfo(folderPath);
FileInfo[] files = dir.GetFiles("*.unit*", SearchOption.TopDirectoryOnly);
Then you will get files in correct order. like:
Sujeet Raman4y ago
that also i have tried but files are generated almost same time.even its not adding the folder placed order.Any other solution or its the behaviour?
Muhammad Imran Ansari4y ago
File are placing in the folder dynamically, you can sort the file with CreationTime as below:
var sortedFiles = files.OrderBy(f => f.CreationTime).ToList(); // Order By Ascending
Sujeet Raman4y ago
after some operation my folder having different textfile created dynamicaly.so in that i am picking some txt file name started with "unit" the file name like. all unit named file will be in last.

unitxys97u
unitxys3445
unitxys9f55gd
unitxys97sdfr43u

but the below code is not taking these files in the order which they placed in folder(like above order)
var sortedFiles = files.OrderBy(f => f.LastWriteTime).ToList(); i have alredy tried but still not keeping these order
Muhammad Imran Ansari4y ago
@Sabeesh,
That is depend on, how files are resides in your folder. Files order matter which exists in FileInfo[] files object but you can sort them with LastWriteTime. Check below modified code:
var sortedFiles = files.OrderBy(f => f.LastWriteTime).ToList();
int fileCount = 0;
foreach (var file in sortedFiles)
{
    fileCount++;
    lines = File.ReadAllLines(file.FullName); //how to fix string issue here?-i cant do this with file type
    for (int x = 0; x < lines.Length; x++)
    {
        if (fileCount > 1 && x == 0) // Add header only one time
            continue;

        cells = lines[x].Split(new Char[] { '\t', ';' });
        for (int j = 0; j < cells.Length; j++)
                csvfile.Write(cells[j] + "\t");
        csvfile.WriteLine();
    }
}
Sujeet Raman4y ago
Header is not repeting now but its changeing the order of files in csv file.like the data order is coming like 1,3,2,5,4. How i can get the same order as in the input textfile placed?

FileInfo[] files = dir.GetFiles("unit*", SearchOption.TopDirectoryOnly);

here in FileInfo[] files, its not adding in the order from folder.I dont have any serial number for those files but need to add in the same order as in the folder.How can fix that?
Nitin Sontakke4y ago
Declare a Boolean variable outside the foreach loop named headerAdded and initialize it to false.

Boolean headerAdded = false;

Also declare one integer variable outside the foreach loop named startFrom and initialize it to 0;

Inside the foreach loop initiaze it again as follows:

startForm = headerAdded ? 1 : 0;

In for loop initialize x to startForm. And inside for loop make headerAdded = true;

Partial code looks like this.
Boolean headerAdded = false, Int32 startWith = 0;

foreach (var item in files)
{
    startWith = headerAdded ? 1 : 0;
    lines = File.ReadAllLines(file); //how to fix string issue here?-i cant do this with file type
    csvfile = new StreamWriter(destfile);
    for (int x = startWith; x < lines.Length; x++)
    {
       headerAdded = true;
Muhammad Imran Ansari4y ago
@Sabeesh,
You can avoid repreating header through a extra flag. Try the below modified code:
int fileCount = 0;
foreach (var file in files)
{
    fileCount++;
    lines = File.ReadAllLines(file.FullName); //how to fix string issue here?-i cant do this with file type
    for (int x = 0; x < lines.Length; x++)
    {
        if (fileCount > 1 && x == 0)  //Add header only one time
            continue;

        cells = lines[x].Split(new Char[] { '\t', ';' });
        for (int j = 0; j < cells.Length; j++)
                csvfile.Write(cells[j] + "\t");
        csvfile.WriteLine();
    }
}
csvfile.Close();
Sujeet Raman4y ago
Hi thats working! but but the headers is repeating in my cs file.How can i avoid the repeat headings?

data is coming like this

name|age|place
sam|23|NY
name|age|place
Jon|33|XY
name|age|place
Roy|21|NY

expecting

name|age|place
sam|23|NY
Jon|33|XY
Roy|21|NY
Muhammad Imran Ansari4y ago
On first point, Use file fullname instead of file object like:
lines = File.ReadAllLines(file.FullName);
On second point, initialize csvfile outside the foreach loop because this object initialized everytime and you are not getting merged result. Full code is below:
string folderPath = @"C:/Temp/";
string destfile = @"C:/CSVFOLDER/sample.csv";
StreamWriter csvfile;
string[] lines, cells;

DirectoryInfo dir = new DirectoryInfo(folderPath);
FileInfo[] files = dir.GetFiles("unit*", SearchOption.TopDirectoryOnly); //txt file starting with unit i have to take from folder
csvfile = new StreamWriter(destfile);

foreach (var file in files)
{
    lines = File.ReadAllLines(file.FullName); //how to fix string issue here?-i cant do this with file type
    for (int x = 0; x < lines.Length; x++)
    {
        cells = lines[x].Split(new Char[] { '\t', ';' });
        for (int j = 0; j < cells.Length; j++)
            csvfile.Write(cells[j] + "\t");
        csvfile.WriteLine();
    }
}
csvfile.Close();