Write to Excel - Reading CSV with Pandas & Openpyxl - Python

Question

I have a csv report thats saved to a specific folder daily. What I would really like to do is to automate & open that csv from the folder every morning, extract what I need, and have it append to my spreadsheet. Baby steps first, I'm a newbie.

I seem to be reading the csv just fine. (Here is a link to it) https://drive.google.com/open?id=16leX2pBV1-kXUtufOn0w00Uq9FrddZl2

I'm trying to append the dataframes to different sheets in my excel workbook and I'm screwing up when I start to use openpyxl.

import pandas as pd
import openpyxl

file = ('artb.csv')
df = pd.read_csv(file, sep=',',header=0,usecols=[0,1,2,3])

df = pd.DataFrame(list)
print (df)

output:

       Date Balance Fwd        Charges        Credits
0  3/4/2018    1,905.00  69,799,377.71  73,405,427.70
1  3/4/2018           0   1,221,028.15     596,488.63

df1=[df.iloc[0]]
df2=[df.iloc[1]]
print (df1)

output:

Date                3/4/2018
Balance Fwd         1,905.00
Charges        69,799,377.71
Credits        73,405,427.70
Name: 0, dtype: object


wb = openpyxl.Workbook()
wb = openpyxl.load_workbook(filename = 'summary.xlsx')

sheets = wb.sheetnames

# Stu Tab
ws = wb[sheets[1]]
ws2 = wb[sheets[2]]

This is where it messes up, I've tried at least a dozen different things.

    #append df1 to Stu Sheet
        for row in df1:
            ws.append(row)

   #append df2 to Sp Sheet
            for row in df2:
                ws.append(row)

    wb.save("summary.xlsx")

It's not giving me errors now, but its not writing anything :(

I'm going to keep messing with it. I'd appreciate any advice. Thank you!

Your output doesn't look like it comes from a csv to me, at least, not with a comma delimiter. What does the file look like? — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Feb 23, 2018 at 21:50
Doesnt look like a real csv to me either. It also comes as a txt file, I just thought it would be easier using the csv. — Jil W
– Jil W, Commented Feb 23, 2018 at 22:18
Sounds like the CSV file is irregular. Would be great if you could provide a sample file. — Charlie Clark
– Charlie Clark, Commented Feb 28, 2018 at 12:36
The structure of the first three lines doesn't match the structure of lines 5 onwards so you cannot read this file with a CSV library. Please check the provenance of the file and what it should look like. I suspect you probably want to skip the first four lines. — Charlie Clark
– Charlie Clark, Commented Feb 28, 2018 at 14:22

lf2225 · Accepted Answer · 2018-02-23 21:43:06Z

1

have you tried using pandas library to do this? there's a function called .to_csv

answered Feb 23, 2018 at 21:43

lf2225

4054 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jil W Over a year ago

I haven't. I wasn't sure what the best thing was to use. I'll go check it out.

lf2225 Over a year ago

stackoverflow.com/questions/16923281/…

Collectives™ on Stack Overflow

Write to Excel - Reading CSV with Pandas & Openpyxl - Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related