CSV Import with Non-Default Separator

Panorama X Beta 10.2
1

I am affected by the new default separator setting for CSV files. (It depends on the System decimal separator settings; so it avoids the comma and defaults to the semicolon here in Germany.)

For most of the files I am using frequently, the new setting is working flawlessly, but today I noticed a problem.

The application “AppShelf” is not localised, and it indeed is using comma separated values (with quoted data).

While in Pan X 10.1 this line of code is working perfectly

importtext fileload(info("desktopfolder")+"Apps.csv"), "Existingdata", "Replace"

the importtext statement in Pan X 10.2 imports the data correctly when I add the new option to use the comma as separator, but the imported data remains quoted.

So it seems that importtext using a non-default separator does not eliminate string delimiters during the import.

2

So are you saying that if you switch to US settings, and just use the default comma settings, then it works?

3

Yes, it does.

4

So the data you are trying to import looks something like this?

"alpha","beta","gamma"
"red","green","blue"
"gold","silver","bronze"
5

Correct. The app “AppShelf” collects data about applications like serial numbers, purchase date, vendor etc., and the exported data look like this

"6.0","$ 14.99","flyingmeat.com/acorn/","flyingmeat.com","2017-07-10 23:28:48 +0000","2027-07-08 23:28:48 +0000"
6

Ok, I think I know what the problem might be. If you explicitly specify the separator character, you must also explicitly specify the quote character. I think if you add the quote option to your code it will work.

importtext fileload("yourfile.txt"),"separator",",","quote",{"}

This is sort of hinted at in the documentation, but it should probably be clearer.

Please let me know if this does actually solve the problem for you.

7

Yes, this solved the problem!

8

Yes, I think this code sample should be included in the documentation.

I had looked for that option, but the text in the documentation seemed to say only that I could disable quote handling, but not how to specify the quote character and that this specification is necessary when the default field separator is not used.

9

I went to change the documentation, and decided that the way it currently works is wrong. To fix this, I’ve changed the importtext statement so that it now assumes that if the separator is a comma or semicolon, the quote character should be " unless you have explicitly specified otherwise. So now I think now it will consistently work the way you would expect. I also slightly tweaked the documentation.

The good news is you have an immediate fix for your issue, and it will stay fixed even when you get the new version, since if you explicitly specify the quote character, Panorama will always honor that.

10

Yes, that’s better!