Inside the Read Excel Spreadsheet Utilities plug-in, there is a smart service (a

Richard over 8 years ago

Inside the Read Excel Spreadsheet Utilities plug-in, there is a smart service (and an expression) that imply some control over the header list. Specifically the Parse Excel Spreadsheet to CDT with Header List Smart Service.

The documentation is not clear on how this works. I have a CDT that contains a definition for some data I care about and a spreadsheet that contains the data I want to put into that CDT. The data is, unfortunately contained in columns that are interspersed with other columns that I don't care about.

Is there a good way to use the Header List parameter to pull the data I want?
Do I need to manually manipulate the spreadsheet to conform to the plugin?
Is there a better way?

Discussion posts and replies are publicly visible

Parents

0 sikhivahans over 8 years ago

An other option is to make use of fn!readexcelcolumn() in the 'Read Excel Spreadsheet Utilities' Shared Component(the one which we are discussing on at the moment) but the process needs to perform little bit home work prior to constructing the CDT.

Example:
fn!load(
          local!col1:fn!readexcelcolumn(4865,1,1),
          local!col2:fn!readexcelcolumn(4865,1,2),
          local!col3:fn!readexcelcolumn(4865,1,3),
          local!col4:fn!readexcelcolumn(4865,1,4),
          local!col5:fn!readexcelcolumn(4865,1,5),
          local!col6:fn!readexcelcolumn(4865,1,6),
          local!col7:fn!readexcelcolumn(4865,1,7),
          local!col8:fn!readexcelcolumn(4865,1,8),
          local!col9:fn!readexcelcolumn(4865,1,9),
          /* Perform Type Casting of the above data */
          fn!apply(
                    type!Example(
                              product_id:null,
                              quantity_ordered:_,
                              special_instructions:_,
                              fulfillment_employee:_,
                              fulfillment_manager:_,
                              warehouse_aisle:_,
                              warehouse_shelf:_,
                              order_priority:_,
                              robot_picker_id:_,
                              backlog_estimate_date:_
                    ),
                    fn!merge(
                              local!col1,local!col2,local!col3,local!col4,local!col5,local!col6,local!col7,local!col8,local!col9
                    )
          )
)

Advantage is that you don't need to worry about how headers are named but the downside is, querying each column, then type casting and mapping to a CDT finally makes the approach expensive on the whole.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 sikhivahans over 8 years ago

An other option is to make use of fn!readexcelcolumn() in the 'Read Excel Spreadsheet Utilities' Shared Component(the one which we are discussing on at the moment) but the process needs to perform little bit home work prior to constructing the CDT.

Example:
fn!load(
          local!col1:fn!readexcelcolumn(4865,1,1),
          local!col2:fn!readexcelcolumn(4865,1,2),
          local!col3:fn!readexcelcolumn(4865,1,3),
          local!col4:fn!readexcelcolumn(4865,1,4),
          local!col5:fn!readexcelcolumn(4865,1,5),
          local!col6:fn!readexcelcolumn(4865,1,6),
          local!col7:fn!readexcelcolumn(4865,1,7),
          local!col8:fn!readexcelcolumn(4865,1,8),
          local!col9:fn!readexcelcolumn(4865,1,9),
          /* Perform Type Casting of the above data */
          fn!apply(
                    type!Example(
                              product_id:null,
                              quantity_ordered:_,
                              special_instructions:_,
                              fulfillment_employee:_,
                              fulfillment_manager:_,
                              warehouse_aisle:_,
                              warehouse_shelf:_,
                              order_priority:_,
                              robot_picker_id:_,
                              backlog_estimate_date:_
                    ),
                    fn!merge(
                              local!col1,local!col2,local!col3,local!col4,local!col5,local!col6,local!col7,local!col8,local!col9
                    )
          )
)

Advantage is that you don't need to worry about how headers are named but the downside is, querying each column, then type casting and mapping to a CDT finally makes the approach expensive on the whole.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data