Slow loading of .CSV files using EPPLUS

Question

I have loads of .csv files I need to convert to .xslx after applying some formatting.

A file containing approx 20 000 rows and 7 columns takes 12 minutes to convert. If the file contains more than 100 000 it runs for > 1 hour.

This is unfortunately not acceptable for me.

Code snippet:

        var format = new ExcelTextFormat();
        format.Delimiter = ';';
        format.Encoding = new UTF7Encoding();
        format.Culture = new CultureInfo(System.Threading.Thread.CurrentThread.CurrentCulture.ToString());
        format.Culture.DateTimeFormat.ShortDatePattern = "dd.mm.yyyy";

        using (ExcelPackage package = new ExcelPackage(new FileInfo(file.Name))){
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(Path.GetFileNameWithoutExtension(file.Name));
            worksheet.Cells["A1"].LoadFromText(new FileInfo(file.FullName), format);
        }

I have verified that it is the LoadFromText command that spends the time used.

Is there a way to speed things up? I have tried without the "format" parameter, but the loadtime was the same.

What loadtimes are you experiencing?

since it's a simple CSV, have you tried to read it with a StreamReader? — Gianni B., Jun 08 '15 at 13:18
No, I'm quite new to C# and doesn't know my way around it, but I will look into your suggestion. Thanks. — gurbao, Jun 08 '15 at 13:28

Gianni B. · Accepted Answer · 2015-06-08T13:44:03.030

8

My suggestion here is to read the file by yourself and then use the library to create the file.

The code to read the CSV could be as simple as:

List<String> lines = new List<String>();
using (StreamReader reader = new StreamReader("file.csv"))
{
    String line; 
    while((line = reader.ReadLine()) != null)
    {
        lines.add(line);
    }
}

//Now you got all lines of your CSV

//Create your file with EPPLUS

foreach(String line in lines)
{
    var values = line.Split(';');
    foreach(String value in values)
    {
        //use EPPLUS library to fill your file
    }
}

edited Jun 08 '15 at 13:44

answered Jun 08 '15 at 13:38

Gianni B.

2,691
18
31

Thanks, this got me started! And what a difference in processing time! A file that previously took 18 minutes to process now runs in 18 seconds!! – gurbao Jun 10 '15 at 07:31

score 2 · Answer 2 · edited May 23 '17 at 12:32

2

I ran into a very similar problem with LoadFromCollection. EPPlus has to account for all situations in their methods to load data generically like that so there is a good deal of overhead. I ended up narrowing done the bottleneck to that method and ended up just manually coverting the data from the collection to Excel Cell object in EPPlus. Probably saved several minutes in my exports.

Plenty of examples on how to read csv data:

C# Read a particular value from CSV file

edited May 23 '17 at 12:32

Community

1
1

answered Jun 08 '15 at 13:40

Ernie S

13,902
4
52
79

@Gianni B Sorry, didnt mean to step on your toes - you beat me by a minute :) – Ernie S Jun 08 '15 at 13:41

Slow loading of .CSV files using EPPLUS

2 Answers2

Linked