IWorkbook - Openning A CSV File created in EXcel

[Peter New]

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......
"FIELD 1","FIELD 2","FIELD 3","FIELD 4"
="6338XXXX30652258",="19554675","5.0000",=hyperlink("https://aURLforARsourceWeUtilise")

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,

ADC

[Dilli Babu Nandha Gopal]

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:

sheet.EnableSheetCalculations();

foreach (IRange range in sheet.UsedRange)

{

    // Assign the calculated value to value of the cell

    range.Value = range.CalculatedValue;

}

sheet.DisableSheetCalculations();        

// 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

Regards,

Dilli babu.

[Peter New]

That works great for the URL.  Thank you......
Howveer, i have now have issues with long numbers which are strings....
eg......
="111111213065023158"
becomes
1.1111121306502403E+17

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

[Peter New]

Aaah.......

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.

[Dilli Babu Nandha Gopal]

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.

Regards,

Dilli babu.