Clarity

CA-PPM Version:14.3

I receive the HR feed with .xls format but I manually convert into 4 colons (::::) delimiter file and remove the double quotes from the file.

Is there anyway I can automate through GEL script?

Thanks in advance for your help

-Venkitta

Not 100% sure what you mean, but GEL can replace text in variables (so can replace "special" characters and so on in strings that you are using to build up XML).

Some examples ; 

Remove single quote in GEL 

Need to include specail character in gel update to sql 

GEL Script XOG - Handling Special Characters 

Hi david

Let me explain 

after receive the excel file, I save as tab delimiter Text file:  I replace tab with 4 colon delimiter and remove the double quotes from the file

the whole process is manual and I like to handle to through gel script 

So perhaps these discussions might help;

GEL Scripting: Reading xlsx document 

Reading xls data with GEL column-by-column, instead of row-by-row 

Import values from Excel into GEL 

Question:  Can GEL script be used to import from an XLS (Excel) file? 

Read data from Excel 

Example of a gel script used to read an excel file 

(the answer is "yes" of course, but the detail is very specific to your XLS and what you are doing with it)

If you are asking if you can just read the xls file from GEL, take a look at this post.

Import values from Excel into GEL 

V/r,

Gene

Sorry Dave,

I didn't see that you already include the same link.

OK, how about saving it as a CSV file and parsing that.

Gel script -Reading csv file and passing the o/p  to sql script 

The double quotes would be seen as multiple line text blocks into single field.

I you really need to get all the double quotes removed -- save it as csv then read the file into a string.  Remove all the double quote and then send it to be parsed.

V/r,

Gene

Hello ,

Depending upon your functional needs and resource availability you can take one of the below mentioned approaches to solve the issue
1. create a macro in excel and XOG data directly from excel to CA PPM.
2. Convert the xls to csv, write a gel to read csv data and XOG into clarity

3. Create a custom JAVA code using the POI libraries to perform the complete activity of reading, sanitizing, transforming and loading the data to CA PPM.
If you need help in terms of macros, java code or gel script then please let me know I can email you some material for reference.

Hope this helps.