Best way to disallow a comma to be entered into a text box?

Measures have to be set on Parser Write AND Read

Escaping the “offending” character is the bst way I can think of…

If in doubt, you can also see what others are doing (OpenOffice among others) …

it gives ideas!

As long as quotes are paired, that’s fine. You just have to make sure the number of quotes is an even number before you save (show a “syntax error” :grin:).
But, well, I guess if a comma is inside a paired quote, it’s also a valid comma, right?
(never used CSV myself).

Seriously recommend looking at this from Norman Palardy:

https://github.com/npalardy/CSVParser

Another important question is whether or not you have to be able to read back the file that you created. If it’s just for export, to a spreadsheet for example, then the short code snippet that I gave above will correctly handle commas quotes and end-of-line characters that occur within the field. Any application that is capable of correctly reading csv files will then be able to read it.

However, if your own application needs to read it back later, then it can be a major programming task to be able to read back the escaped and/or quoted characters. If that’s the case, then tab delimited is by far the simplest solution.

It’s because of many things you mention and others have mentioned in the thread, that I’ve chosen to use Tab Delimited method. I do need to read the data back in. And I also wanted to be able to import elsewhere if and when needed. Since using a TAB instead of a comma solves these issues for me, I’m happy with that decision. Thanks for your feedback.

I haven’t read through all of the tab-delimited discussion, but you should make sure people don’t end up putting tabs into your text box.

Or newlines.

I did consider that, but since tab takes them to the next field, it shouldn’t happen. I suppose I better check to see what happens if one is cut and pasted in. That causes issues, I can use the same routine I was originally going to use to swap commas out with a little modification. :slight_smile:

I did consider that, but since tab takes them to the next field, it shouldn’t happen. I suppose I better check to see what happens if one is cut and pasted in. That causes issues, I can use the same routine I was originally going to use to swap commas out with a little modification. :slight_smile:

removing newlines could be problem. This is a multiline text field and should allow the user to use Return to go the next line. If I filter that out, it’s going to get ugly. :confused:

It sounds like you really should be properly escaping values instead of filtering.

1 Like

I don’t disagree at all, but are there escape codes that will work in XoJo and other software with these tab delimited files?

Fields that contain commas or newlines should be wrapped in quotes. Fields that contain quotes should be wrapped in quotes AND the quotes doubled.

So:

Column1,"This is ""some"" value, that has been
properly escaped and considered one row.",Column3

This is why it’s critical to get a good CSV reader, because it’s not as simple as reading line-by-line.

Google suggests the same is true for tab-delimited, but I’m less familiar with it.

1 Like

Shameless plug warning:
I wrote a csv file editor and submitted it for the Xojo Just Code challenge in 2018.
A link to the project is here:
http://electronbunker.ca/XojoProjects/Week5_CSV_Editor.zip
It reads and writes csv files, and correctly escapes and un-escapes all of the problem characters. The routines that you would be interested in are:
ImportCSV - reads from a text input stream and correctly parses the fields including fields with embedded commas, line endings and quotes
OutputCSV - Saves data to a text output stream. It calls OutQuote to handle the messy job of delimiting and escaping any problem characters.
OutQuote - The part that deals with the problem characters when saving to file.

Edited to add:
Saving to a file is much simpler than reading from a file and parsing the input.
For saving to a file, the routine for dealing with escaping and quoting, OutQuote, is only 6 lines. For reading and parsing the input file, it’s considerably more complicated. However, I’ve never found any other csv input parsing code as short as what I’ve created. It uses a quote parity technique which simplifies the code.

3 Likes

LibreOffice, import csv:

Thanks, I’ll give it a look :slight_smile:

Steve, I did something similar. I use a colon [:] as a delimiter in some text files, so if I find a colon on an input I change it to something else before writing the file; when reading the file I look for the substitute character [‡] (opt-shift-7 on the Mac) since it’s unlikely to ever be needed in a name field.

The same Method is called each time a file is written or read, simply substitute “newChar” and “oldChar” as appropriate in the call.

'*****************************************************************
'*****************************************************************
'**
'** Subroutine clnInput - Global
'**
'** This routine will remove colons from records so they can be
'** written to disk; Colons [:] are used as delimiters and must not
'** be included in any character strings.
'**
'** Routine will place the symbol [‡] and remove a colon [:] from the string
'** sent as “OldStrng”. Program sends “newChar” and “oldChar” to make the substitution.
'**
'*****************************************************************
'*****************************************************************

'*****************************************************************
'***
'*** Housekeeping
'***
'*****************************************************************

var clnStrng as String

'*****************************************************************
'***
'*** The Routine
'***
'*****************************************************************

clnStrng = oldStrng.ReplaceAll(oldChar,newChar)

Return clnStrng

'*****************************************************************
'*** END
'*****************************************************************

Just a note that you said what you didn’t want to happen; you didn’t say what you wanted to happen - there’s a difference. For example, the user entered an invalid character. Most the solutions I’ve read above are focused on “behind the scenes” correcting that character. What about correcting the user?

You could detect the “,” entry, and issue a Beep, then erase/backspace over the comma and put the cursor in the per-comma position. In the olden days, we might even set a counter and if the user tried to enter a comma the second time, we could bring up a dialog entry announcing that a comma was an invalid character in that field. That way the user doesn’t get nagged for just a typo.

Simply correcting the comma in post-processing doing nothing to eliminate the undesirable action - entering a comma. Sort of like today’s parents cleaning up their child’s room rather than letting him/her know the room is messy because they don’t want to hurt the kid’s self-esteem - or whatever.

I hear you Paul. I switched to Tab Delimited which solves the comma issue. As far as other undesirable characters, I’ll deal with them on a case by case basis. Regardless of whether or not the user is notified, the backend work still needs to be done. :slight_smile:

Thank you for understanding. You are absolutely correct that the system should be able to recover and run. As my old programming teacher would say, “I want your program to handle it even if I put the punch cards in upside down…”

I just didn’t see anything posted to address the source of the problem - and educate the user that a comma was invalid in that field.