How to Parse CSV with different delimeter and non english charaters

Community Show & Tell
1

The retool CSV file parser works perfectly fine in most of the cases. However if you run across a special CSV with semicolon instead of comma as the delimiter you might run into some parsing issues. The solution is to use Papa Parse.
{{Papa.parse(atob(filepicker1.file.data),{header:true, delimiter: ";"}).data}}

Now, the atob function might not work if you have some non English characters in your file, like French. You would need to convert your file back to UTF-8 characters. For this you could use the below function instead of the basic atob:

    function b64DecodeUnicode(str) {
       // Going backwards: from bytestream, to percent-encoding, to original string.
       return decodeURIComponent(atob(str).split('').map(function(c) {
       return '%' + ('00' + c.charCodeAt(0).toString(16)).slice(-2);
    }).join(''));
    }
6 Likes
2

Welcome to the community @aveek-mukherjee, and thanks for posting this!!

1 Like
3

Nicely done @aveek-mukherjee! :clap:

1 Like
4

Hello, The header: true don't work as expect.

image
image1669×163 32.8 KB

As showed above, the return still not contain header.
Is sth wrong or Papa version in Retool is too old?

5

Hey @AnsonHwang! That option should be compatible with the version of PapaParse being used by Retool:

Would you mind sharing a snippet of the CSV that you're uploading to Retool with sensitive information redacted from there?

Edit: Ah, I see that you made progress here :slightly_smiling_face:

6

Hello, thanks. Here is my csv. I found it work but still with error.

image
image850×728 29.4 KB

ass.csv (20 Bytes)

7

Ahh I see, thanks for the screenshot and the csv! It looks like the file ends on a newline:

Can you try passing skipEmptyLines as an additional config parameter (docs)?

8

Thanks it works.