4
\$\begingroup\$

I've got ~20GB of data about the same subject, concatenated from small files of 16kB, each produced by different measurement equipment. They share some headers (on the example: B, D & E) and they may have different headers (A - C - G - G), like so:

File1:

HeaderA|HeaderB|HeaderC|HeaderD|HeaderE|
value A|value B|value C|value D|value E|

File2:

HeaderZ|HeaderB|HeaderG|HeaderD|HeaderE|
value Z|value B|value G|value D|value E|

And I want it to be:

HeaderA|HeaderB|HeaderC|HeaderD|HeaderE|HeaderG|HeaderZ  <= Headers
value A|value B|value C|value D|value E|       |         <= File1
       |value B|       |value D|value E|value G|value Z  <= File2

More specifically, headings and data look like the following.

File 1:

FileName|SubTestType|CH4.Min|CH4.Max|CH4.Avg|CO2.Min|CO2.Max|CO2.Avg|CO.Min|CO.Max|CO.Avg|HC.Min|HC.Max|HC.Avg|NO.Min|NO.Max|NO.Avg|NOx.Min|NOx.Max|NOx.Avg
P:\001.xml|Background|1,95|1,95|1,95|0,04|0,04|0,04|-0,20|-0,20|-0,20|4,19|4,19|4,19|-0,01|-0,01|-0,01|0,02|0,02|0,02

File 2:

FileName|SubTestType|CO2.Min|CO2.Max|CO2.Avg|CO.Min|CO.Max|CO.Avg|HC.Min|HC.Max|HC.Avg|NOx.Min|NOx.Max|NOx.Avg
P:\010.xml|Background|0,04|0,04|0,04|0,42|0,42|0,42|2,80|2,80|2,80|0,44|0,44|0,44

Output:

FileName|SubTestType|CH4.Min|CH4.Max|CH4.Avg|CO2.Min|CO2.Max|CO2.Avg|CO.Min|CO.Max|CO.Avg|HC.Min|HC.Max|HC.Avg|NO.Min|NO.Max|NO.Avg|NOx.Min|NOx.Max|NOx.Avg
P:\001.xml|Background|1,95|1,95|1,95|0,04|0,04|0,04|-0,20|-0,20|-0,20|4,19|4,19|4,19|-0,01|-0,01|-0,01|0,02|0,02|0,02
P:\010.xml|Background||||0,04|0,04|0,04|0,42|0,42|0,42|2,80|2,80|2,80||||0,44|0,44|0,44

I need them to be gathered on files by bunch of 5 000. I choose 5 000 because it's more human sized files and avoid to get too much empty data in it.

Then I'll use QLikView to read theses grouped files and view my data. I can't give directly the big CSV to QLikView because it can't handle it.

I've written this code, which works but will take between 3 & 5 minutes by group and there is 230 groups (~16h of treatment).

private const int Limite = 5000;
private static readonly Stopwatch Stopwatch = new Stopwatch();

private static void Main(string[] args)
{
    Stopwatch.Start();
    FromHugeFile();
    Console.Out.WriteLine("Done.");
}

private static void FromHugeFile()
{
    var lines = File.ReadLines(@"C:\Users\me\Desktop\concatenated.csv");
    List<List<string>> couples = new List<List<string>>();

    int i = 0;
    string tempLine = "";
    foreach (var line in lines)
    {
        if (++i%2 == 0)
        {
            couples.Add(new List<string>
            {
                tempLine, line
            });
        }
        else
        {
            tempLine = line;
        }
        if (i%(Limite*2) == 0)
        {
            doMyLines(couples, i/(2*Limite));
            couples.Clear();
        }
    }
}

public static void doMyLines(List<List<string>> couplesOfLines, int chunckNumber)
{
    Console.Out.WriteLine("Starting chunck:" + chunckNumber);
    ConcurrentBag<string> myHeaders = new ConcurrentBag<string>();
    ConcurrentQueue<Dictionary<string, string>> fileValueList = new ConcurrentQueue<Dictionary<string, string>>();
    int fileDone = 0;
    Parallel.ForEach(couplesOfLines, couple =>
    {
        List<string> headerList = couple[0].Split('|').ToList();
        List<string> valueList = couple[1].Split('|').ToList();
        Dictionary<string, string> tempDictionary = new Dictionary<string, string>();
        for (int i = 0; i < headerList.Count; i++)
        {
            string header = headerList[i];
            string value = valueList[i];

            if (!myHeaders.Contains(header))
            {
                myHeaders.Add(header);
            }
            tempDictionary[header] = value;
        }

        Interlocked.Increment(ref fileDone);
        if (fileDone % (Limite / 10) == 0)
        {
            Console.Out.WriteLine("Chunck:" + chunckNumber + "\telapsed:" + Stopwatch.Elapsed + "\tRead: " + fileDone.ToString("N0") + "/" + Limite.ToString("N0"));
        }

        fileValueList.Enqueue(tempDictionary);
    });

    List<string> outputList = new List<string>();

    List<string> myHeadersList = myHeaders.ToList();
    myHeadersList.Sort();

    myHeadersList = myHeadersList.Distinct().Where(x => !string.IsNullOrEmpty(x)).ToList();

    for (int i = myHeadersList.Count - 1; i >= 0; i--)
    {
        if (!NamesList.Any(name => myHeadersList[i].StartsWith(name)))
        {
            myHeadersList.RemoveAt(i);
        }
    }


    outputList.Add(string.Join("|", myHeadersList));

    fileDone = 0;
    foreach (Dictionary<string, string> dictionary in fileValueList)
    {
        string line = "";
        foreach (string key in myHeadersList)
        {
            if (dictionary.ContainsKey(key))
            {
                line += dictionary[key] + "|";
            }
            else
            {
                line += "|";
            }
        }
        fileDone++;
        if (fileDone % (Limite / 10) == 0)
        {
            Console.Out.WriteLine("\tChunck :" + chunckNumber + "\tConcat: " + fileDone.ToString("N0") + "/" + Limite.ToString("N0"));
        }
        outputList.Add(line);
    }

    File.WriteAllLines(@"C:\Users\me\Desktop\meta3\metas" + chunckNumber + ".csv", outputList);

    Reset();
}

Of course I'll be glad to receive comments about good practice, naming and code readability, but I'm mainly looking for speed related advice this time.

\$\endgroup\$
  • \$\begingroup\$ I'm having troubles visualising what your code does. Could you please procured some example input and output? Based on your initial description this seems overly complicated, but I might be missing some information \$\endgroup\$ – holroy Mar 1 '16 at 11:57
  • \$\begingroup\$ It helps but I would still like to see some lines from input and expected output. \$\endgroup\$ – holroy Mar 1 '16 at 13:27
  • \$\begingroup\$ @holroy I've added the files \$\endgroup\$ – Thomas Ayoub Mar 1 '16 at 13:45
1
\$\begingroup\$

If I am reading your code right you want to read the header from two files and "merge" them together and output it. Then you want to take the first record from file one and "merge" it into the output file, then take the first record from file two and "merge" it into the output file.

You might look at this CSV Reader...

https://www.nuget.org/packages/LumenWorksCsvReader/3.9.0

or from GitHub

https://github.com/phatcher/CsvReader

I have used it in several projects and it works really well. The nice thing about it is it reads your file in as a stream so you can read one row at a time.

You can open two streams (one for each file) and read records one at a time from each stream and do your "merge" and output it one record at a time. First, this will save memory since you won't have to have everything in memory at one time and second, you won't have to store things into lists and dictionaries and queues because you will only process one record at a time.

\$\endgroup\$
  • \$\begingroup\$ Well the problem is not that I need to merge two files together but more like 5k files together. Will this library able to do that? \$\endgroup\$ – Thomas Ayoub Mar 6 '16 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.