#
.xlsx file parsing doesn't parse currency the same way .csv does
#1338

Open

opened 4 years ago by mandros1
·
4 comments
No Branch/Tag Specified

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.87

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.11

v0.7.10

v0.7.1

v0.5.9

v0.5.8

v0.5.7

v0.5.10

v0.5.0

v0.4.3

v0.18.6

v0.18.5

v0.18.4+deno

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0+deno

v0.17.5

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.1

v0.12.0

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

**Milestone**

Set milestone

Clear milestone

No items

No Milestone

**Projects**

Set Project

Clear projects

No project

**Assignees**

Assign users

Clear assignees

No Assignees

**1 Participants**

**Notifications**

**Due Date**

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

**Dependencies**

No dependencies set.

Reference: sheetjs/sheetjs#1338

Reference in New Issue

There is no content yet.

Delete Branch '%!s(<nil>)'

Deleting a branch is permanent. It **CANNOT** be undone. Continue?

No

Yes

I have actually commented on one of the threads, but to be sure that you can see it I will open my own.

I have looked around the Issues a bit, trying to find a solution, but as it didn't amount to much I have the following issue:

I take the xlsx file and save it as csv and this is what I get

1. XLSXSoftware,"$6,526",$227,"$45,797","$42,354","$7,931","$15,802","$45,210","$49,233","$32,049","$7,519","$49,916","$21,721"

2. CSVSoftware,$6.526,$227,$45.797,$42.354,$7.931,$15.802,$45.210,$49.233,$32.049,$7.519,$49.916,$21.721

CSV is the format that is desirable, so is there any way I could achieve the same thing with xlsx, from what I read from other threads I believe it has something to do with the format of the cell, but I have no clue how to go about fixing it.

Any input would be highly appreciated,

Thank you :)

edit: it is connected explicitly with the currency, as the percentage is parsed fine

If you're trying to get the currency using a different thousands and decimal symbol, that option is not currently exposed by the number formatting library (Excel doesn't actually store the symbols; it's inherited from the Windows regional settings, just like the format code 14 Date format)

I force fixed it in my code by converting that comma into a dot inside my program. Thanks for the reply though. Good thing is that, even tough the format isn't the same, it surrounded it with quotes, so I was able to scope it and change it.

Really helpful tool and much the work!

Best regards

@mandros1 Can you share how you scoped the quotes ? I am having same issue need to fix it urgently.

@techcreatix I worked on a Django project, so this might not be useful for you, but I will post the whole method I used if anyone else is to stumble upon this.

` if request.method == 'POST':

excel_filename = request.POST['fileName']

excel_filedata = request.POST['fileData']

if (excel_filename != None and excel_filedata != None):

I pasted the whole function, but I believe this is the part you need "convSheetData = re.sub(r'"[^"]*"', lambda m: m.group(0).replace(',', '.'), sheetData[y])".

I hope I remember that correctly