1

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!

Jil W
  • 25
  • 1
  • 7
  • 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 Feb 23 '18 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 Feb 23 '18 at 22:18
  • If it's not a csv, then csv-parsers are not going to work. – juanpa.arrivillaga Feb 23 '18 at 22:19
  • Sounds like the CSV file is irregular. Would be great if you could provide a sample file. – Charlie Clark Feb 28 '18 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 Feb 28 '18 at 14:22
  • Thanks yall. Now I have an actual csv. I am reading it fine, I'm just having trouble appending the rows to an existing spreadsheet. I'm going to update the code and my csv file. – Jil W Mar 05 '18 at 20:44

1 Answers1

1

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

lf2225
  • 405
  • 4
  • 6