CODEWITHSUNDEEP
pythonjsonexcelpandasyaml
Sanjana SG
Sanjana SG

Reputation: 13

Convert Excel to Yaml syntax in Python

I want to convert my data that is in this form to YAML Syntax (preferably without using pandas or need to install new libraries)

Sample data in excel :

users | name | uid | shell

user1 | nino | 8759 | /bin/ksh

user2 | vivo | 9650 | /bin/sh

Desired output format : YAML Syntax output

Upvotes: 1

Views: 11923

Answers (2)

Abhishek Prusty
Abhishek Prusty

Reputation: 864

You can do it using file operations. Since you are keen on *"preferably without using pandas or need to install new libraries

Assumption : The "|" symbol is to indicate columns and is not a delimiter or separater

Step 1

Save the excel file as CSV

Then run the code

Code

# STEP 1  : Save your excel file as CSV

ctr = 0
excel_filename = "Book1.csv"
yaml_filename = excel_filename.replace('csv', 'yaml')
users = {}

with open(excel_filename, "r") as excel_csv:
    for line in excel_csv:
        if ctr == 0:
            ctr+=1  # Skip the coumn header
        else:
            # save the csv as a dictionary
            user,name,uid,shell = line.replace(' ','').strip().split(',')
            users[user] = {'name': name, 'uid': uid, 'shell': shell}



with open(yaml_filename, "w+") as yf :
    yf.write("users: \n")
    for u in users:
        yf.write(f"  {u} : \n")
        for k,v in users[u].items():
            yf.write(f"    {k} : {v}\n")

Output

users: 
  user1 : 
    name : nino
    uid : 8759
    shell : /bin/ksh
  user2 : 
    name : vivo
    uid : 9650
    shell : /bin/sh

Upvotes: 1

NYC Coder
NYC Coder

Reputation: 7594

You can do this, in your case you would just do pd.read_excel instead of pd.read_csv:

df = pd.read_csv('test.csv', sep='|')
df['user_col'] = 'users'
data = df.groupby('user_col')[['users', 'name','uid','shell']].apply(lambda x: x.set_index('users').to_dict(orient='index')).to_dict()
with open('newtree.yaml', "w") as f:
    yaml.dump(data, f)

Yaml file looks like this:

users:
  user1:
    name: nino
    shell: /bin/ksh
    uid: 8759
  user2:
    name: vivo
    shell: /bin/sh
    uid: 9650

Upvotes: 0

Related Questions