+ Reply to Thread
Results 1 to 7 of 7

Saving as tab delimited file

  1. #1
    Registered User
    Join Date
    05-29-2007
    Posts
    2

    Saving as tab delimited file

    I am new to these forums and haven't searched through threads much so let me apologize if this has already been answered numerous times.

    I frequently save .xls sheets as tab and comma delimited files for uploading to a database. When saving as a tab delimited, excel loves to put quotation marks EVERYWHERE. Everywhere I have a " it wants to put another one, then another behind the text in that cell. If i have a dimensions field like 12 x 12 it likes to put "12 x "12....and if I call it 12" x 12"...well that's a whole other can of worms.

    What 3rd party products are out there and work well for eliminating the step of me saving as tab delimited, opening it up in wordpad, and find/replace hundreds of " marks?

    Thanks

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Darth_Beckett
    I am new to these forums and haven't searched through threads much so let me apologize if this has already been answered numerous times.

    I frequently save .xls sheets as tab and comma delimited files for uploading to a database. When saving as a tab delimited, excel loves to put quotation marks EVERYWHERE. Everywhere I have a " it wants to put another one, then another behind the text in that cell. If i have a dimensions field like 12 x 12 it likes to put "12 x "12....and if I call it 12" x 12"...well that's a whole other can of worms.

    What 3rd party products are out there and work well for eliminating the step of me saving as tab delimited, opening it up in wordpad, and find/replace hundreds of " marks?

    Thanks
    Hi,

    Excel, in general, follows the standard for saving .csv etc files, that is, any cell which has what might be considered 'control' characters (ie, comma and quotes) are enclosed in quotes, and a quote character is doubled to distinguish it from the open-close pair, thus 12" would save as "12"""

    Most programs receiving .csv type files recognise this and receive the file accordingly.

    Does your Database program have the facility to export data as .csv?
    If so then it should also import the file corrently.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    05-29-2007
    Posts
    2
    Well the database in question is Miva Merchant 5. When importing to it, it breaks down tab delimited files and lets you select from a pull-down menu for each cell what you want that information stored as (product_code, Name, Price, etc.).

    When importing, all of the quotation marks are scattered throughout the cells and WILL be copied onto the web page as shown. I've seen a product sold called ExcelErate, that claims to clean the double quotations, but sometimes I have quotations around all sorts of data.

  4. #4
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    When I have had similar problems in the past I have resorted to using the text and concatenate functions to format the output EXACTLY as I wanted it in column 1 of an otherwise blank sheet.

    I then output this sheet as a .txt file.
    It can then be renamed as .csv (outside EXCEL).

    Its a bit of a palaver, but it works.

    Mark.

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Darth_Beckett
    Well the database in question is Miva Merchant 5. When importing to it, it breaks down tab delimited files and lets you select from a pull-down menu for each cell what you want that information stored as (product_code, Name, Price, etc.).

    When importing, all of the quotation marks are scattered throughout the cells and WILL be copied onto the web page as shown. I've seen a product sold called ExcelErate, that claims to clean the double quotations, but sometimes I have quotations around all sorts of data.
    Unfortunately the Miva Merchant 5 appears to not follow industry standards in relation to files such as .csv files, in that it does not permit the delimiter to also be present as a part of the data in it's 'flat files', most other programs do.

    Perhaps there is a Miva Merchant users group with their own information as to how to deal with this shortcoming.

    Another possibility is that you exclude from your data all items which cause quotes.

    hth
    ---

  6. #6
    Registered User
    Join Date
    03-13-2005
    Posts
    3
    One thing that I do for this is to use the extended character for " and it works good.
    "
    Steve

  7. #7
    Registered User
    Join Date
    10-16-2013
    Location
    United States
    MS-Off Ver
    Excel 2012
    Posts
    2

    Re: Saving as tab delimited file

    This is the craziest and most frustrating thing about Excel that I've ever encountered! Try OpenOffice. You can install Calc at no charge and with minimal options, if you choose. It makes managing lists and exporting as tab delimited files easy, without any unwanted added quotes! And, it's WYSWYG - unlike Excel, the cells display any quotes and single quotes without any rigamarole. Why MS can't make Excel do this is beyond me!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1