Dates and certain values getting converted, raw and cellDate not helping #749

New Issue

2017-07-27T07:33:05Z

GreggOD commented

2017-07-27 07:33:05 +00:00

(Migrated from github.com)

Hi,

Thanks for the library, its great. But as of late i've been having some issues.

As usual i have looked through all issues and documentation.

I have a CSV with cells that have different date formats, negative numbers, positive numbers, decimals, text etc..

If i use raw: true, then some of the dates get converted into a number which I don't want, i want the dates to stay as is. If i use CellDates: true, some dates get converted, some don't and the format they get converted to is different to how it is in the csv (csv: dd/mm/yyyy, sheetJS Obj: mm/dd/yy). With or without raw, I have a few negative numbers which get converted.. so for example: -0.08 gets converted to 36739.

If i load the CSV into sublime text the values in the CSV match that of excel, the actual values. But SheetJS is converting these. Raw converts them, not having raw converts them, dates are converted either way and i'm trying to find a way to get the information as it is without any changing.

Is this possible?

Hi, Thanks for the library, its great. But as of late i've been having some issues. As usual i have looked through all issues and documentation. I have a CSV with cells that have different date formats, negative numbers, positive numbers, decimals, text etc.. If i use `raw: true`, then some of the dates get converted into a number which I don't want, i want the dates to stay as is. If i use `CellDates: true`, some dates get converted, some don't and the format they get converted to is different to how it is in the csv (`csv: dd/mm/yyyy, sheetJS Obj: mm/dd/yy`). With or without `raw`, I have a few negative numbers which get converted.. so for example: -0.08 gets converted to 36739. If i load the CSV into sublime text the values in the CSV match that of excel, the actual values. But SheetJS is converting these. `Raw` converts them, not having `raw` converts them, dates are converted either way and i'm trying to find a way to get the information as it is without any changing. Is this possible?

GreggOD commented

2017-07-27 12:05:01 +00:00

(Migrated from github.com)

I have noticed something strange.. In excel i did a 'save as' still in csv format and the issue is resolved.

That doesnt help me as the CSV file I have is exported from another system and I cant get clients to save as every time.

Like i said in sublime text the values show correct, in excel they show correct but creating json gives a different result.

I have noticed something strange.. In excel i did a 'save as' still in csv format and the issue is resolved. That doesnt help me as the CSV file I have is exported from another system and I cant get clients to save as every time. Like i said in sublime text the values show correct, in excel they show correct but creating json gives a different result.

SheetJSDev commented

2017-07-27 17:01:27 +00:00

(Migrated from github.com)

@GreggOD We're looking into this. There's quite a bit of guesswork involved for CSV and it's not always correct.

Plaintext formats that mix content and presentation (CSV / HTML / PRN) are not great formats. On the writing side it is extremely easy to generate something that looks correct, but there's no "correct" way to read data back so we have to "guess" what type of data is contained in each cell. And that guess is currently too aggressive in parsing dates and not aggressive enough in interpreting number values.

To contrast with other formats, native Excel files separate the content (numeric values are always stored as IEEE754 doubles in XLSB and XLS, for example) from the presentation (separate number format codes), so there is little guesswork involved.

@GreggOD We're looking into this. There's quite a bit of guesswork involved for CSV and it's not always correct. Plaintext formats that mix content and presentation (CSV / HTML / PRN) are not great formats. On the writing side it is extremely easy to generate something that looks correct, but there's no "correct" way to read data back so we have to "guess" what type of data is contained in each cell. And that guess is currently too aggressive in parsing dates and not aggressive enough in interpreting number values. To contrast with other formats, native Excel files separate the content (numeric values are always stored as IEEE754 doubles in XLSB and XLS, for example) from the presentation (separate number format codes), so there is little guesswork involved.

GreggOD commented

2017-07-28 06:59:06 +00:00

(Migrated from github.com)

@SheetJSDev ahhh ok. Shame guys, sounds like a tough gig for you.

What i noticed was my 1 csv sheet has all values in double quotes: "value", the other had some values without double quotes and some with.. This seemed to be tripping things up.

So when I used cellText: true and raw: true, all the values that had double quotes were then again wrapped in quotes so they came out like this: ""Header"",""value"" but at least the cells were unaltered. I guess raw and cellDatesprobably don't matter if you use cellText?

For anyone else with this issue:
These are my reader options:

var readerOptions = {
	type: rABS ? 'binary' : 'base64',
	raw: true,
	cellText: true,
	WTF: true,
}

I then did this to remove each rows double quotes:

       // Clean Rows
        var row;
        for ( var v=0; v < app.file.length; v++ ){
            // Remove extra quotes, some sheets have something like ""text""
            row = JSON.stringify(app.file[v]).replace( /\\\"/g,'');
            app.file[v] = JSON.parse(row);
        }

I guess I could have stringified the whole file object and done the regex:
app.file = JSON.parse( JSON.stringify(app.file).replace( /\\\"/g, '' ) );

@SheetJSDev ahhh ok. Shame guys, sounds like a tough gig for you. What i noticed was my 1 csv sheet has all values in double quotes: "value", the other had some values without double quotes and some with.. This seemed to be tripping things up. So when I used `cellText: true` and `raw: true`, all the values that had double quotes were then again wrapped in quotes so they came out like this: `""Header"",""value""` but at least the cells were unaltered. I guess `raw` and `cellDates`probably don't matter if you use `cellText`? For anyone else with this issue: These are my reader options: ``` var readerOptions = { type: rABS ? 'binary' : 'base64', raw: true, cellText: true, WTF: true, } ``` I then did this to remove each rows double quotes: ``` // Clean Rows var row; for ( var v=0; v < app.file.length; v++ ){ // Remove extra quotes, some sheets have something like ""text"" row = JSON.stringify(app.file[v]).replace( /\\\"/g,''); app.file[v] = JSON.parse(row); } ``` I guess I could have stringified the whole file object and done the regex: ` app.file = JSON.parse( JSON.stringify(app.file).replace( /\\\"/g, '' ) );`

Sign in to join this conversation.