We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

General Discussions

Discuss a wide range of topics! Questions about the Alteryx Platform should be directed to the appropriate Product discussion forum.
SOLVED

Excel Columns - concatenate rows of each column

dindrani
6 - Meteoroid

I have an Excel workbook with 6 worksheets. I need to read each worksheet and perform the same process on the data and produce 6 Excel worksheets as output.

I have a few columns: Market, Plant, Email etc.  The number of rows in each column vary.  I would like to have a comma separated list of the market names in single quotes so I can use them in a SQL SELECT query IN clause.

So, if there are 3 markets in column A, cells A2, A3 and A4 = Market1, Market2 and Market3 respectively I want the value 'Market1','Market2','Market3' so I can use it in a SQL query.

And same for Plant in column B; there could be 10 plants listed in it and so on.

 

Market       Plant      Email

----------------------------------
Market1     Plant1    Email1
Market2     Plant2    Email2
Market3     Plant3   Email3
                  Plant4   Email4
                  Plant5   Email5
                  Plant6
                  Plant7
                  Plant8
                  Plant9
                  Plant10

 

Please help.

Regards,

Indrani 

 

2 REPLIES 2
DavidP
16 - Nebula
16 - Nebula

Hi @dindrani 

 

There's 2 parts to this: art one is to load all the sheets and part 2 is to create the content for your SQL statement. I guess part 3 would be to write the output to an excel file, but let's focus on 1 and 2. If all the sheets have the same column structure, you can use a dynamic input tool as shown here. If not you'll have to use a batch macro - let me know if that's the case.

 

Sample workflow attached. Please have a look and let me know if you need help to adjust it to your use case.

 

DavidP_0-1634126953064.png

 

dindrani
6 - Meteoroid

Thank you very much David.  Awesome.  I will try to apply this to my workflow and reach out to you if I am stuck.  APPRECIATE YOUR HELP VERY MUCH.  Thank you again.