Zero get excluded when import data to excel .xlsx

Python Help
1

Hi all,
My python script is accessing data and exporting to excel .xlsx
I have a text field which contains 0 in front.
Problem facing is when export to .xlsx the text field License number is deleted.

01234 but I am receiving it like 1234.

print('Get License Data')

query = """
SELECT
GivenName,
Surname,
Licence_Number
From
Table
"""

ws = wb.Worksheets("data")

try:
    sqlite_cursor.execute(query)
    projects_data = sqlite_cursor.fetchall()
    
    output = list()

    for row in projects_data:        

        output.append(list(row))

    row_count = len(output)

    if row_count > 0:
        col_count = len(output[0])
        ws.Range(ws.Cells(2,1),ws.Cells(row_count + 1, col_count)).Value = output

Any help how to fix to format it so that zero doesn’t get deleted?
Thanks

2

The problem is Excel interpreting it as a number rather than a zip code. If you can format the cell as “Text” that might work. Alternatively you can prepend a single quotation, ' before the zip code and it should display properly (I’m not sure if this will mess with things later, though)

2 Likes
4

How to accept this as answer

1 Like
5

This forum doesn’t have that feature enabled, but your reply will be sufficient for future readers!

Were you able to pre-format the cell as text in your code, or did you use the ' to get it to display? I wasn’t sure if the first option was possible with whatever library you are using to write Excel files.

6

Hi James,

I did pre-format the cell to text.
Thanks

7

This is normal behavior when you write a cell with .write_number(). When you open the Excel file Excel will drop any leading zeroes for cells formatted as a number.

So when writing a zip code use .write_string().

When I’m writing Excel files with Xlsxwriter and pandas I have 2 routines:

  1. WriteXlsx(). Writes out the whole Excel file row by row and calls WriteXlsxCell().
  2. WriteXlsxCell(): parameters are row, column, data and I think a format object. It writes one cell and determines if the cell is a number, text, or formula, and calls .write_number(), .write_string(), or .write_formula().

Here is the documentation for the Worksheet class which has the write routines like .write_string(), .write_number(), and .write_formula().