As a learning exercise in Client 8.2.3 on Windows 7 I'm basically trying to eliminate what I'm doing in this question: http://www.visualcron.co....aspx?g=posts&t=7050  and do it directly in Excel (add a value to the end of every row). If I'm successful I can eliminate having to call a different job in order to logically perform a loop with in a loop. It's a good way to learn the capabilities of working with Excel too for future jobs we may get requests for.

To that end I have so far set up a task to list the .xlsx's in a folder, a loop to iterate through those and a "set col" task that for each iteration will add a value to the last column of each row. Now for the questions. :-)

1) In the Target tab of set cell, Is there a variable to represent the last cell + 1? It seems to need a specific cell reference, which in this case I can give as I am working with a set number of columns, but for flexibility I would rather not have to state a specific cell.

2) In the Source tab, I need to logically add my value once for each row. While experimenting, I was successful by hardcoding it repeatedly with a separator (after hardcoding a starting cell as referenced in the first question) and setting the line break dropdown to that separator but in reality I need to loop somehow for the number of rows in the sheet. I see there is task for get rows which I have added as a task and assigned to a job variable but am not sure how to use that in the source section in order to add my value that many times. Is there a way to apply the source value to all rows somehow programmatically in the source tab (using the row count value maybe)? Or to tell VisualCron the source value should be applied to all rows?

I searched the forum and tutorials but didn't see much for working with Excel. If these questions are referenced somewhere I'm not aware of, by all means please point me there! Thank you for any assistance.
Gary

1) In the Target tab of set cell, Is there a variable to represent the last cell + 1? It seems to need a specific cell reference, which in this case I can give as I am working with a set number of columns, but for flexibility I would rather not have to state a specific cell.

No, we do not store such value. In this case it sounds like you want to store the last cell you set in the previous operation. It is unlikely that we will store this in the future - it is simply too specific.

2) In the Source tab, I need to logically add my value once for each row. While experimenting, I was successful by hardcoding it repeatedly with a separator (after hardcoding a starting cell as referenced in the first question) and setting the line break dropdown to that separator but in reality I need to loop somehow for the number of rows in the sheet. I see there is task for get rows which I have added as a task and assigned to a job variable but am not sure how to use that in the source section in order to add my value that many times. Is there a way to apply the source value to all rows somehow programmatically in the source tab (using the row count value maybe)? Or to tell VisualCron the source value should be applied to all rows?

What exactly do you mean by "logically add value". Do you need to evaluate the value? Generally it is hard to follow exactly what you want to do. Maybe it would be easier if you explain with Excel screenshots what you want to do.

---

I will attempt to clarify. Perhaps I am using incorrect terminology since I am a new user. I need to pre-process a file before loading into the database by getting the date from the filename and adding it to the end of every row. I was doing this after converting to csv but was exploring doing this in the Excel file using VisualCron's set col functionality as an exercise really to see what the Excel capabilities are and to get used to using VisualCron.

The files are a fixed number of columns that look something like this:

I need them to end up looking like this, but the number of rows are different every run:

I need to add the date to column D, rows 1 through 4. It seems in VisualCron/Set col on the target tab, I need to state a specific cell to start with. Since in this case I know the set number of columns I could start with "D1" but I would rather keep it generic by stating logically somehow "the last used cell in row1 + 1" as my starting cell.
Then, on the source tab I would have to put: 1/5/2017,1/5/2017,1/5/2017,1/5/2017 and set the line break to "comma". Since the number of rows is unknown though I cannot do this. I need to get the number of rows (get rows?) and loop to build a string maybe and use that in the source tab (depending on the number of rows this could get huge)? I really just need to repeat my value for the number of rows. I looked through the various functions available but there was no way to loop to build a string (too dangerous I suspect!).
Any ideas? Like I said, I am exploring to see what is possible.
Thanks for your time,
Gary

The short answer is that you won't be able to do it with the excel task. You mentioned that the data in the file is going to be imported to a database. Have you considered just passing the date to the proc (or whatever method you us to import), and just insert the date directly into the table?

I have an existing way to do it, I was just trying to do it in VisualCron via the set col functionality.

Thanks