0

I have a csv file with the following structure:

"txNomeParlamentar";"ideCadastro";"nuCarteiraParlamentar";"nuLegislatura";"sgUF"
"AVANTE;1;1;2015;PP"

I need him to stay like this:

"txNomeParlamentar";"ideCadastro";"nuCarteiraParlamentar";"nuLegislatura";"sgUF"
"AVANTE";"1";"1";"2015";"PP"

I received this .csv file from someone else, so I do not know how the conversion was done. I am trying unsuccessfully with the code below:

input_fd = open("/home/gustavo/Downloads/Redes/Despesas/csvfile.csv", 'r')
output_fd = open('dados_2018_1.csv', 'w')
for line in input_fd.readlines():
    line.replace("\"","")
    output_fd.write(line)
    input_fd.close()
output_fd.close()

Is it possible to make this change or will I have to do the conversion from an xml file to a csv, and make this change during the conversion?

Improve this question
3
  • seems your file delimiter is a semicolon, and you're trying to parse semicolons within string literals as delimiter also, is that correct? Commented May 29, 2019 at 2:56
  • Yes my delimiter is a semicolon; I need the element between the semicolons to be in quotation marks, as in the second code sample I posted in question Commented May 29, 2019 at 3:05
  • Will it be either some strings with semicolon between them, and one strings with semicolon within it, or are there any other kinds of strings present in your csv @Costa.Gustavo Commented May 29, 2019 at 3:58

4 Answers 4

Reset to default
1

First: tell the reader to use delimiter=";" and quoting=csv.QUOTE_NONE. This will properly split your second line which is a string literal containing your delimiter, which you desire to be split. We'll tweak that data to remove the quotation marks (otherwise our output will be quoted strings like '"txNomeParlamentar"', etc).

import csv
with open('file.txt') as f:
     reader = csv.reader(f, delimiter=";", quoting=csv.QUOTE_NONE)
     data = [list(map(lambda s: s.replace('"', ''), row)) for row in reader]

Then: we write the file back out, with the delimiter=";", and quoting=csv.QUOTE_ALL to ensure each item is set in quotes

with open('out.txt', 'w', newline='') as o:
     writer = csv.writer(o, delimiter=";", quoting=csv.QUOTE_ALL)
     writer.writerows(data)

Input:

"txNomeParlamentar";"ideCadastro";"nuCarteiraParlamentar";"nuLegislatura";"sgUF"
"AVANTE;1;1;2015;PP"

enter image description here

Output:

"txNomeParlamentar";"ideCadastro";"nuCarteiraParlamentar";"nuLegislatura";"sgUF"
"AVANTE";"1";"1";"2015";"PP"

enter image description here

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

Comments

1

A couple things. First, you do NOT have a csv file because in a csv file, the delimiter is a comma by definition. I'm assuming you want the values in your data file to (1) remain separated by semicolons [why not fix it and make it commas?] and (2) you want each value to be in quotation marks.

If so, I think this will work:

# data reader

in_file = 'data.txt'
out_file = 'fixed.txt'
output = open(out_file, 'w')
with open(in_file, 'r') as source:
    for line in source:
        # split by semicolon
        data = line.strip().split(';')             
        # remove all quotes found
        data = [t.replace('"','') for t in data]   
        for item in data[:-1]:
            output.write(''.join(['"', item, '"',';']))
        # write the last item separately, without the trailing ';'
        output.write(''.join(['"', item, '"']))
        output.write('\n')
output.close()

If your target user is python, you should consider replacing the semicolons with commas (correct csv format) and forgoing the quotes. Everything python reads from csv is taken in as string anyhow.

Improve this answer

2 Comments

it's categorically untrue that the delimiter must be a comma, while I guess this is strictly true in the narrowest sense of the term, "csv" is often used a shorthand for any sort of regularly-delimited file: tab-delimited, pipe-delimited, etc. and the csv.reader supports the delimiter argument so that you can override the default (comma).
@DavidZemens. Well, we are going to disagree. The beauty of .csv is the simplicity and the portability/universality. Just because somebody made a parser that can use any delimiter, doesn't mean it should be exercised.
1

Using csv module.

Ex:

import csv

with open(filename) as csvfile:
    reader = csv.reader(csvfile, delimiter=";")
    headers = next(reader)    #Read Headers
    data = [row.strip('"').split(";") for row in csvfile]    #Format data

with open(filename, "w") as csvfile_out:
    writer = csv.writer(csvfile_out, delimiter=";")
    writer.writerow(headers)   #Write Headers
    writer.writerows(data)     #Write data
Improve this answer

Comments

-1

You could use the csv module to do it if you massage the input data a little first.

import csv


#input_csv = '/home/gustavo/Downloads/Redes/Despesas/csvfile.csv'
input_csv = 'gustavo_input.csv'
output_csv = 'dados_2018_1.csv'

with open(input_csv, 'r', newline='') as input_fd, \
     open(output_csv, 'w', newline='') as output_fd:

    reader = csv.DictReader(input_fd, delimiter=';')
    writer = csv.DictWriter(output_fd, delimiter=';',
                            fieldnames=reader.fieldnames,
                            quoting=csv.QUOTE_ALL)

    first_field = reader.fieldnames[0]
    for row in reader:
        fields = row[first_field].split(';')
        newrow = dict(zip(reader.fieldnames, fields))
        writer.writerow(newrow)

print('done')
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.