How can I convert this text file to .csv in Python?

Question

I have this .txt file that has a table/dataframe, I would like to convert it to .csv in Python, could you help me? (sorry for any mistakes in my question)

Image:

Full column example:

order-id    order-item-id   purchase-date   payments-date   buyer-email buyer-name  payment-method-details  cpf ship-county buyer-phone-number  sku product-name    quantity-purchased  currency    item-price  item-tax    shipping-price  shipping-tax    ship-service-level  recipient-name  ship-address-1  ship-address-2  ship-address-3  ship-city   ship-state  ship-postal-code    ship-country    ship-phone-number   delivery-start-date delivery-end-date   delivery-time-zone  delivery-Instructions   is-business-order   purchase-order-number   price-designation
701-0760000-5410044 00083400045170  2022-06-25T15:16:44+00:00   2022-06-25T15:16:44+00:00   aaaaaaaaaaaaaaa@marketplace.amazon.com.br   Everton Everton da Everton  Other   000.000.000-01  Cidade Cidade       000000132046310000  Fogão Fogão 5 Bocas De Fogão Cor Inox Com Mesa De Fogão E Touch Timer Com Autodesligamento - 0YS5000 110V   1   BRL 1119.00 0.00    101.90  0.00    Standard    Everton Everton da Everton  Rua das Ruas Ruas 111   Apartamento 11 Cidade Cidade        São José dos Josés  PR  11135111    BR                      false

[`pandas.read_csv()`](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) to read it in and [`pandas.DataFrame.to_csv()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html) to write it out. Is there a particular part of this you are stuck with? — JNevill, Aug 16 '22 at 17:40
I want to turn a .txt file into .csv, would pandas.read_csv() then pandas.DataFrame.to_csv() solve my problem? — , Aug 16 '22 at 17:43
Yes. Likely. `.read_csv()` will read in a flat file into a dataframe. Then you can take that dataframe and do `mydataframe.to_csv('somefile.csv')` to write it back out in csv format. Check out the link on `read_csv()` as you likely to need to specify some parameters to tell `read_csv()` how your file is formatted so it can read it in. — JNevill, Aug 16 '22 at 17:46
Since some answers have been supplied but aren't meeting your requirements, I would suggest that you edit your question to include what you have tried and why they aren't working for you. Editing the question will help breathe new life into the question for folks that monitor tags. I doubt anyone will upvote as the question is broad and vague which is why the answers aren't exactly fitting your requirements. — JNevill, Aug 19 '22 at 14:11
Also, I would highly suggest that you share via copy/paste from your input file a few sample records and your desired results that would be written out by this code. This will help give folks something to aim for as well. Lastly, I would suggest not checking on whether the output from the answers works for you by opening it in Excel. Excel is not a flat file/text editor and it will likely make bad decisions about how to open the file and present the data. If your requirement is that the output be in excel format, then state that as Python can handle writing xlsx files as well. — JNevill, Aug 19 '22 at 14:13

score 1 · Answer 1 · answered Aug 16 '22 at 17:48

1

You can use pandas to open it:

import pandas as pd
text_file = pd.read_table('file_name.txt')

And write it as a csv:

text_file.to_csv('new_file_name.csv')

answered Aug 16 '22 at 17:48

Andrew Richter

39
3

It worked a little, but all csv columns are in the first cell: https://prnt.sc/aPdVZ1ZbULUE – Aug 16 '22 at 17:58
@BoTop Are you opening it in excel? – Andrew Richter Aug 16 '22 at 18:03
After transforming the .txt into .csv I opened it in excel! – Aug 16 '22 at 18:05
Well, excel opens files, by default, in xlsx format, you need to open it as csv. Open the Data section, there you will find a button that opens files from text. Csv is, basically, a text file sepparated by commas. – Andrew Richter Aug 16 '22 at 18:13
I added a complete record with all columns in the last edit of my question above! Your code is not able to transform a .txt file into .csv with those two lines. – Aug 16 '22 at 18:24

score 0 · Answer 2 · answered Aug 16 '22 at 17:47

This is if you don't want to use other packages such as pandas, this is the 'manual' way to go about creating a CSV from a TXT. I am assuming that you have tab characters as separators in your text file. If this is not the case, then you will need to change this line:

values = line.strip("\n").split("\t")  # split on tab character

The "\t" represents the character that separates the columns.

This block of code will take your text file and convert it to a csv file. Put this in a python file in the same folder as your .txt file.

filename = "table.txt"  # name of your file, this is probably different on your end!

rows = []

with open(filename, "r") as f:
    for line in f.readlines():
        values = line.strip("\n").split("\t")  # split on tab character
        rows.append(values)
        
print(rows)
        
with open("my_file.csv", "w") as f:
    for row in rows:
        for i, element in enumerate(row):
            f.write(f"{element}")
            
            if i != len(row)-1:
                f.write(",")
            else:
                f.write("\n")

It worked a little, but there are more columns in the .txt file and it is giving an error (UnicodeDecodeError: 'charmap' codec can't decode byte 0x81 in position 6513: character maps to ). — , Aug 16 '22 at 18:00
This code should handle any number of columns, but it appears if that is happening then the table itself has some whacky characters in it — Mitchnoff, Aug 16 '22 at 18:02
Yes, that's probably it, but thanks for the code, I'll use it. — , Aug 16 '22 at 18:03
Using the `csv` module is a happy medium between using pandas and doing it manually. The `csv` module allows you to specify the delimiters and handles quoted fields. — Steven Rumbalski, Aug 19 '22 at 13:51

How can I convert this text file to .csv in Python?

2 Answers2