Comma Delimited File Adding Double Quotes to fields

[sql4088]

Product: Ultra Edit

Hello,
I'm looking to add double quotes to all fields in a comma delimited file that do not already have double quotes.
i.e.
Here is a sample record with 3 fields.

"NAME",01245,"123 STREET"

I want to change this to

"NAME","01245","123 STREET"

Any suggestions?

Thanks,
Matt

[johnph77]

At worst, KISS and two steps.

Replace . with "."

Replace "" with "

[sql4088]

Thanks for the reply. I guess I was assuming that you knew "Text" fields can have commas in the middle of the field.
i.e. "123 Fake St. Fakville,WA"
I appreciate the feedback, but that will not work in my case. That is why I'd like to set-up a Regular Expression that will validate if the field has double quotes around it already. If the field does not, add double quotes. This is not an emergency, so don't go writing something that isn't already coded.

Thanks again for your help,

Matt

[Mofi]

Use these 3 regular expression replaces in UltraEdit style.

Find What: %^([~"^p]*^),
Replace With: "^1",

Find What: ,^([~"^p]*^),
Replace With: ,"^1",

Find What: ,^([~"^p]*^)^p
Replace With: ,"^1"^p

[sql4088]

Thanks for the Expression. There is obviously something that I'm not telling you because the last Find/Replace didn't find anything. I'm going to look at the help section for regular expressions and decode your awsome regular expression syntax. This is definately a new subject that I'm really interested in so no need to debug my problem. Thanks a million for the code.

How's the weather over there?

Regards from the east coast of the U.S.,

Matt

[Mofi]

I have detected, that my 3 replaces are not enough and you will get a bad file, if there are ',' inside a cell or a cell is empty (= ,, in the file).

Here is a macro, which should do the job better. Don't forget to enable the macro property Continue if a Find with Replace not found.

InsertMode
ColumnModeOff
HexOff
UnixReOff
TrimTrailingSpaces
Bottom
IfColNum 1
Else
"
"
EndIf
Top
Loop
Find RegExp ""*""
IfFound
Find ","
Replace All SelectText ";MOFI;"
EndSelect
Else
ExitLoop
EndIf
EndLoop
Top
Find RegExp "%^([~"^p]*^),"
Replace All ""^1","
Loop
Find RegExp ",^([~",^p]*^),"
Replace All ","^1","
IfNotFound
ExitLoop
EndIf
EndLoop
Find RegExp ",^([~",^p]*^)^p"
Replace All ","^1"^p"
Find MatchCase ";MOFI;"
Replace All ","
Top
UnixReOn

Remove the last red command, if you use regular expression in UltraEdit style by default instead of Unix style.
For UltraEdit v11.10c and lower see Advanced - Configuration - Find - Unix style Regular Expressions.
For UltraEdit v11.20 and higher see Advanced - Configuration - Searching - Unix style Regular Expressions.
Macro commands UnixReOn/UnixReOff modifies this setting.


How this macro works:

1) Make sure that last line of the file is terminated with a line break.

2) Search in a loop for all "..." cells and replace all ',' inside such cells by ";MOFI;".

3) Add "" to all cells of first column which do not already have it. "%^([~"^p]*^)," means find a string with 1 or more characters til ',' at start of the line and where the first character is not a " or a DOS line break (empty line).

4) Add "" to all cells, which are not empty (,,) and do not already have "" in column 2 til last column-1.

5) Add "" to all cells of last column (no , at the end, but a line break) which do not already have it.

6) Convert the ";MOFI;" strings back to , as it should be.

The macro is designed for working on a DOS terminated file. If you have a Unix file opened in Unix mode, you have to use ^n instead of ^p.

[sql4088]

MOFI,

I can't tell you how much I appreciate your help. Your instructions where perfect. Thank you, thank you, thank you.
Thanks a Mil!

Matt