2

I'm trying to create a database of orders. Very time program runs and user input their data - a different list is created containing this data. At the end of the program I add this data to a database. I use this currently to concatenate multiple lists and input to database in a format i need. 

orderNumber = ['123456789']
countryOfOrigin = ['United Kingdom']
sizeList = ['2', '4']
quantityList = ['10', '12']

This is the output I get:

OrderNumber        COO         Size   QTY
123456789     United Kingdom    2     10
123456789     United Kingdom    4     20

Note: number of rows is dictated by number of sizes... if other lists only have one item, the data is dublicated down to fill the rows.

This is the code used to create the DataFrame. 

vals = [orderNumber, countryOfOrigin, sizeList, quantityList]
header = ["OrderNumber", "COO", "Size", "QTY"]
d = {a: b[0] if len(b) == 1 else b for a, b in zip(header, vals)}
order = pd.DataFrame(d)

And its simply saved into a document using this:

order.to_excel('C:/Users/userName/Desktop/Pandas-Example2.xlsx', index=False, encoding='utf-8')

This all works, but if the document is not available in the location path provided, it creates one. and if its already there. The data gets replaced with new data every time program runs.

I need it to NOT REPLACE whats already there, but rather find the last populated row, skip one (to create one row gap) and populate new data below.

I tried specifying the row in the:

bottom = excelFile.tail(1)
order.to_excel('C:/Users/userName/Desktop/Pandas-Example2.xlsx',
index=False, encoding='utf-8', startrow=bottom, startcol=1)

No Luck.

babis95
  • 582
  • 6
  • 29
  • 1
    Does this answer your question? [How to write to an existing excel file without overwriting data (using pandas)?](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) – DoRemy95 Jan 16 '20 at 15:59

1 Answers1

2

I think this might help you: https://stackoverflow.com/a/47740262/10740287

By using the helper function append_df_to_excel with the parameters index=False and encoding='utf-8' you should get what you want. 

append_df_to_excel('C:/Users/userName/Desktop/Pandas-Example2.xlsx', order, index=False, encoding='utf-8)
DoRemy95
  • 614
  • 3
  • 19
  • 2
    Thank you it worked. However every time it appends data to the excel file - both the data and the column headers get appended. All i need is the data - no need for the header names. Edit: for those who want to append without the column headers, simply add ``` header=False``` after the DataFrame Object. – babis95 Jan 17 '20 at 10:17