RegEx to add commas and Single quotes

Question

Thanks in advance for your help... I have a panel of information in the following format:

ACMH       ADMIT TO XXX                  
ACSU       ADMIT TO XXX SUB-ACUTE  (TCU) 
ADOPT      ADOPTION                      
AMA        AGAINST MEDICAL ADVICE        
APSY       ADMIT TO CMH PSYCHIATRIC UNIT 
CANCEL     CANCELLED SERVICE             
CANCELER   CANCELLED ER                  
CANCELTRI  CANCELLED TRIAGE              
EXP        EXPIRED                       

I need to format the Panel to look like this:

'ACMH','ADMIT TO XXX'                  
'ACSU','ADMIT TO XXX SUB-ACUTE  (TCU)' 
'ADOPT','ADOPTION'                      
'AMA','AGAINST MEDICAL ADVICE'        
'APSY','ADMIT TO CMH PSYCHIATRIC UNIT' 
'CANCEL','CANCELLED SERVICE'             
'CANCELER','CANCELLED ER'                  
'CANCELTRI','CANCELLED TRIAGE'              
'EXP','EXPIRED'

My colleague recommended RegEx replacement. Is this an appropriate strategy? If so can someone provide me with some guidelines on how to achieve this.

My RegEx experience is very limited (close to non-existant) so any explanations are appreciated.

This information is being inserted into a SQL Server database. We have a program that will read .csv file with single quotes and pretty much insert into a table.

i.e....

Insert Into  myTable  ( CodeValue, Description)
Values ('ACMH', 'ADMIT TO XXX')

We have never been given a panel like this so I was wondering if it is possible to achieve.

Answer 1

You can replace

^(\S+)\s+(.*?)\s*$

with

'$1','$2'

DEMO

Explanation:

• ^ matches the beginning of the line
• (\S+) matches a non-empty sequence of non-space characters. The parentheses around this puts it in capture group 1
• \s+ matches the spaces after the first field
• (.*?) is a non-greedy match of any sequence of characters, and the parentheses put the match in capture group 2
• \s* matches the spaces at the ends of the lines. I did this so they won't be included in the preceding capture group, to remove the trailing whitespace that was in your input data.
• $ matches the end of the line

In the replacement $1 and $2 are repaced with the contents of the capture groups.