Escape quotes in Excel file when saving as tab delimited

This isn't a question specifically about the Eggplant tool, but I need this file conversion to work so that the data is ready for Eggplant to read. I'm hoping someone has run into this problem and found a solution; I've not found the answer anywhere else.

How do I escape double quotes when saving an Excel file as a tab delimited file? Let's say I have test data stored in an Excel spreadsheet. And in one cell of the worksheet I have a string that looks something like this:

This is a "test" string

When I save the Excel file as a tab-delimited file, the result is that the string in the text file now looks like this:

"This is a ""test"" string"

I've not been able to find out how to escape the double quotes so that the result in the tab delimited file is the same string that was in the excel file. Any ideas?

Comments

  • EggplantMattEggplantMatt ForumAdmin admin
    I don't know if there's any way to get Excel to output the text without the double quotes, but you can use a simple script to clean up the file:
    -- read the file into a variable
    put file "/path/to/myOutputFile" into someVar
    -- find the doubles and replace them with a placeholder
    replace <<"">> in someVar with "jjj"
    -- remove all of the single quotes
    replace quote in someVar with empty
    -- replace the placeholders from step one with single quotes
    replace "jjj" in someVar with quote
    -- write the modified text back out to the file
    put someVar into file "/path/to/myOutputFile"
    
Sign In or Register to comment.