How to write data to CSV file into correct column heading in Python?

Question

I have a dictionary data that I need to write it into a CSV file under the heading Form Name Type & Definition, the dictionary data to write is in the code snippet below.

writeData.py

def writeCSV():

    # Dictionary Data to write received from Form Submit
    csvData = {
                'form'                      : 'Customer', 
                'Customer [form]'           : 'Customer is module for recording information related to customer such as Name, Address, Date of Birth, Place of Birth, ID Number, etc.', 
                'Salutation [label]'        : 'A greeting in words or actions, or the words used at the beginning of a letter or speech. This field has values such as Mr, Ms, Miss.', 
                'First Name English [label]': 'The name that was given to you when you were born and that comes before your family name. This field accept only English Character.'
                }

    items = {key:value  for key,value in csvData.iteritems() if key != 'form'} 

    form = csvData.get('form')

    Columns = ['Form','Name','Type','Definition']

    string  = ''

    with open("myfile.csv","w+") as f:

        # Write Heading
        for col_header in Columns:
            string = string + "," + col_header
        f.write(string[1:]+'\n')

        # Write Data Body
        string = ''
        for key,value in items.iteritems():
            string = form + "," + key + "," + " " + ","+value
            f.write(string)
            f.write('\n')

    return ''

writeCSV()

However, after I executed the python script above, data was written correctly under the heading Form, Name, and Type. Yet, under the heading Definition, the data was expanded to some more column be young its heading Definition.

I searched around but no clue why it expands column like this, or the amount of data is limited inside on csv column? What's wrong with this, how can I write its data in correct column of CSV file? Thanks.

Uli Sotschok · Accepted Answer · 2019-03-19 19:09:05Z

1

The problem is that csv delimiter each column by a special character. In your case, you are using a comma ','. But in your text also commas occur. So the csv takes this as a delimiter and interprets it as a new column. You can switch from comma to semicolon ';' as a delimiter. But even then you have to ensure that there are no semicolons in your original text.

If you make it this way you need to change these lines:

string = string + ";" + col_header  # ; instead of ,
string = form + ";" + key + ";" + " " + ";"+value

But I would suggest using a library, like @Nathaniel suggests

edited Mar 19, 2019 at 19:09

answered Mar 19, 2019 at 19:02

Uli Sotschok

1,2461 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Houy Narun Over a year ago

yes it is. I have tried to remove comma "," separated from my csvData dict, and it works. Would like to adding that the delimiter also determined by software application to view the file as I viewed it with Libre Office Cal, it allow us to choose text delimiter on opening file, by selecting a different delimiter from script, resulting in different result as well. Anyway, thanks very much for helpful answer :)

Scott Hunter Over a year ago

This is why there is a csv module in Python.

Nathaniel · Accepted Answer · 2019-03-19 19:06:25Z

1

You may have success converting your dictionary to a data frame and then saving it to a csv:

import pandas as pd

csvData = {'form'                      : 'Customer', 
           'Customer [form]'           : 'Customer is module for recording information related to customer such as Name, Address, Date of Birth, Place of Birth, ID Number, etc.', 
           'Salutation [label]'        : 'A greeting in words or actions, or the words used at the beginning of a letter or speech. This field has values such as Mr, Ms, Miss.', 
           'First Name English [label]': 'The name that was given to you when you were born and that comes before your family name. This field accept only English Character.'
           }

# Try either of these
df = pd.DataFrame(csvData, index=[0])
#df = pd.DataFrame.from_dict(csvData)

# Output to csv
df.to_csv('myfile.csv')

Without some example data, it is difficult to test this on your data.

edited Mar 19, 2019 at 19:06

answered Mar 19, 2019 at 19:00

Nathaniel

3,32013 silver badges19 bronze badges

3 Comments

story645 Over a year ago

And if you want to stick to the standard library, there's the csv module docs.python.org/3/library/csv.html

Houy Narun Over a year ago

@Nathaniel, that is a very interesting package I've never known before, I will try that, thanks very much :)

Nathaniel Over a year ago

@HouyNarun No problem!

Scott Hunter · Accepted Answer · 2019-03-19 18:58:10Z

0

It did not expand into adjacent columns; because of the size of the text, it doesn't fit the column width, and Excel's default is to draw that text over adjacent columns. You can verify this is the case by selecting cells that it appears to have expanded into, and seeing they are in fact empty. You can also change the way these cells are displayed, "wrapping" their contents within the column provided (making the rows taller).

answered Mar 19, 2019 at 18:58

Scott Hunter

50k12 gold badges65 silver badges107 bronze badges

2 Comments

Houy Narun Over a year ago

Thanks and apologized could be my uploaded image confused you specifically at column row D:4, look very much like it is one single sentence :)

Scott Hunter Over a year ago

D4 actually is one single sentence; its row 3 that got split.

Collectives™ on Stack Overflow

How to write data to CSV file into correct column heading in Python?

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related