Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to output data at specific row and column in excel

anuar
5 - Atom

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?

 

Capture.JPG

 

TQ

11 REPLIES 11
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
5 - Atom

Hi NJ,

 

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

jschroeder
7 - Meteor

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
5 - Atom

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

lovi93
5 - Atom

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

 

Thanks!

mat_ste_cb
8 - Asteroid

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):

screen.PNG

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

 

Many thanks in advance!

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

@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
8 - Asteroid

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

amitbanwar
7 - Meteor

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

 

 

Labels