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
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
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.
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.
Depending upon your functional needs and resource availability you can take one of the below mentioned approaches to solve the issue1. 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.