Read multiple tables & write the data into multiple csv files

Question

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!

ArtApa · Answer

@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-Dynamic-Input-Tool/ta-p/17431

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

Basheer · Answer

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 · Answer

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/24946

Hope that helps.

Sheets_to_Files_AA.yxmd