Slow loading of .CSV files using EPPLUS
I have .csv files. I need to convert to .xslx after applying some formatting.
A file with approximately 20,000 rows and 7 columns takes 12 minutes to convert. If the file contains more than 100,000, it runs for> 1 hour.
Unfortunately, this is not acceptable to me.
Snippet of code:
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 is wasting time using.
Is there a way to speed up the process? I tried without the "format" parameter, but the load times were the same.
What kind of stress are you experiencing?
source to share
My suggestion here is to read the file yourself and then use a library to create the file.
The code to read the CSV can 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
}
}
source to share
I had a very similar problem with LoadFromCollection
. EPPlus must take all situations into account in its methods to load data in general so that a lot of overhead is imposed. In the end, I narrowed down the bottleneck on this method and ended up simply binding the data from the collection to the Excel Cell item in EPPlus. I probably saved a few minutes in my export.
Lots of examples on how to read csv data:
source to share