ShopSite Knowledgebase :: Extra doublequote marks (") appear in my products and/or pages after a database upload of a file edited with Microsoft Excel

Knowledgebase Home

Search the Knowledgebase

Browse by Category

Knowledgebase Home > General

Extra doublequote marks (") appear in my products and/or pages after a database upload of a file edited with Microsoft Excel

Product: ALL
Version: ALL
Platform: ALL

After uploading a tab-delimited products or pages file that was created or edited in Microsoft Excel, I have noticed that many of my ShopSite fields contain extra double quotes (") that I didn't put there. Why is this, and how can I prevent it?

Solution ID: S02720

These extra quotes are being added by Microsoft Excel when you export from the Excel spreadsheet into a tab-delimited format. Excel will often put these quotes around fields with non-alphanumeric characters such as commas, hyphen, etc, even though a tab-delimited file should not need these since the fields are clearly delimited by the tab character.

The best way to avoid this is to copy-and-paste the data from Excel into a text editor (such as Notepad) rather than use Excel's Save or Save As functions. To do this, highlight all of the rows and columns of data in the Excel Spreadsheet that you want to import to ShopSite, right click on a highlighted area and select the Copy option to copy the highlighted content. Open a new text file document using Notepad (or another plain text editor) and right click in it and choose the Paste option to paste the copied data into this new text document, which you can save and use to upload into ShopSite.

A trick in Excel to highlight all rows and columns in the spreadsheet is to click on the little empty square that is above the row numbers and to the left of the column letters in Excel.

Solution ID: S02720

Another method of getting rid of the extra doublequotes after exporting a tab-delimited file from Excel is the search-and-replace method. First open up the text file in a text editor with Search/Replace capabilities (such as Notepad).

Perform the following Search/Replaces:

1. search "" replace ***** (replace double double quotes, which is how your original double quotes will appear in a file that has been exported/modified by Excel, with five astericks)

2. search " replace _ (replace quotes with nothing)

3. search ***** replace " (replace five astericks with double quotes to recover your original double quotes)

Solution ID: S02720

No related articles were found.