ASPOSE.Cells : while reading csv file format- leading zeros in SSN are removed

Aspose.Cells Product Family
1

Hi,

When a csv file is read using aspose, the fields containing only numbers are treated as numeric type. So, the leading zeros are removed.

For example : 0001234567 from input file changes to 1234567

But if the field value has an apostrophe in front of it (which actually acts as a text qualifier), the field is properly loaded with leading zeros.

Without adding an apostrophe in front of a field, is there a provision to tell Aspose.cells to read a field as text even though it contains numbers.

I tried the following but it does not work for me. May be i am doing some thing wrong while using it,

1) workbook.Settings.ConvertNumericData = false

2)ws.Cells.ExportDataTableAsString

Please advice

Thanks,

Ranjith

2

Hi,

In MS Excel, when you insert “0001234567” into a cell, it comes out as “1234567” of numeric type (removing the leading zeros). For a CSV file, even if you set String/Text format for a Cell and insert the leading zeros, when you open it in MS Excel, the leading zeros would be removed. For confirmation, you may open your csv file into MS Excel manually and check.

Thank you.

3

Thank you very much for your quick response.

You are correct. Aspose.cells behaves the same way MSExcel.

With MSExcel, we can some how get the data without trimming the leading zeros by the following method,

1)Renaming the .csv file to .txt to prevent automatic parsing,

2)Importing it as a text delimited file

3)Forcing all column to text mode.

Do we have any provision or work around with Aspsose.cells to achieve this ?

Thanks & Regards,

Ranjith

4

Hi,

I think for you may use/instantiate TxtLoadOptions object to specify the concerned attributes, e.g ConvertNumericData etc. So, if you do not want to remove the leading Zeros while loading and process it as string values, you may try:
txtLoadoption.ConvertNumericData = false;

Otherwise the component will convert it to numeric values (by default) as MS Excel does.

See some code segments for your reference, you can use it for both txt and CSV file formats.

1)
//Instantiate Text File’s LoadOptions
TxtLoadOptions txtLoadOptions = new TxtLoadOptions();
//Specify the separator
txtLoadOptions.Separator = Convert.ToChar(",");
txtLoadOptions.ConvertNumericData = false;
//Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(“e:\test2\MyFile.txt”, txtLoadOptions);

Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;


MessageBox.Show(cells[“A1”].StringValue);
MessageBox.Show(cells[“A2”].Value.ToString());
MessageBox.Show(cells[“B3”].StringValue);


2)
//Instantiate Text File’s LoadOptions
TxtLoadOptions txtLoadOptions = new TxtLoadOptions(LoadFormat.CSV);
txtLoadOptions.ConvertNumericData = false;
//Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(“e:\test2\Book1.csv”, txtLoadOptions);

Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

MessageBox.Show(cells[“A1”].StringValue);
MessageBox.Show(cells[“A2”].Value.ToString());
MessageBox.Show(cells[“B3”].StringValue);



Hope, this helps.

Thank you.

5

Hi,

Thank you very much. This solution works fine with the latest version dll .

I am using v 4.9.0.0 with which TxtLoadOptions is not available. Is this option available with v4.9.0.0 in a different class or should i use the latest dll?

Thanks,

Ranjith

6

Hi,

Well, TxtLoadOptions is a new API included in the later versions of the product.
Since you are using some older version of the product, so, you may try (not sure if it will work fine for you):
e.g
workbook.ConvertNumericData = false;
//or
workbook.Settings.ConvertNumericData = false;

If this is not working with your version v4.9.0, I am afraid, you need to use the latest version of the product as there is no alternative to your requirements.

Thanks for your understanding!

7

Thank you very much for your quick response every time. I have to use the latest version of Aspose.cells as setting the ConvertNumericData = false in Workbook.Settings does not work.

Thanks & Regards,

Ranjith