1

I have input fixed width txt file as source.

test file sample below

column_1
12ABC3455
13XYZ5678

How to build dynamic column pattern to produce derived columns.

column Name : empId -> substring(column_1,1,2)

derive Column setting

I can hardcode the empid in & substring(column_1,1,2) in expression.

but i need to make it dynamic with the JSON input to derive dynamic derived columns with column pattern.

Below sample JSON input parameter.

My input JSON formatted parameter

[
   {
      "colname": "empid",
      "startpos": 1,
      "length": 2
   },
   {
      "colname": "empname",
      "startpos": 3,
      "length": 3
   },
   {
      "colname": "empSal",
      "startpos": 6,
      "length": 4
   }
]

help me to build the column pattern with the json input

mju
  • 25
  • 1
  • 4
  • what's the problem？Did you get any error? – Leon Yue Dec 17 '20 at 02:10
  • Which part would you like to make dynamic? The options in substring can be turned into parameters. Instead of referencing direct column names, you can use pattern matching and reference ordinal column position instead with the built-in system var 'position'. – Mark Kromer MSFT Dec 17 '20 at 02:53
  • Hi Mark & Leon, Thanks for helping me. I have updated the post with my sample input JSON parameter. Is is possible to create column pattern to create the substring dynamically. If yes please show me some sample. Thanks in advance. – mju Dec 18 '20 at 06:50
  • I found similar problem resolved with databricks. i want to do this ADF Dataflow.https://stackoverflow.com/questions/53817746/read-fixed-width-file-using-schema-from-json-file-in-pyspark – mju Dec 18 '20 at 07:03
  • @mju I'm afraid to tell you that it's impossible to to that in Data Factory actives or Data Flow with json parameter. – Leon Yue Dec 21 '20 at 03:02

1 Answers1

0

I tested many times and can't achieve that.

Just per my experience, I'm afraid to tell you that it's impossible to to that in Data Factory actives or Data Flow with json parameter.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Thanks for your efforts. I will go with the data bricks option to resolve this issue. – mju Dec 22 '20 at 04:58