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.

Read multiple tables & write the data into multiple csv files

Basheer
7 - Meteor

Hello Everyone,

 

I've 10 SAP HANA tables  now i want to create a workflow which would read tables one by one and write the data into csv files. Also the output files names should be aligned with the table names.

 

for example let say I've Table_1, Table_2, Table_3 and Table_4 in source. Now I want to create a workflow so that after executing the workflow I should have 4 files i.e Table_1.csv, Table_2.csv, Table_3.csv and Table_4.csv with data.

 

Any lead would be highly appreciated. Please guide me with each single steps to design it.

 

Thanks!

6 REPLIES 6
Qiu
20 - Arcturus
20 - Arcturus

@Basheer 
This  SAP HANA table is like Excel file?

Can you upload one sample file?

If its Excel files, they are plenty sample in this community to read and export them.

Basheer
7 - Meteor

@Qiu

 

No these are tables only. need to load these tables as files.

apathetichell
18 - Pollux

Have you read through this:

https://help.alteryx.com/current/designer/sap-hana

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SAP-HANA-connections-to-alteryx/td-p/1...

 

assuming you are using input data to take in your content - you can take the filename i.e. Table_1 as a field and use that in your Output Data.

 

quick clarification - I think @Qiu was asking if the process of reading in multiple SAP Hana process would be any different than the process for reading in multiple excel files - i.e. they are both fairly standard table formatted data sources and if you had tried that process.

 

The solution to this question walks you through how to use a wildcard in an input data tool. This answer was provided specifically for an excel question - but it may work for SAP HANA as well.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/load-multiple-excel-files-from-a-folde...

 

note- you want output filename as field to be marked "yes"

 

ArtApa
Alteryx
Alteryx

Hi @Basheer - You will need to use a batch macro. I attached a sample workflow with a batch macro for your convenience.

 

In this example, excel file may contain multiple sheets. The number of sheets can be dynamic, the sheet names can be dynamic, and the schemas can all all different. The workflow creates a file for every sheet. The filename is output_[sheetname].

 

To adjust the sample for your use case, you need to do the following:

 

1) Read the metadata with the table names from your SAP HANA or manually populate the list. 

2) Adjust the macro. You'd need to use Dynamic Input to read each table one by one.

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Input/ta-p/249... 

 

Hope that helps. 

Basheer
7 - Meteor

Thanks @ArtApa 

 

this will be helpful.

 

2) Adjust the macro. You'd need to use Dynamic Input to read each table one by one. can you give one example step by step process that would be helpful.

ArtApa
Alteryx
Alteryx

@Basheer - The batch macro will read your table names one by one. You just need to learn how it works here: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros 

If you don't have time to watch all lessons, just focus on Types of Macros and Creating a Batch Macro.

 

In my previous post I provided a link to a Tool Mastery article. This article contains a link to the following: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynam... 

 

In other words, in the macro you need to configure your Dynamic Input so that it would read one of the required table. Then you will manually replace a dynamic part of your SQL query to something crazy, for example "XXXXX". Then you will configure your Dynamic Input to Modify SQL query and replace "XXXXX" with a String from the field (Control Parameter). 

 

I cannot share an SQL working example with you, as I cannot package a Data Base. The information in the trainings, articles and the provided workflow example with the macro should be sufficient for you to achieve the desired outcome. If you will still struggle, please log a VSC call and one of my colleagues will assist you: https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc 

Labels