1

I want to import data from excel form to informatica and finally insert into DB table. the data in the excel is in key:value format(ex. name : "xyz"(in the next right cell)) how can i import that data such that the "Name" becomes the column name and "xyz" becomes the data in informatica source?

Shalabh srivastava
  • 11
  • 1

3 Answers3

0

Is there a reason the Excel file cannot be saved as a CSV first? If not, you'll an Excel connector. Then you will need to convert the rows to columns. There are a few approaches to that. Google them or search the Informatica Network.

Scott S Nelson
  • 178
  • 6
0

As i understood you want to convert {Name:value} pair in excel file to {Column:Row} in Informatica Source i have articulated input from your statement; Can you also propose your output for more clarification?

Input Name1:"Value1" Name2:"Value2" Name3:"Value3" Name3:"Value4"

Now, If you want to convert the file before importing it into informatica; there are multiple functions in Excel to perform Like(VLOOKUP,XLOOKUP) please try to explore these if it can make some use.

Please Refer below to split your values: https://www.excelfunctions.net/split-string-in-excel.html

Or there is one more way you can perform 1. Import Excel file into informatica source with no changes in single column 2. Using Expression transformation - you can split the {name:value} pair into {Column:row} with regular expression(Refer infa sample workflow on pivot from google)

It can help with Output format as well.

Thanks

Shiva
  • 40
  • 1
  • 8
0

Here is your answer:

Create first a temporary table e.g. TMP_RAW_DATA with one column. load all the values as they are into that table. now load your table from that TMP_RAW_DATA table using sql to your new table

Separate the Columns from the single raw data into multiple columns, then load them to your new table.

 SELECT 
        regexp_substr(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(value,'[A-Za-z0-9_-]+:',''), '(" ")+', '","'), '"', ''), '[^,]+', 1, 1) as COL_ONE,
        regexp_substr(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(value,'[A-Za-z0-9_-]+:',''), '(" ")+', '","'), '"', ''), '[^,]+', 1, 2) as COL_TWO,
        regexp_substr(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(value,'[A-Za-z0-9_-]+:',''), '(" ")+', '","'), '"', ''), '[^,]+', 1, 3) as COL_THREE 
 FROM  TMP_RAW_DATA

You will have two mappings

  • one for loading the raw data as it is into one column
  • two for loading from TMP_RAW_DATA table to your new table

db <> fiddle

Omari Victor Omosa
  • 2,814
  • 2
  • 24
  • 46