Exanding an array into another file.

Hi I have a text file that I need use as a osascript to format some Excel files. My commands are working between the osascript and Excel but what I would like to do is format the text file with an array.

Is there a way to expand the array variables into another text file while still retaining the original formatting?
I cant run the below code in the shell until the array has been expanded

source //Osascript_ForExcel_Shapes/Kernel/filelist_array
for $i in "${filelist_array[@]}"
        
osascript <<EOD
tell application "Microsoft Excel"
    activate
    set theWorkbookFile to "Macintosh HD:Osascript_ForExcel_Shapes:Working:"$i".xlsx"s
end tell
EOD

Below is the source filelist_array

declare -a filelist_array  
filelist_array=(
"R18-310805_Market_Summer_Customer_Campaign_QR_0328"
"R18_223497_Summer_Insiders_Q_0314"
)
 

I hope that I have explained myself clearly. What I am essentially triyng to do is format a proper command to run as an osascript.
Thank you very much for your help.

If I understand you, it's almost as easy as adding >filename to the end of your loop.

for i in "${filelist_array[@]}"
do
cat <<EOD
tell application "Microsoft Excel"
    activate
    set theWorkbookFile to "Macintosh HD:Osascript_ForExcel_Shapes:Working:"$i".xlsx"s
end tell
EOD
done > outputfile

Though I question why all your data is stored as arrays in the first place. Flatfiles would be a whole lot easier. Then you could just do

while read FILENAME
do
...
done < inputfile > outputfile

without the fraught and tedious loading-into-array part.

Hi thank you very much! that worked, but I was wondering if there is a better way to go about this?
You suggested to use a flat file. I'm not sure I understand, would that be to already perform the substitution of the variables with something like sed?
The osascript is used save the Excel file as a tab delimited file which I will use in another script.
The array comes form a list of files that are submitted through a hot folder.
Directly below is the script I use to format the array and move the files from one folder to another to be worked on.
Is there a way to call the osascript from the script I use to format the array, and write out my osascript file with the proper file names (your suggestion worked fine I just wondered if there is a more direct way).

Or is there a way to run the osascript in such a way that the shell can parse the variables in the array and pass it to the osascript?

Thank you very much for your help!!

while true
 do
    cp /Osascript_ForExcel_Shapes/Kernel/Standing_filelist_array /Osascript_ForExcel_Shapes/Kernel/filelist_array
    ls /Osascript_ForExcel_Shapes/ExcelThumbs >> /Osascript_ForExcel_Shapes/Kernel/filelist_array
    sed -i '' -e s'/^/\"/g' /Osascript_ForExcel_Shapes/Kernel/filelist_array
    sed -i '' -e s'/\"declare/\declare/g' /Osascript_ForExcel_Shapes/Kernel/filelist_array
    sed -i '' -e s'/\"filelist/\filelist/g' /Osascript_ForExcel_Shapes/Kernel/filelist_array
    sed -i '' -e s'/\.xlsx/\"/g' /Osascript_ForExcel_Shapes/Kernel/filelist_array
    awk '1; END {print ")"}' /Osascript_ForExcel_Shapes/Kernel/filelist_array > tmp && mv tmp /Osascript_ForExcel_Shapes/Kernel/filelist_array
    source //Osascript_ForExcel_Shapes/Kernel/filelist_array
        for i in "${filelist_array[@]}"
            do
            mv /Osascript_ForExcel_Shapes/ExcelThumbs/"$i".xlsx /Osascript_ForExcel_Shapes/Working/                
done
  

Below is the full osascript to save as tab delimited "FILENAME" is name of the original Excel files.

osascript <<EOD
 tell application "Microsoft Excel"
    activate
    set theWorkbookFile to "Macintosh HD:Osascript_ForExcel_Shapes:Working:FILENAME.xlsx"
    open theWorkbookFile #open the xls file
    set theWorksheetname to name of worksheet 1 of active workbook
    set theWorksheet to worksheet 1 of active workbook
        
    tell application "Microsoft Excel" to set display alerts to false
    set targetFolder to ("Macintosh HD:Osascript_ForExcel_Shapes:Working:")
    save the active workbook as text Mac file format in ((targetFolder as text) & "FILENAME.txt") with overwrite
    close the active workbook without saving    
end tell
EOD

What substitution of what variables?

A flat file is a file like this:

data1
data2
data3
data4
data5

Which can be used without transforming it into an array like

while read LINE
do
...
done < inputfile

The proper way would be to write the code you want in applescript, but I don't know applescript.

You have taken the long way around by transforming a file into an array with six invocations of sed however! That is unnecessary.

cp /Osascript_ForExcel_Shapes/Kernel/Standing_filelist_array /Osascript_ForExcel_Shapes/Kernel/filelist_array
ls /Osascript_ForExcel_Shapes/ExcelThumbs >> /Osascript_ForExcel_Shapes/Kernel/filelist_array
while read i
do
        mv /Osascript_ForExcel_Shapes/ExcelThumbs/"$i".xlsx /Osascript_ForExcel_Shapes/Working/
done < /Osascript_ForExcel_Shapes/Kernel/filelist_array

I have left out your outer 'while true' loop since I'm not certain what that's for. I don't think you actually want an infinite loop over the same data, do you?

Using Hypercard to kludge Microsoft Excel into working automatically is rather the long way around too. Do you have Perl? Can you install Spreadsheet::WriteExcel?

#!/usr/bin/perl

use Spreadsheet::WriteExcel;

my ($in,$out)=(shift,shift); # commandline arguments

(defined($in) && defined($out)) or die "Usage:  flattoxls input out.xls";

open(FH,"<".$in) or die "Cannot open file: $!\n";
my $book = Spreadsheet::WriteExcel->new($out);
my $sheet = $book->add_worksheet();
my ($row,$col) = (0,0);
while (<FH>){
        chomp;                  # Remove newline character
        @list = split /\t/,$_;  # Split on tabs
        # Loop over row and add, cell by cell
        foreach my $c (@list){ $sheet->write($row, $col++, $c); }
        $row++; $col=0; # Next row, column 0
}

close(FH);      # Close input file
$book->close(); # Finish writing excel file
exit(0);        # Return success

Then you can do

perl flattoxls.pl inputfile output.xls

Hi, yes I have perl Its the better way to go, and I will give it a try. Thank you again for your help.

When this working my troubles are not over, applescript for excel was really not my first choice. All this works end result is to insert some thumbnail pictures on the same row as a given sku number plus provide a link to a web page in the original excel file. The files are supplied in excell 2016. Anything would have been easier than working with excel applescript, Visual basic for 2016 has been all but eliminated in excel for mac 2016, and grep like functions to pull the file names out of the excel file do not exist in applescript. The best I can do is try to format a final osascript using sed and awk and then run that.

Thank you again for your advice and your help!!

Now that you specify excel input -- not just XLS but XLSX -- that changes things quite a lot.

It would be good to see sample input and output data.

Hi thank you for your help I have gotten a little further with this script, I probably should have explained the entire scope of the script so that you might have had a better understanding of what I need to do, rather than just the part that I was having trouble with.

The task I'm trying to accomplish is to insert a number of thumbnails and a web address link on the same row that a sku # appears in a Excel 2016 document. The sku number or a fairly close approximation of it appears in the name of the web page on our web server and in the thumbnail file names I will use to insert into the excel doc.
So far thanks to your help I have been able to write out a csv file from the excel doc and form this into a find command that gives me the proper file name for the web page and thumbnails for each sku item.
This is the point where I am little out of my depth and could use a suggestion of what is the best way to accomplish what I need to do next.

I have the the row number and the filename of the matching sku saved in a file I also have a osascript to place the thumbnails and weblink

What I need to do is insert the $ROW_NUMBER and $SKU_NAME into the osascript where they need to be and save the file off with the values expanded into there proper names
Directly below is an example of the file with the $ROW_NUMBER and proper file name of the $SKU_NAME variables, the "tLINE" copy is just a placeholder that I use to make it easier to manipulate the file if I need to, the number at the front of the line is the actual row number

2tLINE 488906_001_Winter_NY_Produce_End_Cap_Bunker_ATL_Agri-Growers_36x19.5_E_F
3tLINE 488906_002_Winter_NY_Produce_End_Cap_Bunker_ATL_Agri-Growers_36x19.5_E_F
4tLINE 488906_003_Winter_NY_Produce_End_Cap_Bunker_ATL_Scotia-Gold_36x19.5_E_F

Below is the osascript I am using

osascript <<EOD
tell application "Microsoft Excel"
    activate    
    make new hyperlink of cell "B$ROW-NUMBER" at active sheet with properties {address:"http://switchauto.tigroup.ca/SCL_Preflight_Report_Page/$SKUNAME_report.html"}
    set theLeft to left position of cell "A$ROW-NUMBER"
    set theTop to top of cell "A$ROW-NUMBER"
    set currentWorkbook to workbook 1
    tell currentWorkbook
        tell worksheet 1
            set pictureShape to make new shape at the beginning with properties {height:"72", width:"72", left position:0, top:theTop, placement:placement move}
            user picture pictureShape picture file "Macintosh HD:Library:WebServer:Documents:FilesForExcel:Jpeg:$SKU-NAME_02.jpeg"
        end tell
EOD
 

I was hoping to use an array although I'm not sure if this is the most direct way to go about this.

The part that is giving me trouble today is that if I use an array I do not know If i can have more than one value for "i", I have two values the ROW-NUMBER and the SKUNAME that need to populate during the same iteration, is this even possible?

If you know of another way to go about this I would be happy to give it a try.

Apple script doesn't have any grep like functions although you can call a shell script that will do that for you, I was thinking that if I need to use a shell script to accomplish what I need, that I would do as much in the shell as I could, seeing as I am not very comfortable with applescript to start with.
Thank you for your time and patience.

If your input data is processed in sequence, a text file is the perfect form. And the shell is great in handling text files. (An array is good for random access.)
A simple "echo" demo:

# read two space-separated columns from stdin into two variables
while read ROW_NUMBER SKU_NAME
do
  echo "ROW_NUMBER=$ROW_NUMBER and SKU_NAME=$SKU_NAME"
done < arrayfile
# the stdin of the while-do-done block is from arrayfile

The read reads one line from a text file. The while will terminate the loop if the read was unsucessful i.e. at the end of the input.
Now with your application:

while read ROW_NUMBER SKU_NAME
do
    osascript <<EOD
    tell application "Microsoft Excel"
        activate    
        make new hyperlink of cell "B${ROW_NUMBER}" at active sheet with properties {address:"http://switchauto.tigroup.ca/SCL_Preflight_Report_Page/$SKUNAME_report.html"}
        set theLeft to left position of cell "A${ROW_NUMBER}"
        set theTop to top of cell "A${ROW_NUMBER}"
        set currentWorkbook to workbook 1
        tell currentWorkbook
            tell worksheet 1
                set pictureShape to make new shape at the beginning with properties {height:"72", width:"72", left position:0, top:theTop, placement:placement move}
                user picture pictureShape picture file "Macintosh HD:Library:WebServer:Documents:FilesForExcel:Jpeg:${SKU_NAME}_02.jpeg"
            end tell
EOD
# the EOD must be at the very left
done < arrayfile

The braces in ${SKU_NAME} encapsulate the variable name, so ${SKU_NAME}_02 is the variable's value and the string _02 appended.

Thanks very much!!

I'll give that a try and let you know how it works!
Thanks again!!!

Hi I have these scripts working fine now I set up a hot folder that has the script attached and it is inserting the thumbnails and the HTML links beautifully

Thanks for your help!!!