Can I cleanse or change data before exporting it to CSV?

Product Forums Katalon Recorder
1

I am trying to automate a repetitive task that involves taking the project title, hours for role A, and hours for role B from multiple project reports and exporting that to CSV.

The problem I am encountering is that for larger projects, some have hours that are more than 1000, e.g. 1,234.56, and because of the comma, the test case fails when it tries to append it to csv with a " [error] The amount of data miss match the amount of column in data file" error." message.

Is there a way to remove or change the comma to a different character before appending it to CSV? Would any of these options be doable through recorder as I am not dev savvy? When I looked into exporting the code, it seems like they don’t accept the appendToCSV command either, which makes sense, so I have no idea what to do.

I feel foolish already considering how long it took me to figure out that the problem was the comma, and at this point I’ve probably spent more time trying to figure this out than it would have taken me to just check the projects manually for the next few months, but I’m in too deep to give up.

1 Like
2

Hi,

You can set up whether comma, tab or semicolon here Manage test data | Katalon Docs before exporting CSV

2 Likes
3

There is a String function called, replace, that I use (note I use Katalon Studio, not Katalon Recorder, so see if you can get it to work in KR).

yourHours = ...
yourHours = yourHours.replace(",", "")

If you have more than 1 comma in the hours, then you should use replaceAll.

Edit: note that you can have a CSV with comma values but you have to surround the value with double quotes, like: “1,234.56”

1 Like
4

Thank you both for the information! Looks like recorder doesn’t do what I would need in this case, so I’ll have to try with Studio. So far I have not been able to figure out how to export this data to csv in Studio, but at least it’s a new problem to sort out.

5

Here is a link to writing to a text file for Katalon Studio.

How to create a Katalon method (Keyword) for writing to a text file with Java - Product Forums / Tips & Tricks - Katalon Community

A CSV file could be thought of as a text file with comma separators, so you might be like:

hoursRoleA = ...
hoursRoleA = hoursRoleA.replace(",", "")
hoursRoleB = ...
hoursRoleB = hoursRoleA.replace(",", "")
data.WriteToFile('projectTitle, hoursRoleA, hoursRoleB')

or maybe even try to write the hours direct to the file by using the double quotes,

data.WriteToFile('projectTitle, "${hoursRoleA}", "${hoursRoleB}"')
6

I wondered if Katalon Recorder’ appendToCSV command is really fragile against a comma in data string.

I checked the source code of Katalon Recorder https://github.com/katalon-studio/katalon-recorder/blob/master/panel/js/background/playback/service/actions/play/execute-appendToCSV.js. I tried to find if it is really fragile against the comma character contained is a data 1,234.56.

At the line#60:

  let values = value.split(',');
  let result = appendDataFile(fileName, values);

Ahhh, yes. Unfortunately, appendToCSV command is fragile against the comma in data.

I think Katalon Recorder need to be more robust.

2 Likes
7

Very fragile indeed! I tried having it append the data in double quotes, and it works for numbers without a comma, but it still throws the exact same error when there is a comma in between those quotes.

8

I hope you guys realise that, Katalon Recorder is opensourced.
Feel free to contribute with PR’s… or just rise feature requests and wait for a resolution.

9

Hi,

I’ve encountered this issue with the (lack of) handling of commas in strings (with CSV files), so I usually use the following command (as suggested by others, but this one is suitable for Katalon Recorder):

storeEval | Result = “1,000,000.00”; Result.replaceAll(“,”,“”); | NewResult

You can then store this new value with appendToCSV. I agree that it would be good if Katalon Recorder was more robust in this sense (as then you wouldn’t need to ‘process’ strings in this way).

Maybe the following advice may assist the Katalon devs. with fixing this issue:
jquery - How to deal with Commas in CSV using Javascript - Stack Overflow (cc: @albert.vu)

10

Hi,

I will inform our develop team with this request. Lots of things are in queue :rofl:

Anyway, @jeff.fogel does the advice of @guy.mason work for you? If yes, please help mark the solution? Thank you.

1 Like
11

This was very helpful, thank you!

Marked as solved.

2 Likes