Importing large CSV file

I’m trying to convert a large CSV file (up to 65K records with about 50 columns) to Excel format, primarily for the purpose of creating hyperlinks in one of the columns. It’s working, but the process takes a long time, consumes a large amount of memory and the CPU utilization is very high the entire time. Stats for a 25,000 record file on a P4 2.4 GHz with 1GB RAM are:

Load duration: 25 sec
Hyperlink creation duration: 185 sec
Save duration: 159 sec
Memory consumed: 176 MB
CPU utiliziation: 95%+

if I take out the step to create the hyperlinks, it only takes about 28 seconds to save. Is there anything I can do to improve any of these metrics? My code is below:

Excel ss = new Excel();

DateTime start = DateTime.Now;

ss.Open(“itr 2005073b.vsi”, FileFormatType.CSV);

int loaded = (int)DateTime.Now.Subtract(start).TotalMilliseconds;

Worksheet ws = ss.Worksheets[0];

int hlIdx = 0;

for(int i = 4; i <= ws.Cells.MaxRow; i++)

{

string url = ws.Cells[i, 39].Value.ToString();

if (url != null)

{

ws.Cells.ClearRange(i, 39, i, 39);

ws.Hyperlinks.Add(i, 39, 1, 1, url);

ws.Hyperlinks[hlIdx++].TextToDisplay = “Detail”;

}

}

int formatted = (int)DateTime.Now.Subtract(start).TotalMilliseconds;

ss.Save(“itr 2005073b.xls”, FileFormatType.Excel2000);

int saved = (int)DateTime.Now.Subtract(start).TotalMilliseconds;

MessageBox.Show(loaded.ToString() + " " + formatted.ToString() + " " + saved.ToString());

You just need to change this line of code from:

ws.Cells.ClearRange(i, 39, i, 39);


to:
ws.Cells.ClearContents(i, 39, i, 39);

You will find the program will be much faster.

@bespey,
Aspose.Excel is disposed of now and is replaced by the latest product Aspose.Cells that supports a variety of options to import data from different sources including CSV files. You can set a number of configurations before importing data from CSV using TxtLoadOptions like you can set separator for data, , set encoding and even set custom parsers for importing data. For a detailed description of this feature refer to the following article:
Opening CSV Files