How to create a hierarchical csv file?

Question

I have following N number of invoice data in Excel and I want to create CSV of that file so that it can be imported whenever needed...so how can I archive this?

Here is a screenshot:

same as https://stackoverflow.com/questions/9884353/xls-to-csv-converter — MrE, Jun 28 '17 at 05:24
@Haranadh i have account_invoice, customer,date and under that i have these line of name,product_id,quantity... — Alpesh Valaki, Jun 28 '17 at 05:25
@ADVALAKI, you can also save all the invoices to csv files. so that you can use the csv files using `csv` lib in python. — Hara, Jun 28 '17 at 05:25
@ADVALAKI, please see my answer below. That approach would be helpful for you. — Hara, Jun 28 '17 at 05:32

score 1 · Answer 1 · answered Jan 14 '18 at 23:34

Assuming you have a Folder "excel" full of Excel Files within your Project-Directory and you also have another folder "csv" where you intend to put your generated CSV Files, you could pretty much easily batch-convert all the Excel Files in the "excel" Directory into "csv" using Pandas.

It will be assumed that you already have Pandas installed on your System. Otherwise, you could do that via: pip install pandas. The fairly commented Snippet below illustrates the Process:

# IMPORT DATAFRAME FROM PANDAS AS WELL AS PANDAS ITSELF
from pandas import DataFrame
import pandas as pd
import os


# OUR GOAL IS:::
# LOOP THROUGH THE FOLDER: excelDir.....
# AT EACH ITERATION IN THE LOOP, CHECK IF THE CURRENT FILE IS AN EXCEL FILE,
# IF IT IS, SIMPLY CONVERT IT TO CSV AND SAVE IT:

for fileName in os.listdir(excelDir):
    #DO WE HAVE AN EXCEL FILE?
    if fileName.endswith(".xls") or fileName.endswith(".xlsx"):
        #IF WE DO; THEN WE DO THE CONVERSION USING PANDAS...
        targetXLFile    = os.path.join(excelDir,    fileName)
        targetCSVFile   = os.path.join(csvDir,      fileName) + ".csv"

        # NOW, WE READ "IN" THE EXCEL FILE
        dFrame          = pd.read_excel(targetXLFile)

        # ONCE WE DONE READING, WE CAN SIMPLY SAVE THE DATA TO CSV
        pd.DataFrame.to_csv(dFrame, path_or_buf=targetCSVFile)

Hope this does the Trick for you.....
Cheers and Good-Luck.

score 0 · Answer 2 · answered Jun 28 '17 at 05:30

Instead of putting total output into one csv, you could go with following steps.

Convert your excel content to csv files or csv-objects.
Each object will be tagged with invoice id and save into dictionary.
your dictionary data structure could be like {'invoice-id': csv-object, 'invoice-id2': csv-object2, ...}
write custom function which can reads your csv-object, and gives you name,product-id, qty, etc...

Hope this helps.

How to create a hierarchical csv file?

Here is a screenshot:

2 Answers2