3

For a study project I have many many csv files that I need to change from comma (,) separated to semicolon (;) separated. So I only need to change the separator.

I normally do it in Excel, but that takes a lot of work. And there I need to do it for every file separately plus Excel take a lot of time to do it.

I have made a input and output folder. That works fine in the code below. The problem is:

  1. the comma is not getting changed in a semicolon.
  2. and for some reason it is adding a blank line, I don’t know why it does that.

Can somebody give some tips?

import csv 
from pathlib import Path

folder_in = Path(r'C:\convert\Trajectory\In') 
folder_out = Path(r'C:\convert\Trajectory\Out')

for incsv in folder_in.iterdir():
    outcsv = folder_out.joinpath(incsv.name)
    with open(str(incsv), 'r') as fin, open(str(outcsv), 'w') as fout:
        reader = csv.DictReader(fin)
        writer = csv.DictWriter(fout, reader.fieldnames, delimiter=';')
        writer.writeheader()
        writer.writerows(reader)
martineau
  • 119,623
  • 25
  • 170
  • 301
Bjorn ten Broeke
  • 135
  • 1
  • 3
  • 10
  • 1
    The `delimiter=';'` should be a semicolon. – Willem Van Onsem Jul 22 '17 at 15:18
  • sorry, copied the code whom I was playing around with. there is actually a ; – Bjorn ten Broeke Jul 22 '17 at 15:19
  • check this link https://unix.stackexchange.com/questions/159367/using-sed-to-find-and-replace – caot Jul 22 '17 at 15:23
  • 1
    @caot Bjorn is using Windows, he may not have sed, or an equivalent program. – PM 2Ring Jul 22 '17 at 15:25
  • correct , a few solutions I already have seen are in linux or other OS. Cant work with that..... – Bjorn ten Broeke Jul 22 '17 at 15:27
  • Wondering, have you thought about streaming the whole content char by char, from input to output, but replace `,` chars with `;` ? – Ori Jul 22 '17 at 15:29
  • check this link for Windows https://stackoverflow.com/questions/42482508/replacing-characters-in-a-text-file-from-windows-batch-file – caot Jul 22 '17 at 15:29
  • I just tested the core of your code, the stuff starting at the `with` statement, and it works as expected. – PM 2Ring Jul 22 '17 at 15:30
  • 1
    I don't know why the code doesn't work for you. (I don't get the unwanted blank lines because I'm using Linux). But if you want to do a simple search-and-replace on every comma in the file, there's a simpler way to do that, you don't need the `csv` module. Just read the whole file into a string and use the `str.replace` method. – PM 2Ring Jul 22 '17 at 15:33
  • See https://stackoverflow.com/a/3348664/235698 for why you get blank lines. – Mark Tolonen Jul 22 '17 at 15:35
  • @ ori Bar Lian, do you have a suggestion how I can stream it from the in folder to the out folder with replacing the ; to , – Bjorn ten Broeke Jul 22 '17 at 16:14
  • Hi, if I test this, then it goes wrong, it doesnt replace it. 48.08132977;11.15165874;595.635;202.961;-1.699;2.788;0.322;0.347;0.676; 48.08135273;11.15166321;595.563;157.345;1.809;1.521;0.318;0.339;0.666; if I use this it goes OK A, B, C, D 1, 2, 3, 4 5, 6, 7, 8 9, 10, 11, 12 why doe sthat happen – Bjorn ten Broeke Jul 22 '17 at 19:06
  • ; must be , in the wrong example – Bjorn ten Broeke Jul 22 '17 at 19:13

1 Answers1

7

There are no answer, here is my proposition for a csv comma to semicolon implementation on the same file:

path="file_to_convert.csv"

reader = list(csv.reader(open(path, "rU"), delimiter=','))
writer = csv.writer(open(path, 'w'), delimiter=';')
writer.writerows(row for row in reader)

I used the list() so the content of reader is kept and I reopen the file to write in it.

If you don't need the change to be in the same file you can check this answer.

Sylhare
  • 5,907
  • 8
  • 64
  • 80