Why string element of a list converted to integer when saved in CSV file?

Question

I have a list of lists below:

mylist = [['HBASE-5727', 2, '66772ce043', '1044d5e353'],
          ['HBASE-5286', 2, '180b41f518', '0132176084'],
          ['HBASE-532', 3, 'ef5bb6f316', 'fb2db8cdaa']
         ]

I need to save that list into CSV file. I wrote this code:

with open("numberofbugs.csv",'w') as csvfile:
     header = ['bug_id', 'number_of_bugs', 'first_commitID', 'last_commitID']
     writers = csv.writer(csvfile)
     writers.writerow(header)
     for item in mylist:
          writers.writerow(item)

However, the fourth element in the second sublist is written as integer 132176084. Here's the CSV file resulted:

bug_id     | number_of_bugs | first_commitID | last_commitID |
--------------------------------------------------------------
HBASE-5727 |              2 | 66772ce043     | 1044d5e353    |
HBASE-5286 |              2 | 180b41f518     |     132176084 |
HBASE-532  |              3 | ef5bb6f316     | fb2db8cdaa    |

Why is it happen and how to keep the type of the element as it is written in the list (as string), so the value still 0132176084 instead of 132176084?

Cannot confirm. How did you open the file - in Excel or in a text editor? In the former case, it was probably Excel that converted the strings to numbers. Judging by indentation, it was Excel indeed. — DYZ, Apr 09 '18 at 03:04
Will second @DyZ comment above. or u can try `str(item)` in `writers.writerow(str(item))` — Rehan Azher, Apr 09 '18 at 03:06
yes, I open the file using excel. Is it possible to keep the type as a string in excel? — YusufUMS, Apr 09 '18 at 03:07
https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates — DYZ, Apr 09 '18 at 03:10

Martin Evans · Answer 1 · 2018-04-19T17:23:17.493

As suggested, you could pretend that your third and forth columns are formulas by enclosing them with ="....." as follows:

import csv

mylist = [['HBASE-5727', 2, '66772ce043', '1044d5e353'],
          ['HBASE-5286', 2, '180b41f518', '0132176084'],
          ['HBASE-532', 3, 'ef5bb6f316', 'fb2db8cdaa']
         ]

with open("numberofbugs.csv", 'w', newline='') as csvfile:
    header = ['bug_id', 'number_of_bugs', 'first_commitID', 'last_commitID']
    writers = csv.writer(csvfile)
    writers.writerow(header)

    for item in mylist:
        item[2:4] = ['="{}"'.format(c) for c in item[2:4]]
        writers.writerow(item)

The resulting numberofbugs.csv file would then look as follows when viewed in a text editor:

bug_id,number_of_bugs,first_commitID,last_commitID
HBASE-5727,2,"=""66772ce043""","=""1044d5e353"""
HBASE-5286,2,"=""180b41f518""","=""0132176084"""
HBASE-532,3,"=""ef5bb6f316""","=""fb2db8cdaa"""

Why string element of a list converted to integer when saved in CSV file?

1 Answers1