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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

IWorkbook - Openning A CSV File created in EXcel

Thread ID:

Created:

Updated:

Platform:

Replies:

122105 Feb 17,2016 01:37 PM UTC Feb 19,2016 05:31 AM UTC ASP.NET Web Forms 4
loading
Tags: XlsIO
Peter New
Asked On February 17, 2016 01:37 PM UTC

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 [Syncfusion]
Replied On 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:

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
Replied On 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....
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
Replied On February 18, 2016 01:26 PM UTC

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 [Syncfusion]
Replied On 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.


Regards,

Dilli babu.


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;