Creating csv files from Excel file

[azizrasul]

deanhystad, included a workbook name and got it to work. Ta.

[azizrasul]

Still can't get any csv files from the code in post #14. I get no errors.

[deanhystad]

In 14 you never called main(). If you had, you'd see there is an error calling ExcToCsv(). ExcToCsv does not have a keyword argument named nrows.

[azizrasul]

Thanks, will have a look at that tomorrow.

[Larz60+]

If you can post a sample excel file that you'd like to convert, I'll make the code work with that.
Please note that in my original code Post #11, the last thing that I stated is that the code was not tested.
None the less, it has to be close to correct.
Post an excel file if you can.

[azizrasul]

I don't know how to attach a file here. However, here is the sample data. You can use a couple of lines. Thanks.

Sheet1
Segment Country Product Discount Band Units Sold Manufacturing Price Sale Price Gross Sales Discounts Sales COGS Profit Date Month Number Month Name Year
Government Canada Carretera None 1618.5 $3.00 $20.00 $32,370.00 $- $32,370.00 $16,185.00 $16,185.00 01/01/2014 1 January 2014
Government Germany Carretera None 1321 $3.00 $20.00 $26,420.00 $- $26,420.00 $13,210.00 $13,210.00 01/01/2014 1 January 2014
Midmarket France Carretera None 2178 $3.00 $15.00 $32,670.00 $- $32,670.00 $21,780.00 $10,890.00 01/06/2014 6 June 2014
Midmarket Germany Carretera None 888 $3.00 $15.00 $13,320.00 $- $13,320.00 $8,880.00 $4,440.00 01/06/2014 6 June 2014
Midmarket Mexico Carretera None 2470 $3.00 $15.00 $37,050.00 $- $37,050.00 $24,700.00 $12,350.00 01/06/2014 6 June 2014
Government Germany Carretera None 1513 $3.00 $350.00 $529,550.00 $- $529,550.00 $393,380.00 $136,170.00 01/12/2014 12 December 2014
Midmarket Germany Montana None 921 $5.00 $15.00 $13,815.00 $- $13,815.00 $9,210.00 $4,605.00 01/03/2014 3 March 2014
Channel Partners Canada Montana None 2518 $5.00 $12.00 $30,216.00 $- $30,216.00 $7,554.00 $22,662.00 01/06/2014 6 June 2014
Government France Montana None 1899 $5.00 $20.00 $37,980.00 $- $37,980.00 $18,990.00 $18,990.00 01/06/2014 6 June 2014
Channel Partners Germany Montana None 1545 $5.00 $12.00 $18,540.00 $- $18,540.00 $4,635.00 $13,905.00 01/06/2014 6 June 2014
Midmarket Mexico Montana None 2470 $5.00 $15.00 $37,050.00 $- $37,050.00 $24,700.00 $12,350.00 01/06/2014 6 June 2014

Sheet2
Segment Country Product Discount Band Units Sold Manufacturing Price Sale Price Gross Sales Discounts Sales COGS Profit Date Month Number Month Name Year
Enterprise Canada Montana None 2665.5 $5.00 $125.00 $333,187.50 $- $333,187.50 $319,860.00 $13,327.50 01/07/2014 7 July 2014
Small Business Mexico Montana None 958 $5.00 $300.00 $287,400.00 $- $287,400.00 $239,500.00 $47,900.00 01/08/2014 8 August 2014
Government Germany Montana None 2146 $5.00 $7.00 $15,022.00 $- $15,022.00 $10,730.00 $4,292.00 01/09/2014 9 September 2014
Enterprise Canada Montana None 345 $5.00 $125.00 $43,125.00 $- $43,125.00 $41,400.00 $1,725.00 01/10/2013 10 October 2013
Midmarket United States of America Montana None 615 $5.00 $15.00 $9,225.00 $- $9,225.00 $6,150.00 $3,075.00 01/12/2014 12 December 2014
Government Canada Paseo None 292 $10.00 $20.00 $5,840.00 $- $5,840.00 $2,920.00 $2,920.00 01/02/2014 2 February 2014
Midmarket Mexico Paseo None 974 $10.00 $15.00 $14,610.00 $- $14,610.00 $9,740.00 $4,870.00 01/02/2014 2 February 2014
Channel Partners Canada Paseo None 2518 $10.00 $12.00 $30,216.00 $- $30,216.00 $7,554.00 $22,662.00 01/06/2014 6 June 2014
Government Germany Paseo None 1006 $10.00 $350.00 $352,100.00 $- $352,100.00 $261,560.00 $90,540.00 01/06/2014 6 June 2014
Channel Partners Germany Paseo None 367 $10.00 $12.00 $4,404.00 $- $4,404.00 $1,101.00 $3,303.00 01/07/2014 7 July 2014
Government Mexico Paseo None 883 $10.00 $7.00 $6,181.00 $- $6,181.00 $4,415.00 $1,766.00 01/08/2014 8 August 2014
Midmarket France Paseo None 549 $10.00 $15.00 $8,235.00 $- $8,235.00 $5,490.00 $2,745.00 01/09/2013 9 September 2013

[Larz60+]

Please show header one item per line so I can understand where one field starts and the other leaves off.
Better yet, on header and first data line separate fields using '|' for delimiter.
It's difficu;t to see where one field starts and ends without understanding the data content.

[deanhystad]

I believe this is how things should be broken out for sheet 1.

Output:
Segment|Country|Product|Discount Band|Units Sold|Manufacturing Price|Sale Price|Gross Sales|Discounts|Sales|COGS|Profit|Date|Month Number|Month Name|Year
Government|Canada|Carretera|None|1618.5|$3.00|$20.00|$32,370.00|$-|$32,370.00|$16,185.00|$16,185.00|01/01/2014|1|January|2014
Channel Partners|Canada|Montana|None|2518|$5.00|$12.00|$30,216.00|$-|$30,216.00|$7,554.00|$22,662.00|01/06/2014|6|June|2014

Some of the dollar columns are a big sketchy, but I'm not an accountant.

[azizrasul]

Sheet1
Segment|Country|Product
Enterprise|Canada|Montana
Small Business|Mexico|Montana

Sheet2
Segment|Country|Product
Government|Peru|Paseo
Channel Partners|France|Carretera

Hope that helps. Just 3 columns.

[Larz60+]

Thanks dean for format:
I only created an excel file with sheet1:
Spreadsheet:
   
code:

import pandas as pd
from pathlib import Path
import os 
  
  
class ExcelToCsv:
    def __init__(self):
        os.chdir(os.path.abspath(os.path.dirname(__file__)))
  
    def excel_to_csv(self, workbook, sheetname, csvfile, startrow=None, endrow=None, index=False):
        # Allows for selection start/end if sepecified 
        if startrow:
            skiprows = startrow
            if startrow > 1:
                skiprows = startrow-1
            nrows = endrow - startrow
            df = pd.read_excel(workbook, sheet_name=sheetname, skiprows=skiprows, nrows=nrows)
        else:
            df = pd.read_excel(workbook, sheet_name=sheetname)
        print(df)
        df.to_csv(csvfile, index=index)

def main():
    etc = ExcelToCsv()
    # You will need to adjust filenames and locations
    just1 = Path('../data/excel/JustFirstSheet.xlsx')
    csvfile = Path('./extocsv.csv')
    etc.excel_to_csv(just1, 'Sheet1', csvfile=csvfile)

if __name__ == '__main__':
    main()

results:

Output:
Segment,Country,Product,Discount Band,Units Sold Manufacturing Price,Sale Price,Gross Sales,Discounts Sales,COGS Profit,Date,Month Number,Month Name,Year,Unnamed: 13,Unnamed: 14,Unnamed: 15,Unnamed: 16,Unnamed: 17,Unnamed: 18,Unnamed: 19
Government,Canada,Carretera,None,1618.5,$3.00,$20.00,$32,370,$-,$32,370,$16,185,$16,185,01/01/2014,1,January,2014
Government,Germany,Carretera,None,1321.0,$3.00,$20.00,$26,420,$-,$26,420,$13,210,$13,210,01/01/2014,1,January,2014
Midmarket,France,Carretera,None,2178.0,$3.00,$15.00,$32,670,$-,$32,670,$21,780,$10,890,01/06/2014,6,June,2014
Midmarket,Germany,Carretera,None,888.0,$3.00,$15.00,$13,320,$-,$13,320,$8,880,$4,440,01/06/2014,6,June,2014
Midmarket,Mexico,Carretera,None,2470.0,$3.00,$15.00,$37,50,$-,$37,50,$24,700,$12,350,01/06/2014,6,June,2014
Government,Germany,Carretera,None,1513.0,$3.00,$350.00,$529,550,$-,$529,550,$393,380,$136,170,01/12/2014,12,December,2014
Midmarket,Germany,Montana,None,921.0,$5.00,$15.00,$13,815,$-,$13,815,$9,210,$4,605,01/03/2014,3,March,2014
Channel Partners,Canada,Montana,None,2518.0,$5.00,$12.00,$30,216,$-,$30,216,$7,554,$22,662,01/06/2014,6,June,2014
Government,France,Montana,None,1899.0,$5.00,$20.00,$37,980,$-,$37,980,$18,990,$18,990,01/06/2014,6,June,2014
Channel Partners,Germany,Montana,None,1545.0,$5.00,$12.00,$18,540,$-,$18,540,$4,635,$13,905,01/06/2014,6,June,2014
Midmarket,Mexico,Montana,None,2470.0,$5.00,$15.00,$37,50,$-,$37,50,$24,700,$12,350,01/06/2014,6,June,2014