Expand my Community achievements bar.

SOLVED

Unable to convert multiple columns to upper case while loading data as CSV

Avatar

Level 2

Hi Experts,

I need one help in loading the CSV file through "Map CSV to XDM Schema" workflow. I have defined an XDM Schema which is extending "XDM Individual Profile". My file contains basic customer information (first_name, last_name, email_id, birth_date, address information). I want to convert the First Name and Last Name to UPPERCASE, email_id to LOWERCASE. I had added Calculated fields to use "upper" function for each of teh 3 fileds: first_name, last_name, email_id. When I finished the workflow, I could see that only first_name is getting converted to UPPERCASE, but the other 2 fields are being set as NULL in data lake and in Profile UI.

 

Can you please help me to know what is wrong? 

 

Thanks in advance. 

1 Accepted Solution

Avatar

Correct answer by
Level 1

Hi @bitunsen 

 

Looks like data for country_code is missing and hence mobile column will be loaded as null. Besides, can you confirm your mapping looks something similar to the attached?

 

https://ibb.co/QX6T6PW

 

Keshav

View solution in original post

4 Replies

Avatar

Employee

The Data Prep step will result in NULL if it is unable to transform the data. Since UPPER is a very simple operation, I am suspecting that the incoming data for other 2 attributes is something like a date or number that cannot be case converted. Can you please double check or post the sample input data (if not sensitive)?

 

Keshav

Avatar

Level 2

Hi Keshav,

 

At first, thank you very much for responding. I have a simple CSV file having 1 record in it.

The data I am using is a mock data. See below:

 

id,first_name,last_name,email,birthDate,gender,addr_line_one,city,state,country,country_code,mobile
CRM001,Steve,Miller,steve.miller@aol.com,1986-05-18,Male,123 Main St,Midland,Texas,United States,254-467-2383

 

I wanted to convert the values of first_name and last_name columns to Upper case while loading the data using "Map CSV to XDM Schema" workflow. If I am adding 1 calculated field for any of the first_name and last_name columns, then the conversion is happening, but when I am adding 2 calculated fields (one for first_name and one for last_name), only one field is being converted to upper case and other one is set to NULL. 

Avatar

Correct answer by
Level 1

Hi @bitunsen 

 

Looks like data for country_code is missing and hence mobile column will be loaded as null. Besides, can you confirm your mapping looks something similar to the attached?

 

https://ibb.co/QX6T6PW

 

Keshav

Avatar

Level 2

Hi @keshav-vadrevu 

You are correct, while copying the data in the post, I missed to put the data in those to columns. The mapping is exactly same as except for the below 3 fields. I have mapped:

  • addr_line_one to homeAddress.street1
  • city to homeAddress.city
  • country_code to homeAddress.countryCode
  • state to homeAddress.state

The mapping of the 2 calculated fields are exactly same.

 

Please let me know if you need further information. Thank you again for helping me out here.