0

This is my first time posting here so I am sorry if I've missed some of the code of conduct you usually have.

I am having problems converting my Excel (xlsx) file to a CSV file using Python. This is required by my job, I wouldn't be otherwise doing it. So my excel file is structured like this: I have a name for our service, our buy price for it, and our customers buy price for it.

The problem that I am facing is that when lets say for example I have a line like this in my xlsx file:

BasicLevel    1798,20    1998,00
ExtremLevel   2,25       2,50

But whenever I convert my xlsx file to a csv file, the line turns into this:

BasicLevel    1516.41    1684.9
ExtremLevel   43344      43102

So now what has happened is that the numbers have indeed changed. This makes the whole program obsolete as the numbers HAVE to be correct.

This is my xlsx to CSV conversion code:

def csv_from_excel_max():
    wb = xlrd.open_workbook("Pricelist.xlsx")
    sh = wb.sheet_by_name("EUR")
    result_file = open("result_file.csv", "wb")
    wr = csv.writer(result_file, delimiter=";")
    rownum = 0

    while rownum < sh.nrows:

            wr.writerow([str(sh.cell(rownum, 3).value.replace(u"\xa0", "").replace(u"\u2122", "")).encode("utf-8"),
                         sh.cell(rownum, 9).value,
                         sh.cell(rownum, 10).value])
            rownum += 1
Rivena
  • 1
  • `ExtremLevel 43344 43102` these values look like a date. You probably shifted some columns or rows. – Luuklag May 28 '18 at 11:05
  • The problem could lie in your decimal points. The value "2,25" is separated by a comma which in international convention is actually a 1000 separator. Try change your extreme values into "2.25" and "2.5" and retry (same for the BasicLevel) – offeltoffel May 28 '18 at 11:08
  • @Luuklag this isn't the problem. My columns and rows are the right ones, I have doublechecked. – Rivena May 28 '18 at 11:14
  • @offeltoffel I did try that, but it didn't work. The numbers are already wrong when extracted from the xlsx file, so I have no idea whats gonig on and it's driving me mad – Rivena May 28 '18 at 11:16
  • 1
    @Rivena, you should check if those numbers do exist in your sheet(s). – Luuklag May 28 '18 at 11:18
  • Also there might be a syntax error in your replace according to: https://stackoverflow.com/questions/10993612/python-removing-xa0-from-string – Luuklag May 28 '18 at 11:22
  • Excel does not always show you the true value of a cell. As Luuklag pointed out, some are formatted as dates but in fact hold numerical values. Also the decimal points are only a matter of how the value is visiualized. Maybe the numbers are as they are, but in Excel something prints them out different than you expect. – offeltoffel May 28 '18 at 11:22
  • I could not reproduce your error. I created a new sheet named `EUR` and added the same information (decimal point though - you said it didn't matter for you). `print(sh.cell(0,1))` gives me the output `number:1798.2` as expected. – offeltoffel May 28 '18 at 11:27

1 Answers1

0

Okay so thanks to everyone contributing! Always remember to check that you're looking at the right sheet when doublechecking your numbers. I had the Dollars sheet open when in fact I was working with euros.

Rivena
  • 1