Deleting & Adding Rows/Columns in Excel formatting

Eggplant Functional
#1

So the scenario is this. I have a client that is producing an excel file for me to process. For now I have been manually opening it, and deleting the first row (which has some title in it) so the header values are moved up into row 1. I am hoping I could edit the excel in EPF to do this for me (as well as maybe add some columns)

I know I can do something like

set cell(sheet1,"A1")to empty

but that of course doesn’t delete it so that isn’t any help

Also tried something like

delete cellRange(1) of sheet1 from data1

While this runs nothing actually happens if I log it out (thought it was worth a try)

Then there is something like

put the first record of sheet2 into test1 // not sure the word first does anything
log test1
delete record test1

but this will leave the title row (thinking its the header) and delete row 2 instead

Is there a simple way to do this I am just missing?

Also hoping I can add a new column (Status) to report the processing status of each row that can be in column A but not sure if that is possible either. I know I could write to a new file (maybe) but trying to keep it simple to work with the client

#2

Currently the only way for SenseTalk to delete a row in an Excel worksheet is when accessing it as a database and deleting a record. Feel free to file a feature request for new functions that will let you add or delete rows and columns.
In the meantime, one possible workaround that might work for you would be to copy all of the cell values from row 2 into row 1. Then you should be able to access it as a database and delete record 1.

#3

Thanks @SenseTalkDoug
My brain was finally able to come up with something today.

  • Essentially I read in the file as a workbook
  • check if A1 contains title in it if so…
  • hardcoded out my 4 new columns into A1, B1, C1, D1
  • put item 1 of cellRange(2) of sheet1 into theKeys.columnNames, to get all the columns in row 2
  • repeat though each columnName item and insert in cell(“1”, repeatIndex() + 4. So they are after my new columns
  • fun part is I then have a repeat for each row that read each cell and starting at the end (so as not to copy of existing data), shifts it over 4 columns. With 40 columns and 80 rows this does take a few minutes but best I had
  • then clear out first four columns (in same repeat) since they are new
  • then I read in the file as a database
  • check if there is a record with head names in it (row 2) and delete it

Hopefully that kind of made sense. Not the hardest code but the one thing that did catch me is at first I was reading in as workbook, moving header, reading as database, deleting the row, then going back to the workbook (new variable) did somethings and would find it would revert changes from the database when debugging. I tried reading in as new file, worksheets and tables variables but still had issues. I didnt spend much time trying to fix as simple enough to just get all you need done atworksheet level before moving to database level. Just wanted to give heads up in case anyone else runs into this

#4

That makes sense. But I think you could save a lot of time and trouble by adding your new columns at the end, after the existing columns, instead of moving everything over.
I hadn’t heard of problems using the workbook functions along with the database access approach, but I can understand how they might conflict, particularly since you are making changes to the data.

#5

Just now seeing this. Main reason I am adding at the beginning is for the client who then views the spreadsheet as a results report and they like it up front so doing my best to please them :slight_smile: