Conversion text to number in python

Question

input is a text table - some columns are numbers and some text. I have python script to convert this text file to xlsx. After open in Excel or Libre all fields are text. Is there any option to convert some columns to number?

cat script.py:

import csv
import sys
from xlsxwriter.workbook import Workbook

# Add some command-line logic to read the file names.
tsv_file = sys.argv[1]
xlsx_file = sys.argv[2]

# Create an XlsxWriter workbook object and add a worksheet.
workbook = Workbook(xlsx_file)
worksheet = workbook.add_worksheet()

# Create a TSV file reader.
tsv_reader = csv.reader(open(tsv_file, 'rb'), delimiter='\t')

# Read the row data from the TSV file and write it to the XLSX file.
for row, data in enumerate(tsv_reader):
    worksheet.write_row(row, 0, data)

# Close the XLSX file.
workbook.close()

run script:

python script.py in.txt out.xlsx

I would like to change script to convert some columns to text and some to numbers and keep xlsx format. Any idea how to do that?

jmcnamara · Accepted Answer · 2017-05-11T09:30:24.443

2

You can use the strings_to_numbers XlsxWriter constructor option. From the docs:

strings_to_numbers: Enable the worksheet.write() method to convert strings to numbers, where possible, using float() in order to avoid an Excel warning about “Numbers Stored as Text”. The default is False. To enable this option use:

workbook = xlsxwriter.Workbook(filename, {'strings_to_numbers': True})

Example:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx', {'strings_to_numbers': True})
worksheet = workbook.add_worksheet()

worksheet.write(0, 0, 'Hello')
worksheet.write(1, 0, '123') # Write this string as a number.

workbook.close()

Output:

edited May 11 '17 at 09:30

answered May 11 '17 at 07:27

jmcnamara

38,196
6
90
108

Hi, thank you for comment. When I add this line to my script and try to run, I have an error: NameError: name 'xlsxwriter' is not defined Any idea? – Geroge May 11 '17 at 08:52
I changed xlsxwriter.Workbook(.. to xlsxwriter.workbook(... Because python is case sensitive, but still have the sam error: NameError: name 'xlsxwriter' is not defined Exception Exception: Exception('Exception caught in workbook destructor. Explicit close() may be required for workbook.',) in > ignored – Geroge May 11 '17 at 09:03
1

The syntax is correct. I'm the author of the module. I've added a working example that you can test. – jmcnamara May 11 '17 at 09:31
thank you for nice example. I am newbie in python.. My input text file has about 50 columns and I need to write as number for example only 10 columns. In my example I am using for loop - would be possible to show example how to modify my original script with for loop! Thank you again for your time!! – Geroge May 11 '17 at 12:41
For that case you should probably loop over the row *and* column and then only call `.write()` if `col < 10`. But that is probably worth asking in another question. – jmcnamara May 11 '17 at 13:26
Thank you. this saved me a lot of headache. – switchboard.op Apr 19 '20 at 00:09

score 1 · Answer 2 · answered May 10 '17 at 21:01

According to the documentation, you need to add a numeric format, and use it when you write cells which need numeric format.

# Add a number format for cells with money.
money = workbook.add_format({'num_format': '$#,##0'})

...

worksheet.write(row_idx, col_idx, your_value, money)

See Tutorial 2: Adding formatting to the XLSX File

Conversion text to number in python

2 Answers2

Linked