Saving as tab-delimited file, commas interfere with column parsing

[theDrew]

Hello all,

I regulary receive from a client a data transfer file that I must open in Excel to edit and then save as a Tab-delimited file for importing into a database. If a field has a comma in it, such as

123 45th St., Apt. 18

then Excel saves the field in the Tab-delimited file as:

"123 45th St., Apt. 18" (surrounded by quotes) This causes my database import process to error out.

The source file is comma-delimited. We have tried using a pipe | delimited file, but the same error still occurs. For reasons beyond my control, the client cannot feasibly save the source data as a tab-delimited file. Help?

The current procedure:

- The client sends me the file as a comma-delimited .txt file in the format
"M","Smith ","Mary","J"," ","19591106"...etc for several dozen more columns
- I open the file in Excel via the Text Import Wizard, with a comma as the delimiter and all fields set to Text value
- I make the necessary edits
- I save the file as type Text (Tab Delimited)
- I then open the newly saved Tab Delimited file in Notepad to verify that everything is correct. All fields export correctly EXCEPT for those that contain a comma. They are export in quotes "texthere" as shown above.

How can I work around this problem? I have tried everything and can find no feasible solution.

 

[Gerald Higgins]

What about deleting the commas in the original data, or replacing them with another character such as ;

 

[theDrew]

Gerald, thanks for the suggestion. I actually tried that already but could not figure out how to differentiate between a comma that acts as a delimiter and a comma that is part of a field value.

Someone suggested that I use Access to open the data rather than Excel. The abovementioned problem does not occur in Access...but now we will see what new problems I have created!