cancel
Showing results for 
Search instead for 
Did you mean: 

Number formatting in CSV file

hoernst
Participant

Hi all

I have some issues with the number formatting in CSV-files. If I export a table from a story in a CSV-file some of the numbers are formated as a date. (Please see screenshot below)

If I chose the option "Include number formatting (convert numbers to strings)" I get the numbers with the scale (in my case "Hours") so I can't do further calculations in Excel with them.

Do have any tips to prevent Excel from converting the numbers in a date format?

Thanks and best regards
Stefan

I_MCA
Advisor
Advisor

Hi Stefan,

Are you able to post a screenshot of the original SAC table please? I am curious to know the content of the cells that are being converted to dates.

Excel automatically converts some cells to date format when it thinks you have entered a date... and that cannot be turned off. For example, if I enter 21-1 or 21/1 into a cell, both will result in 21-Jan appearing in the cell and the formula bar will show 2022-01-21.

You can stop this by prefixing the entry with an apostrophe... for example, entering '21-1 will display 21-1 in the cell... but this only works before you enter the data into the cell... not after-the-fact like when data is exported.

In the short term you may need to follows Roy's suggestion to modify the data, but there may also be an opportunity for an enhancement request,

Kind Regards

Ian

Accepted Solutions (1)

Accepted Solutions (1)

roy_jin
Participant

Hi Stefan,

In data wrangling, you can do some further modification to your data by designing some simple formulas.

Back to your case, if the scale "Hours" appears in every cell, and the value before "Hours" is what you need, you may try the formula 'split' to remove "Hours" from your cells.

best regards,

Roy

Answers (0)