Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

import CSV datafile to Existing AEP Dataset

Avatar

Level 2

I have a CSV datafile which needs to be loaded into Existing AEP Dataset mapped to a schema, which needs to be performed via API 

 

Data ingestion Questions:

while providing the Filepath as one of the Param in the URL, do we need to pass CSV file Schema structure or always JSON format ?

While uploading the File in body section is this needs to be  completely different from file used in FILE_PATH variable shown below or can we use same file in both section?

how to map a CSV file  attributes to existing Dataset Schema via API?

sandeep_crs_0-1652188251241.png

 

Scenario's tested  as of now:

   1. Providing json file absolute path  in filePath variable and uploading json Datafile from body able to import data into existing DATASET in AEP

   2. Providing json file absolute path  in filePath variable and uploading CSV Datafile, able to trigger the batch but ended with ERROR.

sandeep_crs_1-1652188499534.png

  3. Providing CSV file absolute path  in filePath variable and uploading CSV Datafile, able to trigger the batch but ended with ERROR.

sandeep_crs_3-1652188600805.png

 

 

Dataset Question:

Can any one help how to create DATASET via API, as shown below what is the expectation in body section.

 

sandeep_crs_4-1652188934243.png

 

 

 

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hello @sandeep_crs ,

 

To import a CSV file you need to follow the following steps as mentioned in the documentations. 

Docmentation URL to ingest CSV:  Batch Ingestion API Guide | Adobe Experience Platform

 

1. Create a data set if required. But in your case you want to load in the existing data set then you can skip this one. 

 

2. Create a batch: When you creating a batch you need to define CSV as input format. Then you will get the batch id, which you need to next call when loading the file.

Parvesh_Parmar_0-1652272217209.png

 

3. Load the files: Once you get the batch id, you need to use API to load the file.

Parvesh_Parmar_1-1652272352456.png

4. File which you are going to load must be  matches the XDM schema of the data.

Batch Ingestion API Guide | Adobe Experience Platform

There is example for the profile csv file. 

experience-platform-etl-reference/CRM_profiles.csv at master · adobe/experience-platform-etl-referen...

 

As far as i see, column name and data type in csv should be same of schema. 

For e.g in your data set schema definition there is two field F_Name and L_Name. 

In your CSV file you can put F_Name and L_Name column header. Please try it, I think it might work.

 

I see you are using post man and in adobe documentation they are using a CURL. Normally it will work in post man also just pass the correct attribute as passing in CURL request.  

 

Thanks.

Parvesh.

 

 

 

 

View solution in original post

1 Reply

Avatar

Correct answer by
Level 4

Hello @sandeep_crs ,

 

To import a CSV file you need to follow the following steps as mentioned in the documentations. 

Docmentation URL to ingest CSV:  Batch Ingestion API Guide | Adobe Experience Platform

 

1. Create a data set if required. But in your case you want to load in the existing data set then you can skip this one. 

 

2. Create a batch: When you creating a batch you need to define CSV as input format. Then you will get the batch id, which you need to next call when loading the file.

Parvesh_Parmar_0-1652272217209.png

 

3. Load the files: Once you get the batch id, you need to use API to load the file.

Parvesh_Parmar_1-1652272352456.png

4. File which you are going to load must be  matches the XDM schema of the data.

Batch Ingestion API Guide | Adobe Experience Platform

There is example for the profile csv file. 

experience-platform-etl-reference/CRM_profiles.csv at master · adobe/experience-platform-etl-referen...

 

As far as i see, column name and data type in csv should be same of schema. 

For e.g in your data set schema definition there is two field F_Name and L_Name. 

In your CSV file you can put F_Name and L_Name column header. Please try it, I think it might work.

 

I see you are using post man and in adobe documentation they are using a CURL. Normally it will work in post man also just pass the correct attribute as passing in CURL request.  

 

Thanks.

Parvesh.

 

 

 

 

Avatar

Level 2

hi Parvesh,

 

Thank you for the solution, it worked but have further clarification

 

I was able to ingest CSV file by creating New Dataset with simple plan Schema 

Dataset via AEP: Successful

sandeep_crs_4-1652343333064.png

Schema in CSV file used:

{
"title","description","type"
}

sandeep_crs_0-1652342268017.png

 

since Dataset failed to create via API for the same  ended up with errors 

sandeep_crs_6-1652343531998.png

 

also I was not able to ingest CSV for existing Dataset tagged to Schema with struct type having multilevel datatype, in below Case "_lifetech"

sandeep_crs_1-1652342364704.png

Sample CSV data :

sandeep_crs_0-1652343664983.png

 

 

Is there any way we can ingest Data from CSV datafile to above Dataset having "_lifetech" kind of attribute?

 

Thanks,

Sandeep

Avatar

Level 4

Hello Sandeep, 

 

For e.g. If you want to insert data with JSON format then your request data will be in following format: 

{
"_lifetech": {
"application": "car loan",
"brand": "BMW",
"category": "car",
"productDesc": "electric car",
"productName": "X3"


}
}

 

Now you want to insert the same with CSV. Normally it would be like below: 

_lifetech/application_lifetech/brand_lifetech/category_lifetech/productDesc_lifetech/productName
car loanBMWcarelectric carX3

Please try it. 

 

For the data set: 

1. I would first suggest you first try to check your data schema definition and verify everything is OK with schema. 

API to read schema : Create a Dataset using APIs | Adobe Experience Platform

 

Parvesh_Parmar_0-1652345597638.png

 

2. Create a data set: Once you verified your schema then use the following API to create data set. 

Send all the parameters. I saw parameters in your dataset requests  are not same as in documentation as below.

 

Create a Dataset using APIs | Adobe Experience Platform

 

Parvesh_Parmar_1-1652345799755.png

 

Thanks. 

Parvesh.

 

 

 

Avatar

Level 2

hi Parvesh,

 

I tried with above method, load was not able to identify the attributes 

sandeep_crs_0-1652350163852.png

 

 

 

For Dataset creation,

I didn't captured the headers  as working via post and all parameter is set under environment section , I captured only BODY part of it where we need to pass json  text 

sandeep_crs_1-1652350385641.png

 

 

 

 

Thanks,

Sandeep 

Avatar

Level 4

Hello Sandeep, 

 

I saw now you are trying to import with JSON request. 

Did you create a new batch ID for JSON data and for correct data set?

 

If you have defined the file format as CSV in the the batch. 

Then  take the following example of as reference to create a CVS, you might need to change it according to your data schema definition.

Parvesh_Parmar_0-1652361949138.png

 

 

 

For data set request:  

 

I saw you are sending scheme definition in the request. But it is not required. 

1. First you need to create schema. Schema you can create with API or with UI like from CSV. As I see, you create a schema with CSV then you do not need to create it again. 

 

2.  Send request for data set: 

In this request, you need to pass the id and contentType as shown below. 

 

Parvesh_Parmar_1-1652362352651.png

 

Thanks.

Parvesh.

 

Avatar

Level 2

hi Parvesh,

 

Did followed the process mentioned in Data ingestion link Batch Ingestion API Guide | Adobe Experience Platform  , my assumption is the way column headers present in  CSV file its not getting mapped to NESTED datatype of DATASET

 

Step1: New batch for CSV processing

sandeep_crs_3-1652364429955.png

 

 

 

STEP2: Create New CSV file:  ProductLookupReference.csv.

sandeep_crs_6-1652364623462.png

 

sandeep_crs_2-1652364297448.png

When compared with Schema for dataset which we are loading it matches 

sandeep_crs_8-1652365198576.png

 

STEP3 : Upload file with New BATCHID created mapped to DATASET used to create BATCHID

sandeep_crs_5-1652364530567.png

 

Batch load  failed with below error :

sandeep_crs_9-1652365497173.png

 

sandeep_crs_7-1652364781453.png

 

 

 

Thanks,

Sandeep 

Avatar

Level 4

Hello Sandeep, 

 

Can you change the header like below and try again? 

Parvesh_Parmar_0-1652366669540.png

If still it would not work then try to check with Adobe support or Adobe partner solution help.

 

Thanks. 

Parvesh.

Avatar

Level 2

As of now AEP doesn't support CSV  ingestion into Nested Dataset.

We can only ingest simple headers with no Multilevel