/
Import CSV File (1.2.0 and higher)

Import CSV File (1.2.0 and higher)

Steps:

  1. Read the CSV file from the defined URI:

    <from id="from1" uri="file:{{cars-fromUri}}"/>

     

  2. Split the records to batches to increase efficiency of the loading. In this particular example, there is grouping into batches of 5000 records. 
    There is an assumption that the records are separated by the new line character.

    <split id="by_rows_grouped"> <tokenize group="5000" token="\n"/>

     

  3. Unmarshal each batch using the defined header with parameters depending on formatting of the particular CSV. 

    <to id="to1" uri="pfx-csv:unmarshal?header=sku,label,price,date,year&skipHeaderRecord=true&delimiter=,"eCharacter=~"/>

     

  4. The unmarshaled (POJO) batch is loaded into Pricefx by pfx-api:loaddata, using a custom mapper. In this particular case:

    <to id="to2" uri="pfx-api:loaddata?mapper=customerMap&objectType=DM&dsUniqueName=Product"/>

     

  5. Flush the loaded data from the Data Feed to a Data Source on completion. 

  6. Flush the completed data by the property: integration.events.event-to-route-mapping.PADATALOAD_COMPLETED=direct:eventPADataLoadCompleted

  7. Truncate DMF.Product. 

    <to id="e_to1" uri="pfx-api:truncate?targetName=DMF.Product"/>

     

 

 

Example of a Camel route intended for CSV import using pfx-api:

routes/carRoutes.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:pfx="http://www.pricefx.eu/schema/pfx" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <routeContext id="carsRoutes" xmlns="http://camel.apache.org/schema/spring"> <route id="example_csv_load_to_datasource"> <from id="from1" uri="file:{{cars-fromUri}}"/> <split id="by_rows_grouped"> <tokenize group="5000" token="\n"/> <to id="to1" uri="pfx-csv:unmarshal?header=sku,label,price,date,year&skipHeaderRecord=true&delimiter=,&quoteCharacter=~"/> <to id="to2" uri="pfx-api:loaddata?mapper=customerMap&objectType=DM&dsUniqueName=Product"/> </split> <onCompletion id="onComp" onCompleteOnly="true"> <to id="to3" uri="pfx-api:flush?dataFeedName=DMF.Product&dataSourceName=DMDS.Product"/> </onCompletion> </route> </routeContext> </beans>

Example of the imported file:

Example definition of a mapper used in the above Camel route: 

mappers-filters.xml
<?xml version="1.0" encoding="UTF-8"?> <beans:beans xmlns="http://www.pricefx.eu/schema/pfx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <loadMapper id="customerMap"> <body in="sku" out="ProductId" /> <groovy expression="(body.sku + '/' + body.label)" out="ProductName" /> </loadMapper> </beans:beans>

 

Sample route for treating events:

eventRoutes.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:pfx="http://www.pricefx.eu/schema/pfx" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <bean class="net.pricefx.integration.command.cfs.Calculate" id="callProblemScorecalculation"> <property name="cfsLabel" value="ProblemScoreCFS" /> </bean> <routeContext id="eventsRoutes" xmlns="http://camel.apache.org/schema/spring"> <route id="eventPADataLoadCompleted"> <from id="e_from1" uri="direct:eventPADataLoadCompleted" /> <setHeader headerName="type" id="type"> <simple>${body[data][0][type]}</simple> </setHeader> <setHeader headerName="targetName" id="targetName"> <simple>${body[data][0][targetName]}</simple> </setHeader> <setHeader headerName="status" id="status"> <simple>${body[data][0][status]}</simple> </setHeader> <choice id="e_choice"> <when id="e_when1"> <simple>${headers.type} == "DS_FLUSH" && ${headers.targetName} == "DMDS.Product" && ${headers.status} == "READY"</simple> <to id="e_to1" uri="pfx-api:truncate?targetName=DMF.Product" /> </when> </choice> </route> </routeContext> </beans>

 

Example of using both the above Camel resources in a Camel root context:

camel-context.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:pfx="http://www.pricefx.eu/schema/pfx" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <import resource="mappers-filters.xml" /> <import resource="eventRoutes.xml" /> <import resource="routes/carRoutes.xml" /> <camelContext id="camelContextExample" trace="false" xmlns="http://camel.apache.org/schema/spring"> <contextScan /> <routeContextRef ref="eventsRoutes" /> <routeContextRef ref="carsRoutes" /> </camelContext> </beans>

 

Data Source import table:

DS import tabel definition
{ "typedId":"1974.DMDS", "version":6, "uniqueName":"Product", "sourceName":"DMDS.Product", "label":"Product", "type":"DATASOURCE", "query":null, "valid":true, "view":false, "locked":false, "deployed":true, "auxiliary":false, "formulaName":null, "messages":null, "fields":[ { "expression":null, "calculated":false, "name":"ProductId", "type":"TEXT", "format":null, "label":"Product Id", "rank":3, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"sku", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":true, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductName", "type":"TEXT", "format":null, "label":"Product Name", "rank":4, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"label", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"UOM", "type":"TEXT", "format":null, "label":"UOM", "rank":5, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"unitOfMeasure", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductCurrency", "type":"TEXT", "format":null, "label":"Product Currency", "rank":6, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"currency", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"formulaName", "type":"TEXT", "format":null, "label":"FormulaName", "rank":7, "persisted":true, "dimension":false, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"formulaName", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"BusinessUnit", "type":"TEXT", "format":null, "label":"Business Unit", "rank":8, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute1", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductGroup", "type":"TEXT", "format":null, "label":"Product Group", "rank":9, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute2", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductClass", "type":"TEXT", "format":null, "label":"Product Class", "rank":10, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute3", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductLifeCycle", "type":"TEXT", "format":null, "label":"Product Life Cycle", "rank":11, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute4", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"DiscountGroup", "type":"TEXT", "format":null, "label":"Discount Group", "rank":12, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute5", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"FocusProduct", "type":"TEXT", "format":null, "label":"Focus Product", "rank":13, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute6", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductType", "type":"TEXT", "format":null, "label":"Product Type", "rank":14, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute7", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"EANCode", "type":"TEXT", "format":null, "label":"EAN-Code", "rank":15, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute8", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"LaunchDate", "type":"DATE", "format":null, "label":"Launch Date", "rank":16, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":true, "source":"DMDS.Product", "sourceField":"attribute9", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"Weighting", "type":"NUMBER", "format":null, "label":"Weight in g", "rank":17, "persisted":true, "dimension":false, "numeric":true, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute10", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":"FIXED", "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"isDeleted", "type":"BOOLEAN", "format":null, "label":"IsDeleted", "rank":1, "persisted":true, "dimension":false, "numeric":false, "deleted":false, "deployed":true, "auxiliary":true, "queryable":false, "time":false, "source":"DMDS.Product", "sourceField":null, "owningFC":"[System]", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"lastUpdateDate", "type":"DATETIME", "format":null, "label":"LastUpdateDate", "rank":2, "persisted":true, "dimension":false, "numeric":false, "deleted":false, "deployed":true, "auxiliary":true, "queryable":true, "time":true, "source":"DMDS.Product", "sourceField":null, "owningFC":"[System]", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"ProductAttribute", "type":"TEXT", "format":null, "label":"Product Attribute", "rank":18, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute11", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null }, { "expression":null, "calculated":false, "name":"PackagingType", "type":"TEXT", "format":null, "label":"Packaging Type", "rank":19, "persisted":true, "dimension":true, "numeric":false, "deleted":false, "deployed":true, "auxiliary":false, "queryable":true, "time":false, "source":"DMDS.Product", "sourceField":"attribute12", "owningFC":"Product", "formulaElement":null, "messages":null, "measureType":null, "key":false, "functionalType":null } ], "persistedObjectClass":"net.pricefx.domain.Product", "sourceTypeCode":null, "sourceId":null, "sourceLabel":null }



IntegrationManager version 6.0.0