How to output data at specific row and column in excel

[anuar]

Hi, newbie here,

I want to save my records at 2nd row and 2nd column in excel (similar as below). Can anyone help me how to do this in alteryx?

TQ

[NicoleJohnson · Accepted solution]

Possible with a few specific conditions... After you select the sheet you want to save it to (Sheet1, for example), you can modify the configuration of your output tool to include the cell references for the range you want to paste your data in. But the trick is that (I believe) you have to know the exact range where your data will be posted. 

So one way to do it could be to determine which columns your data would be pasted in based on the number of fields included + your start column (so B:I per the example below), and then determine the number of rows based on a count of records (let's say 2 records for this example, plus a header row, so it would be the start row + 1 + 2). You could then create a field that indicates the "Sheet name" by concatenating the sheet, a $ symbol, and the row/column reference based on your data. 

So in this example, the field would be created with a formula that creates something like C:\\Documents\FileName.xlsx|||'Sheet1$B2:I4', and then you can choose the option in your Output file tool to replace the entire field path with this field (un-check the box to include field in output). The data will then be output to whatever range you specified.

Not the most straightforward of solutions, and I would say it's likely someone else out there has a much faster/more streamlined way to do it :) But for an unknown output range in Excel, this would potentially do the trick? Let us know if you want to tinker with this idea (or another one) some more! 

Cheers,

NJ

[anuar]

Hi NJ,

Solved. Thank you so much for your help NJ. Appreciate it.

[jschroeder]

This is great, I was able to do this for XLSX output but how might you do the same thing for a .TXT file?  

Best,

John S

[jwalk]

It's almost a year later, but this is exactly what I needed. Thanks!

[lovi93]

Do you know how you would write to a specific range for a .CSV file?

Thanks!

[mat_ste_cb]

Thanks @NicoleJohnson,

I have this error: "Encountered existing data beyond the specified range". My sheet looks like this (output range is filled red -J6:J21):

Is there a way to upload data to specyfied range in sheet that contains some data?

Many thanks in advance!

[NicoleJohnson]

@mat_ste_cb - I think the issue here might be that you need to include the header row in your specified range? Alteryx has no problem writing to a range that has data, but if you don't incorporate the header row, then it will try to write your data starting with the header in J6, which will extend the data down to J22, which is outside your specified range.

Let me know if that does the trick :)

Cheers,

NJ

[htalteryx]

Can you please attach a workflow?  I'm having trouble replicating.

[amitbanwar]

Hello,

It runs perfectly when I run it in designer, however on server, I get an error:

'Sheet1$A4:D4' is an invalid Sheet/Range specification for Excel

Kindly assist with this issue. 

Kr,

Amit