Read multiple tables & write the data into multiple csv files with file name as table name

[swapnilbera]

Hello Everyone,

I've 50 oracle tables in same schema and 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!

[Qiu]

@swapnilbera 
First, we need to include a clause like below in your query to have the Database Table Name

---

SELECT
    ID,
    'table1' as TableName
FROM
    table1

---

Then we can use the attached workflow to export your data to CSV for each table.

[echuong1]

You'd need to create a batch macro to read in each table. I'd start off by creating a list of the tables you want to bring in, then use it as the parameter for the macro. It will process file by file, so you can have your output in the macro as well. You can import the table name with the actual data import, and then use it in the output file name.

See these links for information on the macro:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900

[swapnilbera]

hello @echuong1,

Thanks for your response. Actually I'm new to Alteryx. Can you please guide me step by step development what you just explained or can you attach the macro which you are referring to.

Thanks!

[swapnilbera]

Thanks @Qiu 

Thanks for the detailed answer. One quick question. If we have 60 tables then do we need to write 60 SELECt statement for all 60 tables? Is there any other way to select 60 tables dynamically along with the table names?

Thanks!

[Qiu]

@swapnilbera 
There might something we can do if the tables are having the same schema.

If not, that will be one by one case.

[swapnilbera]

@Qiu 

Tables are different in structure. Is there anyway to design a batch macro which would execute each table and write the data into different different .csv files?

[Basheer]

@Qiu

Exactly same scenario am try to implement. can you plz explain step by step that would be great.