Export Tab-Delimited File Inserting Unwanted Quote Marks Around Fields Containing Commas And Other Special Characters

JDSpearns

New Member
Joined
Jul 24, 2009
Messages
2
Excel is inserting "unwanted quote marks" around text fields containing commas or other special characters when exporting a file in "tab-delimited" format.

Is there a standard option or something to disable this behaviour without having to write a macro to prevent it?

Thanks.
J. David Spearns
{Email address removed by Admin}
Spam bots routinely troll public forums looking for email addresses to Spam
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,878
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Yeah, that is one of those inexplicable little things that Excel does. Usually, it is not a problem because most programs that read in text files can handle text qualifiers. Do you know for a fact that the program you are importing into can't handle them?

You may be able to locate some VBA code to create tab-delimited text files that will avoid this issue.

What other special characters are you talking about?

BTW, I removed your Email Address from your post. It is generally not a good idea to post it in a public forum unless you like getting Spam! If you have the need to share your email address with someone, it is best to do so in a Private Message.
 

JDSpearns

New Member
Joined
Jul 24, 2009
Messages
2
Thanks for the quick reply and advice.(y)

A closer look at the data revealed that it was just commas that were triggering this unwanted behaviour and not other characters.

The application that imports this data is a package, although I can strip the unwanted quote marks before or after the import to work around the problem.
It is just that I wish that Excel would not put them there in the first place unless you specifically requested them.

Again, thanks.
J. David Spearns
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,878
Office Version
  1. 365
Platform
  1. Windows
Yeah, I am not a big fan of how Excel handles text qualifiers and text files. What I really hate is if you open a CSV file directly in Excel, and you have numeric data with text qualifiers (like zip code "01234"), Excel brings them in as numbers and you lose the leading zeroes. Very annoying, you think it should recognize it should be a text field by the qualifiers.

You can get around that by importing the file as text instead of opening directly in Excel, but it is still an annoyance -- especially since Microsoft usually sets Excel as the default program to view CSV files (which you can also manually change).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,739
Members
425,429
Latest member
MMMMMM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top