Write in excel skipping columns

#1

Hi,

I’m facing this problem:
I have a loop and i need to write some values in an excel sheet. The problem is that i need to skip one excel column between iterations. For example:

Loop Iteration(1):

I write the cells A4, B4 and C4

Loop Iteration(2)

I write the cells E4,F4,G4 (it skips G4)

Is there any function/activity to do it? I’ve thought about creating an auxiliar sheet to map the letters of the columns to numbers in order to use a counter (A-1, B-2) but I would like to avoid this solution.

Many thanks in advance,

1 Like
#2

Do you mean “it skips D4”?

1 Like
#3

Are you getting the data from a data table? You want to write data to the excel file, where is the data coming from. Data table?

1 Like
#5

Yes, D4, sorry

1 Like
#6

So in each iteration you will write 3 columns with 1 column gap?

1 Like
#7

The data comes from a web app but not in a data table format.
I imagine I can use a “Read Range” activity in every loop, work the data table adding columns and finally write it again in the excel.

Is there any way to relate with a function the column with a counter?

Thanks for your quick reply

1 Like
#8

Yes, so I would like to know if there is any solution apart from reading the sheet and adding columns in the data table variable.
Thanks

1 Like
#9

So how are you getting your data into the excel application scope? Is it a get text activity? What activities are you using to get the data from the web-app

1 Like
#10

You could use a Write Range Activity to write a datatable in excel that has empty column where you don’t want to write anything. Depending on how you get the data you can use a Build Datatable Activity to set up the format. Also you should uncheck Include Headers Property to not write the Column Headers.

1 Like
#11

Assuming
1/ you know the iteration number and
2/ in each iteration you need to write 3 columns and
3/ skip one column after each iteration

Expected result:
iterationNo = 0
columns = A, B, C
iterationNo = 1
columns = E, F, G
iterationNo = 2
columns = I, J, K
…

You could use the following to get column labels for each iteration

col1=if((cint((iterationNo*4+0) ) \ 26)=0, "", chr((cint((iterationNo*4+0) ) \ 26)+64) ) & chr(cint((iterationNo*4+0) mod 26)+65)
col2=if((cint((iterationNo*4+1) ) \ 26)=0, "", chr((cint((iterationNo*4+1) ) \ 26)+64) ) & chr(cint((iterationNo*4+1) mod 26)+65)
col3=if((cint((iterationNo*4+2) ) \ 26)=0, "", chr((cint((iterationNo*4+2) ) \ 26)+64) ) & chr(cint((iterationNo*4+2) mod 26)+65)

Cheers

#12

yes, I’m using a get text activity

#13

There are several ways to do this. One very easy one if you know there are no carriage returns inside your cells would be to convert the datatable to a string, then process the string and parse back into a datatable before writing to Excel.

You would begin by using the “Output Datatable” activity on your datatable. This will give you a string that looks something like

DTString = “A1val,B1val,C1valcarriage returnA2val,B2val,C2val…” and so on.

Then you can use use an assign activity to compute

newDTString = DTString.Replace(vbCr, “, ,”)

so that newDTString will look like “A1val,B1val,C1val, ,A2val,B2val,C2val…” and so on.

Then use the “Generate Datatable” activity with CSV parsing to convert newDTString back to a datatable and just write the new datatable using the “Write Range” activity. Alternatively, you could just write newDTString to a CSV directly, depending on what else you need to do to the file.

EDIT: had to correct formatting, the text editor on here does not support double commas

#14

I’m trying to put something together for you, but I’m not sure how you are getting the data, and what data type variable it’s stored in. Could you possibly give us all a screen shot of what you are doing so far?

I pulled up a get Text activity But I’m wondering how you are using it. Are you Getting text from the whole page? Or is your get Text activity in a loop of some sort?