Converting String Data containing only Numerics when Opening comma Seperated Csv file

[david_foley_azyra_co]

Hi

I am opening a Csv file using Aspose Cells version 16.11.0.0 for .Net 4.0 and Converting and saving it as a .Xls file.

I have a number of columns containing numeric data and I want this data to convert to numeric in Excel. This is all working fine using TxtLoadOptions.ConvertNumericData = True;

I have another text column, which is a serial number, and contains only numerics with a few leading zeros. I want to retain this column as a string. In Excel I can prefix the data with a Single quote to achieve this. I have tried prefixing the data with a sinle quote in the Csv file and whn I view the converted Xls file the data is showing in the Cell as '000123456, i.e. I can see the single quote. It is not automatically detecting the single quote and not displaying it, as Excel would normally do. I have also tried another suggestion to encompass the data as follows in the Csv file, =""000123456"". This works when using Microsoft Excel Automation, but with Aspose cells the data appears as =""000123456"" after conversion.

Anyone any ideas how to get around this issue without setting TxtLoadOptions.ConvertNumericData to false, as I need this for all other numeric columns in the Csv file.

Reagrds,

David Foley

[Amjad_Sahi]

Hi,

Well, I think you may try to use custom parser feature which we added earlier. See the details below:

1) ICustomParser.

This interface allows users to add their custom value parser for parsing string values to other proper cell value object. Its methods are as follows:

i) object ParseObject(string value)

Parses given string to proper value object.

ii) string GetFormat()

Gets the formatting pattern for last invocation of ParseObject(string).

2) TxtLoadOptions class.

e.g. use/set attribute PreferredParsers = …(ICustomParser[] parsers) . For the parameter “parsers”, the parsers[0] is the parser that will be used for the first column in text template file, parsers[1] is the parser will be used for the second column, and so on, …etc. The last one(parsers[parsers.length-1]) will be used for all other columns start from parsers.length-1.

So, I think you may try to use the APIs to implement your own value parser. See the sample in the thread:

https://www.aspose.com/community/forums/permalink/578636/578839/showthread.aspx#578839

Hope, this helps a bit.

Thank you.

[david_foley_azyra_co]

Hi Amjad

Thanks for your prompt reply.

Seems like a long winded way to get something to work in the same way that Excel does. I would of thought Aspose Cells would handle prefixing numeric data with a Single Quote in the same way that Microsoft excel does when opening the csv file.

If this is the only way to achieve this, I will have to give it a go.

Regards,

David

[Amjad_Sahi]

Hi,

Well, I am afraid there is no other way to cope with your custom needs and you got to use the solution we provided.

Let us know if you find any issue.

Thank you.

[david_foley_azyra_co]

Hi Amjad

Thanks Again. I will go down the route you recommend. I wouldn’t have thought though, trying to get Aspose Cells to convert a Csv file in the same way Microsoft Excel does as ‘custom needs’.

Regards,

David