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

General
1

I’m trying to disallow, filter or whatever needs to be done prevent a user from entering a comma into a text box. The reason is, that the output will go to a CSV file so the comma will will obviously screw up the output.

2

There may be some problems with this method I haven’t thought of, but in the KeyDown event you can put:

if key = "," then
  return true
end if
3

what about replace comma with space when doing output to csv??

4

I guess great minds think alike :wink:

I did exactly that a few minutes after my post. Thanks!

5

I will check this out, I actually already did a routine to replace the comma with a space, but this might even be cleaner

6

What about Paste or drag and drop?

What about quotes?

Excluding characters may not be the best approach.

7

This actually works very well for my situation. I’ll continue testing with it but, don’t for see any issues as it’s only for the one text box.

And if I do find issues, I have my other routine to fall back on.

Thanks!

8

I didn’t even think about cut and paste. Thanks! Yes, that does present the problem so I’m changing back to use routine to just replace the comma with a space. This particular field will just be used for notes so the user can separate things with other methods or simply hitting Return and going to a new line.

9

Well unfortunately, this doesn’t help if the user cuts and pastes text containing a comma. So, I had to go back to my routine to just scrub the string of commas replacing with spaces.

10

And quotes.

Otherwise someone can enter

Hello, "World

Your resulting CSV might avoid quotes

1234,Apple,Red

But if it uses quotes around strings, you could end up with

“1234”,"Hello “World”,“Red”

…will the parser understand that later on?

11

Two possible ways:

Maybe you can parse the text before exporting to CSV and remove (or replace) all , chars ?

Or, you can change the CSV importer to not use , but any other chars so you have control over this.

12

This is the code I use in my .csv file editor to quote fields before writing to the output file. It handles embedded commas, endofline characters and quotes.

Public Function OutQuote(txt As String) as String
  'Enclose field data in quotes as necessary, and escape any embedded quotes.
  'If Boolean property fullQuote is true, then the field is quoted whether needed or not.
  dim delim As String = ","
  if fullQuote or (InStr(txt,EndOfLine)>0) or (InStr(txt,kQuote)>0) or (InStr(txt,delim)>0) then
    return kQuote+ReplaceAll(txt,kQuote,kQuote+kQuote)+kQuote
  else
    return txt
  end if
End Function

Note that constant kQuote is the double quote character.

13

dependent on what happens with this file output.
how about tab or ; as delimiter?

if the csv parser read rows, then you also have to replace cr lf possibly.

14

Two (possibly) less helpful ideas:

  1. Removed the key from the keyboard. Problem solved.

  2. Consider using tab-delimited if possible. This does not have the comma problem. Most programs that will accept CSV will accept tab-delimited. It’s much cleaner.

2 Likes
15

I will certainly consider this. Thanks!

16

Tab-delim is much superior to CSV for many reasons.

1 Like
17

This is possibly an even less useful answer, but it is easy.

If there is some simple character like ~ that you want to substitute for , (comma) you can put

Me.Text=ReplaceAll(Me.Text,",","~")

in the Text Change event of the Text Area. You could use a space, but ~ might be more useful if you wanted to have some hope of restoring commas downstream. {If ~ would not be expected in your text normally}.

Putting this code in the TextChange event would seem to potentially slow down typing and make the computer tired, but it does actually not cause a problem, at least with reasonably short texts, and it handles pasting in of text.

18

Thanks for the tip. This could come in handy for some things. For now, switching to Tab Delimited format solved the comma issue for my current situation.

19

I don’t get it…
How is allowing or disallowing quotes (") preventing or allowing commas to be entered?

20

The reason for disallowing commas is to ensure that commas do not break the CSV file.
But adding a quote character will also break the file: some parsers will see a quote and treat everything until the next quote as one field.
You need to prevent both. Preventing commas is half the job.