How can I add columns in an excel file?

Eggplant Functional
1

I’m trying to automate a process we do on excel that includes adding columns to the excel file and paste data from an external txt file.
I couldn’t find information about this on the Eggplant documentation.

Thanks!

2

Hallo rbedoy,
I had same task to solve and found 2 ways.

  1. Open EXCEL file as workbook and shift in each row each cell (i) to (i+1). It works, but it is very slow.
  2. Build a short powershell command and call it via the shell of ep.
    New-Object -ComObject excel.application | %{ $.workbooks.open(!filename!) | %{ $.worksheets.item(!nnn!) |%{$cs=$.columns(!F:F!); $cs.Insert}; $.save(); $_.close()}}
    Replace variables !..! acc. your needs. Take care about escape characters when calling the powershell via cmd.exe.
3

Hey Both,

Here is the documentation for Excel.
Keep in mind that we can work in two ways with excel files:

  1. Work with Excel with worksheets, workbooks, cells etc
  2. Excel as database: which provides you with all the commands that are available for databases. As the excel sheet is treated as one internally in Eggplant. Adding records docu

Here the example from our docs for adding a record to an excel file via database connection

Set myDB to {type:"Excel", file:"~/Desktop/ClubData.xlsx", Writable:yes} -- Define the database connection, whether Excel or ODBC.
put table "Members" of myDB into MemberTable -- Pull the reference information for the table you need to modify
put {NameFirst:"Heidi", NameLast:"Mullers", StartDate:"2019-01-02"} into MemberToAdd -- This could also be done recursively using Iterators, if adding numerous members to the database table
add record MemberToAdd to table "Members" of myDB -- Add the record to the table in the database
Put the Record of MemberTable where "NameFirst" is "Heidi" into member3-- Retrieve the record you just added and store it in a variable
if member3 is a record then log "Success!" -- Verify that the record is an actual record with an active database connection

Cheers,
Karsten

4

Karsten, thank you for your help, the problem of the example you shared is for adding a record so basically you are adding a row to the excel file, but I need to add a column, do you know how?

5

You can try this:

//This script will read from a spreadsheet and then write info to it
put "Data.xlsx" into filename
put "Sheet1" into WorkingTab

//Open the Excel file as an excel workbook and get the number of columns
set CurrentTab to Workbook(ResourcePath(filename)).Worksheet(WorkingTab)
Put the number of items in item 1 of CurrentTab.cellRange(1) into numberOfColumns
add 1 to numberOfColumns

//Add New Columns
put "New Column 1" into CurrentTab.Cell(1,numberOfColumns)
add 1 to numberOfColumns

put "New Column 2" into CurrentTab.Cell(1,numberOfColumns)
add 1 to numberOfColumns