Need to map csv file to target table dynamically

Question

I have several CSV files and have their corresponding tables (which will have same columns as that of CSVs with appropriate datatype) in the database with the same name as the CSV. So, every CSV will have a table in the database.

I somehow need to map those all dynamically. Once I run the mapping, the data from all the csv files should be transferred to the corresponding tables.
I don't want to have different mappings for every CSV.

Is this possible through informatica?
Appreciate your help.

Answer 1

My understanding is you have mant CSV files with different column layouts and you need to load them into appropriate tables in the Database.

Approach 1 : If you use any RDBMS you should have have some kind of import option. Explore that route to create tables based on csv files. This is a manual task.

Approach 2: Open the csv file and write formuale using the header to generate a create tbale statement. Execute the formula result in your DB. So, you will have many tables created. Now, use informatica to read the CSV and import all the tables and load into tables.

Approach 3 : using Informatica. You need to do lot of coding to create a dynamic mapping on the fly.

Proposed Solution : mapping 1 : 1. Read the CSV file pass the header information to a java transformation 2. The java transformation should normalize and split the header column into rows. you can write them to a text file 3. Now you have all the columns in a text file. Read this text file and use SQL transformation to create the tables on the database

Mapping 2

Now, the table is available you need to read the CSV file excluding the header and load the data into the above table via SQL transformation ( insert statement) created by mapping 1

you can follow this approach for all the CSV files. I haven't tried this solution at my end but, i am sure that the above approach would work.