Read file and convert to JSON

Question

I have file which contains the data in comma separated and i want final output to be json data. I tried below but wanted to know is there any better way to implement this?

data.txt

1,002, name, address
2,003, name_1, address_2
3,004, name_2, address_3

I want final output like below

{
"Id": "1",
"identifier": "002",
"mye": "name",
"add": "address"
}
{
"Id": "2",
"identifier": "003",
"mye": "name_2",
"add": "address_2"
}
and so on...

here is code which i am trying

list = []

with open('data.txt') as reader:
     for line in reader:
         list.append(line.split(','))
          
print(list)

above just return the list but i need to convert json key value pair defined in above

What have you tried wrt the conversion part? Did you use a `dict`? — quamrana, Aug 10 '21 at 15:38
Are you aware of the [`json`](https://docs.python.org/3/library/json.html) module? — bicarlsen, Aug 10 '21 at 15:41
@quamrana can you please share few example. very new to Python — user1591156, Aug 10 '21 at 15:45
your `.txt` is actually a CSV file. So the question is answered here https://stackoverflow.com/questions/19697846/how-to-convert-csv-file-to-multiline-json — Ufos, Aug 10 '21 at 15:48

alfinkel24 · Accepted Answer · 2021-08-10T16:24:43.023

2

Your desired result isn't actually JSON. It's just a series of dict structures. What I think you want is a list of dictionaries. Try this:

fields = ["Id", "identifier", "mye", "add"]
my_json = []

with open('data.txt') as reader:
     for line in reader:
         vals = line.rstrip().split(',')
         my_json.append({fields[vals.index(val)]: val for val in vals})
print(my_json)

edited Aug 10 '21 at 16:24

answered Aug 10 '21 at 15:50

alfinkel24

551
5
13

thanks for example, i see each end of line appended with '\n' how to remove in your loop? – user1591156 Aug 10 '21 at 16:21
Just add an `rstrip()` between `line` and `split(',')`. Answer edited accordingly. – alfinkel24 Aug 10 '21 at 16:25

score 1 · Answer 2 · answered Aug 10 '21 at 15:49

Something like this should work:

import json

dataList = []

with open('data.txt') as reader:
    # split lines in a way that strips unnecessary whitespace and newlines
    for line in reader.read().replace(' ', '').split('\n'):
        lineData = line.split(',')
        dataList.append({
            "Id":           lineData[0],
            "identifier":   lineData[1],
            "mye":          lineData[2],
            "add":          lineData[3]
        })
out_json = json.dumps(dataList)
print(out_json)

Note that you can change this line:

out_json = json.dumps(dataList)

to

out_json = json.dumps(dataList, indent=4)

and change the indent value to format the json output.

And you can write it to a file if you want to:

open("out.json", "w+").write(out_json)

mpx · Answer 3 · 2021-08-10T16:27:58.360

0

Extension to one of the suggestion, but you can consider zip instead of list comprehension

import json
my_json = []
dict_header=["Id","identifier","mye","add"]
with open('data.txt') as fh:
    for line in fh:
        my_json.append(dict ( zip ( dict_header, line.split('\n')[0].split(',')) ))

out_file = open("test1.json", "w")
json.dump(my_json, out_file, indent = 4, sort_keys = False)
out_file.close()

This of course assuming you save the from excel to text(Tab delimnited) in excel

1   2   name    address
2   3   name_1  address_2
3   4   name_2  address_3

edited Aug 10 '21 at 16:27

answered Aug 10 '21 at 16:03

mpx

3,081
2
26
56

see edit @quamrana. This assuming OP get the `txt` from excel save as .tab delimited` – mpx Aug 10 '21 at 16:09
Why make that assumption? Why not take what the OP has supplied in 'data.txt'? – quamrana Aug 10 '21 at 16:09
OP did not share this information hence the assumption – mpx Aug 10 '21 at 16:10
In what way did the OP not share this information? Its been there from the original post. – quamrana Aug 10 '21 at 16:11

score -2 · Answer 4 · answered Aug 10 '21 at 15:49

-2

I would suggest using pandas library, it can't get any easier.

import pandas as pd

df = pd.read_csv("data.txt", header=None)

df.columns = ["Id","identifier", "mye","add"]

df.to_json("output.json")

answered Aug 10 '21 at 15:49

klegoff

51
3

Read file and convert to JSON

4 Answers4