# How to create a csv file from text

**URL:** <https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820>\
**Category:** Python Help\
**Created:** [March 18, 2024, 1:00am UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820 "2024-03-18T01:00:44Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![rambleon](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rambleon](https://discuss.python.org/u/rambleon)\
**Post date:** [March 18, 2024, 1:00am UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/1 "2024-03-18T01:00:44Z")

</div>

I have this fixed width report file:

I need to insert commas between the values, remove all unnecessary spaces and enclose the alpha field on the right in quotes. I can’t see how to do this with pandas to\_csv. Please can someone tell me how to do this.  
Thanks

---

<div class="post-metadata">

**Author:** ![kknechtel](https://avatars.discourse-cdn.com/v4/letter/k/e47c2d/32.png) [@kknechtel](https://discuss.python.org/u/kknechtel)\
**Post date:** [March 18, 2024, 1:36am UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/2 "2024-03-18T01:36:56Z")

</div>

> [@rambleon](#):
>
> I can’t see how to do this with pandas to\_csv

`to_csv` is for creating a CSV from already loaded data in a Pandas DataFrame.

The natural way to approach the problem is to load the file into a DataFrame first. Assuming that the file you have is indeed fixed width (and it doesn’t merely _look_ fixed width due to clever use of tabs), the appropriate tool is [`pandas.read_fwf`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_fwf.html).

See here for a more detailed guide, which I found by putting `pandas constant width field` into a search engine:

> **[Parsing Fixed Width Text Files with Pandas](https://towardsdatascience.com/parsing-fixed-width-text-files-with-pandas-f1db8f737276)**
>
> How to easily read fixed width text files into Pandas DataFrames

After you have created the `DataFrame`, you can use its `to_csv` method normally. The format that you describe should already be the default.

---

<div class="post-metadata">

**Author:** ![rambleon](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rambleon](https://discuss.python.org/u/rambleon)\
**Post date:** [March 19, 2024, 6:41pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/3 "2024-03-19T18:41:51Z")

</div>

Hi Karl, Thanks for this.  
I tried to follow the method you suggested, but couldn’t get the right (any) formating. In the end I used this code:

```python
import re
file_lines =[]
filename = 'S285IBUD.CSV'
outfile = open(filename, 'w', encoding='ISO-8859-1')
with open("S285IBUD.PRN", encoding='ISO-8859-1') as the_file: 

     cnt = 0
     match = ""
     spc = ' '
     hash = '#'
     for line in the_file:
         #check if new page 
         match = re.findall(r'\f', line)
         if match: cnt = 0
         #check if new line
         match = re.findall(r'\n', line)
         if match: cnt+=1
         #start processing from 7th line, where line length is > 140
         if cnt > 7 and len(line) > 140:
            inp = line 
    # Insert commas after each field - fixed known positiom
            inp = inp[:11] + ',' + inp[11:]
            inp = inp[:24] + ',' + inp[24:]
            inp = inp[:29] + ',' + inp[29:]
            inp = inp[:43] + ',' + inp[43:]
            inp = inp[:57] + ',' + inp[57:]
            inp = inp[:62] + ',' + inp[62:]
            inp = inp[:77] + ',' + inp[77:]
            inp = inp[:90] + ',' + inp[90:]
            inp = inp[:95] + ',' + inp[95:]
            inp = inp[:108] + ',' + inp[108:]
            inp = inp[:113] + ',' + inp[113:]
            inp = inp[:128] + ',' + inp[128:]           
    #Find start (1st none blank character) of quoted string
            i = 129
            while i < 148:
                i+=1
                if inp[i] != ' ':
                  break
    #insert opening quote  
            inp = inp[:i] + '"' + inp[i:]
    #Replace spaces with '#' in quoted string          
            while i < 146:
                i+=1
                if inp[i] == ' ':
                    inp = inp[:i] + '#' + inp[i + 1:]
    #insert closing quote                
            inp = inp[:148] + '"' + inp[148:]
    #insert last comma (before last field)           
            inp = inp[:149] + ',' + inp[149:]
    #remove all spaces           
            newl = inp.replace(" ", "")
    #replace '#' with spaces in quoted string         
            inp = newl.replace("#", " ")
            outfile.write(inp) #The variable line is already formatted correctly!
            print("line :", inp)
outfile.close() #Close the file when we’re done!
            

```

Here’s the input and output:

 ![IN-OUTPUT](https://us1.discourse-cdn.com/flex002/uploads/python1/original/3X/a/5/a5d90209c04a2d042cb859dde13b0901aeeafb45.jpeg)

Like to know what I’m doing wrong

Thanks

---

<div class="post-metadata">

**Author:** ![kknechtel](https://avatars.discourse-cdn.com/v4/letter/k/e47c2d/32.png) [@kknechtel](https://discuss.python.org/u/kknechtel)\
**Post date:** [March 19, 2024, 9:00pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/4 "2024-03-19T21:00:21Z")

</div>

> [@rambleon](#):
>
> Like to know what I’m doing wrong

The first thing wrong is showing us an image of the data. Instead, please copy and paste data and format it like the code.

The second thing wrong is that you say that you tried doing it a different way and couldn’t get it to work, and seem to want to know why it didn’t work, but you don’t **show us the code for the different way** that you tried. If you mean that you think there is something wrong with the code you’re showing now, then you need to explain the actual issue. As far as I can tell, from what I can read in the image, you are getting a result that matches what you asked for originally.

---

<div class="post-metadata">

**Author:** ![rambleon](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rambleon](https://discuss.python.org/u/rambleon)\
**Post date:** [March 19, 2024, 9:25pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/5 "2024-03-19T21:25:41Z")

</div>

I don’t see how to upload the data, only image is authorized.  
Yes the code I posted works, what I said was that I couldn’t get your code to work, and was wondering why 'cause it’s far more concise and shows the power of python.

---

<div class="post-metadata">

**Author:** ![kknechtel](https://avatars.discourse-cdn.com/v4/letter/k/e47c2d/32.png) [@kknechtel](https://discuss.python.org/u/kknechtel)\
**Post date:** [March 19, 2024, 9:31pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/6 "2024-03-19T21:31:52Z")

</div>

> [@rambleon](#):
>
> I don’t see how to upload the data

It’s not uploading. It’s copying and pasting out of the screen that you took that screenshot from, and putting in some formatting markup - the same way that you add code to your posts. If that doesn’t work for some reason, then you’ll need to explain exactly how you are running the code and where/how you see the output.

---

<div class="post-metadata">

**Author:** ![rambleon](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rambleon](https://discuss.python.org/u/rambleon)\
**Post date:** [March 19, 2024, 11:33pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/7 "2024-03-19T23:33:07Z")

</div>

```python

	I N P U T: open("S285IBUD.PRN", encoding='ISO-8859-1') as the_file

    4240.13 4240.13 3 13034.71 13034.71 3 4240.13 99 19/09/21 DONALD DUCK 6181
 
    4000.00 4000.00 90 421574.91 421574.91 90 4000.00 99 5/07/17 MICKY MOUSE 82556

     659.24 659.24 71 137428.05 137428.05 71 659.24 99 27/06/17 MARKS AND SPENCER 125198

	 O U T P U T: outfile = open(filename, 'w', encoding='ISO-8859-1')
	 
4240.13,4240.13,3,,,,13034.71,13034.71,3,4240.13,99,19/09/21,"DONALD DUCK",6181
4000.00,4000.00,90,,,,421574.91,421574.91,90,4000.00,99,5/07/17,"MICKY MOUSE",82556
659.24,659.24,71,,,,137428.05,137428.05,71,659.24,99,27/06/17,"MARKS AND SPENCER",125198

```

---

<div class="post-metadata">

**Author:** ![kknechtel](https://avatars.discourse-cdn.com/v4/letter/k/e47c2d/32.png) [@kknechtel](https://discuss.python.org/u/kknechtel)\
**Post date:** [March 19, 2024, 11:48pm UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/8 "2024-03-19T23:48:06Z")

</div>

Thank you. But - is there something wrong with this result?

---

<div class="post-metadata">

**Author:** ![rambleon](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rambleon](https://discuss.python.org/u/rambleon)\
**Post date:** [March 20, 2024, 6:47am UTC](https://discuss.python.org/t/how-to-create-a-csv-file-from-text/48820/9 "2024-03-20T06:47:22Z")

</div>

No the result is fine, but like to do it using DataFrame .read\_fwf and .to\_csv as you suggested.  
Thanks