0

I have a code that creates a csv file, when I first open it I everything is in one column so I have to do the usual

Go to Data and do the following. The data is then spplited into columns.

enter image description here

I work with Office 365, and recently I was told that if I change the commas with semicolons then when I open the newly created file Csv file, Excel will automatically open the file already separated into columns.

I’m asking for some advice here, since having to do this process for every created Csv file is really time consuming.

Looking for a way to alter my code so it does this automatically maybe instead of splitting columns with commas, do it with semicolons in this case. Just to try if this works out. 

with open('created.csv', 'w', newline='') as f:
    writer = csv.writer(f)


  [1]: https://i.sstatic.net/OtxO4.png
Improve this question
4
  • 2
    Do you need df.to_csv('created.csv', sep=';') ? Commented Jan 16, 2020 at 13:21
  • @BigBen, in one of my computer when I open the file it is already separated into columns. On this other computer where I have office365 it doesn't show the csv file already in column Commented Jan 16, 2020 at 13:24
  • @jezrael, I'm not sure how would I change my current code to that. The code is this one stackoverflow.com/q/59680887/12325998 Commented Jan 16, 2020 at 13:30
  • Yes. Microsoft is Micro!@#$ in this case: CSV stands for COMMA separated value, yet Excel doesn't recognise commas as separators by default, only semicolons... While open software like LibreOffice will recognise both the correct format (commas) and Excel's semicolons. Commented Jan 16, 2020 at 13:30

1 Answer 1

Reset to default
2

If you already want to transform an existing file you can do it like that:

with open('created.csv', 'r', encoding='utf-8') as f_in, open("outfile.csv", 'w') as f_out:
    for line in f_in:
        line = line.split(",")
        line = ";".join(line)
        f_out.write(line)

In case you have already a dataframe you can do it like @jezrael said in the comment with: 

df.to_csv('created.csv', sep=';')

As mention in the comment you are already using the csv module to write your file. You have to change this line in your code:

writer = csv.writer(f)

to

writer = csv.writer(f, delimiter=';')

As for me if I open a csv splitted with "," I have to that thing you described in your question. But if I open a csv splitted with ";" it's already in the right columns. This is (for Windows user at least) dependent on your region settings. This can be different for everyone dependent on your language settings.

You can check them here and also change it if you want:

https://www.itsupportguides.com/knowledge-base/office-2013/excel-20132016-how-to-change-csv-delimiter-character/

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

10 Comments

thank you for your answer. This is my code stackoverflow.com/q/59680887/12325998. I would really want to inside the code there to have a csv file that is already in columns.
thank you that fixed it. Would really appreciate if you can have a look at this one stackoverflow.com/q/59753414/12325998. I'am dealing with the same problem . The code is the same as in the link I sent you in the comment above
@ebe what you can do is basically remove the operation character or you can add ' before each input hej;hej;-hej --> 'hej;'hej;'-hej I don't see another solution.
can you please give an answer on how to implement your approach Here
@ebe in the other threads there are a lot of links in the comments who answer the same problem and have better / the same solution that I gave you.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.