How to change the separator used in a CSV file?
17,635
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.
Author by
Bjorn ten Broeke
Updated on December 03, 2022Comments
-
Bjorn ten Broeke over 1 year
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:
- the comma is not getting changed in a semicolon.
- 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)
-
Willem Van Onsem over 6 yearsThe
delimiter=';'
should be a semicolon. -
Bjorn ten Broeke over 6 yearssorry, copied the code whom I was playing around with. there is actually a ;
-
caot over 6 yearscheck this link unix.stackexchange.com/questions/159367/…
-
PM 2Ring over 6 years@caot Bjorn is using Windows, he may not have sed, or an equivalent program.
-
Bjorn ten Broeke over 6 yearscorrect , a few solutions I already have seen are in linux or other OS. Cant work with that.....
-
Ori over 6 yearsWondering, have you thought about streaming the whole content char by char, from input to output, but replace
,
chars with;
? -
caot over 6 yearscheck this link for Windows stackoverflow.com/questions/42482508/…
-
PM 2Ring over 6 yearsI just tested the core of your code, the stuff starting at the
with
statement, and it works as expected. -
PM 2Ring over 6 yearsI 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 thestr.replace
method. -
Mark Tolonen over 6 yearsSee stackoverflow.com/a/3348664/235698 for why you get blank lines.
-
Bjorn ten Broeke over 6 years@ 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 over 6 yearsHi, 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 over 6 years; must be , in the wrong example