Introduction
I am creating a CSV file based on a query by using Sharperlight Publisher and Scheduler.
The following two Sharperlight objects are prepared.
- A Published Query
- A Scheduled Task
The Published Query collects data which is output to a csv file.
The Scheduled Task does executing the query above, and output the query result to a csv file.
I configure the task to output all values are enclosed in double quotes like this.
However, I also would like to amend the output so that all columns on the header row, and the first column on the data rows don't have doble-quote around the value.
Published Query
I create a simple query fetching data from SAP Business One.
The published Query is called as QiitaSample.PublisherSaveAs.
Then its query definition is like this.
It gets the first 5 rows from Sales A/R table in SAP Business One database.
Its preview is like this. It's simple isn't it.
Scheduled Task
I create a Scheduled Task for outputing the data generated by the query above to a csv file.
The task is called as QiitaSample.PublisherSaveAs.
There are two actions need to be defined. The first one is for executing the query and save the result as a csv file, and the second one is for editing the contents in the csv file.
Action1: PublisherSaveAs
Action2: JavaScript
I use a JavaScript code to edit the contents of the csv file.
RemoveHeaderQuotes(); // Call the function
//Start
function RemoveHeaderQuotes() {
try {
//File Path - Input here
var filePath = "C:\\QiitaSampleTemp\\TestCSV.csv";
console.log(filePath);
if(!lib_sys.io.file.Exists(filePath)){
console.error("error", "file doesn't exist");
}
//Read file
var readLines = lib_sys.io.file.ReadAllText(filePath);
var lines = readLines.split('\n'); // Split into lines
lines[0] = lines[0].replace(/"/g,''); // Remove double-quote from the first line. i.e. header row
// Remove double-quote from the first column of each data row
for(var l=1; l<lines.length; l++){
var cols = lines[l].split(",");
cols[0] = cols[0].replace(/"/g,'');
lines[l] = cols.join(",");
}
// Put all together back
var updatedLines = lines.join('\n');
console.log(updatedLines);
//Writebck to file
lib_sys.io.file.WriteAllText(filePath,updatedLines);
}catch(err){
console.error("Error: " + err);
}
}
Verification
Firstly, I disable the JavaScript action and execute the action PublisherSaveAs only.
I can see this result and they all are enclosed in double quotes.
Then make the JavaScript action enabled and execute the task.
The result becomes what I would like to see.
Conclusion
Because Sharplight is so flexible, I found I could combine different techniques to find different solutions.