We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

IWorkbook - Openning A CSV File created in EXcel

Hi all,
I have a system where by we import files supplied to us from a 3rd party.

I want to imoport these files as they are without any manual intervention.

The file in question is a CSV file, but it is apparent that it must have been created in Excel.

Here is an excerp from one of the files......

You can see my isssue.  I want the data, not the EXcel = and Hyperlink function text.

Is there a way that I can open a CSV file as a workbook / sheet and convert the VALUES to a data table without the function text?
I am looking for something generic, so don't really want crude string manipulation.

Regards all,


4 Replies

DB Dilli Babu Nandha Gopal Syncfusion Team February 18, 2016 10:30 AM UTC

Hi Peter

Yes, your requirement can be achieved through XlsIO. If a workbook of CSV type contains a formula, then calculated value of the cell can be exported to Datatable by assigning the CalculatedValue property of the cell to Value property of the cell which is illustrated in the following code example.

Code example:


foreach (IRange range in sheet.UsedRange)


    // Assign the calculated value to value of the cell

    range.Value = range.CalculatedValue;



// Export to Datatable
DataTable dt = sheet.ExportDataTable(sheet.UsedRange,ExcelExportDataTableOptions.None);

We have also shared the sample for your reference which can downloaded from following link.

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Open_CSV1602557067.zip


Dilli babu.

PN Peter New February 18, 2016 01:07 PM UTC

That works great for the URL.  Thank you......
Howveer, i have now have issues with long numbers which are strings....

In this instance, I want the value 111111213065023158.  I populate a nvrachar field with this value.

For clarity - I have attached the file in question.  I have had to adjust some data for security reasons.

It's ironic really as providers of the file have put in the formulas to avoid the problem.

I do hope you can help.  In the meantime I am experiment from my end.

Thanks for you efforts.

Attachment: FileForSyncFusion_4c62ca76.zip

PN Peter New February 18, 2016 01:26 PM UTC


It would seem I need to apply this..............

range.IsStringsPreserved = true;

It seems to do the trick........If I am incorrect, please let me know.

Thank you very much for your assistance, your response and the time it took has been very impressive.

DB Dilli Babu Nandha Gopal Syncfusion Team February 19, 2016 05:31 AM UTC

Hi Peter,

Thank you for updating us.

Yes, long number will be preserved as a string by enabling range.IsStringsPreserved property. Instead of enabling this property for every range, you can enable IWorksheet.IsStringsPreserved property which preserves the value as string through the worksheet.


Dilli babu.

Live Chat Icon For mobile
Up arrow icon