Reading CSV and need to treat numeric column as alphanumeric

Aspose.Cells Product Family
1

I am running the 4.4.1 hotfix version of the Cells software.

I am reading a csv-formatted file. This file contains a particular column--the data in which can either be all numeric or all alphanumeric for a particular row. I need to treat all rows for this particular column as alphanumeric.

A value of "00139820" in the csv-file gets returned to my program as a numeric value of: 139820. The leading zeroes get stripped, and I need them--they are an important component of the data itself.

I have attempted to load this column by referencing myCells[myRow, 2].StringValue.ToString(); I have also tried a number of other properties, but nothing allows the leading zeroes to be represented.

What can I do?

Thank You,

David Nowak

2

Hi David,

Well, For a .csv file, if cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are also lost.

I think you may try to set a custom style i.e. "00000000" before obtaining the values from the cell(s) for your need.

Example (Suppose we have a .csv file which has "00139820" value into A1 cell in the worksheet ):

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\mybook.csv",FileFormatType.CSV);
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].Style.Custom = "00000000";
MessageBox.Show(workbook.Worksheets[0].Cells["A1"].StringValue);

Thank you.

3

Ok, but this would require that I know the total length of the data contained in the column. What happens if I have data of varying length in the same column? For example, A1 contains "00139820", but A2 contains "000442".

Is there no way that I can tell Cells to treat a particular column as text?

Thank You.

4

If I can set the style of a particular cell, can I also set its format (string, text, etc.), so that it returns the entire contents of the cell?

My specific problem is that Cells is seeing a value such as "00012345" as a numeric. It needs to treat it as text.

Thank You,

David.

5

Hi,

Well, As I said before I think for a .csv file, all formatting will be lost. So if you can see a value i.e. "00012345" in notepad, when you open that file into MS Excel, MS Excel will take it as 12345 in general format and all the leading zeros will not be visible any more.

Any how we will still check to see if there is some option to do it.

Thank you.

6

This is correct, a value visible in Notepad of "00012345" will appear as 12345 in Excel. However, if I "Import" the csv file into Excel, I have the option of setting any specific column to "Text" format--this is accomplished in the Import Wizard. When the CSV is imported, the column will appear as it does in NotePad: "00012345".

The Aspose software allows me to specifically return a DateTimeValue from a cell. Why can't it allow me to return a "Text" value as well?

Thank You,

David.

7

Hi David,

Please set the property Workbook.ConvertNumericData = false.See following codes:

Workbook workbook = new Workbook();
workbook.ConvertNumericData = false;
workbook.Open(@"F:\FileTemp\test.csv",',');
workbook.Save(@"F:\FileTemp\dest.xls");

8

Hello Warren,

Thank you sooooo much. This works perfectly.

Request for improvement: while this treats the entire worksheet as columns of string data, it would be very nice if individual columns could be flagged to be read as string and others left as numeric.

But again, this works for my purposes.

Thanks again for the excellent support.

David Nowak.

9

I am having a similar problem. My data for the same column can take many forms it can be 01 , 01-01-01, 01-01 … For this reason I cannot use a custom format and if the value happens to have leading zeros it removes them.

10

Hi,

Could you create a sample test code and post it here with the template file to reproduce the issue. We will check it soon.

Thank you.