Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Problem saving Excel as text file

  1. Feb 14, 2006 #1

    Doc Al

    User Avatar

    Staff: Mentor

    I'm trying to save an Excel spreadsheet as a (tab-delimited) text file for input into another database. For some reason, fields containing a comma are being displayed in the text file within quotes, which I don't want. For example:
    John Doe appears as: John Doe
    Doe, John appears as: "Doe, John"​

    What I want to see is:
    John Doe appearing as: John Doe
    Doe, John appearing as: Doe, John​

    Is there any clever way of saving the Excel file so I don't have those quotes?
     
  2. jcsd
  3. Feb 14, 2006 #2

    chroot

    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    I believe the quotes will go away if the cell categories (types) are not Text. Select the cells, then choose Format->Cells.

    Can you change the cell type from Text to some other value? Like General or Custom?

    - Warren
     
  4. Feb 15, 2006 #3

    Doc Al

    User Avatar

    Staff: Mentor

    I've tried all sorts of cell formats, but the double quotes still appear for entries that contain a comma. So that didn't work.

    (It's almost like the program is trying to prevent a comma-separated value error. But I'm saving as tab-separated text. Strange!)
     
  5. Feb 15, 2006 #4

    NateTG

    User Avatar
    Science Advisor
    Homework Helper

    Since you're using Excel, it's clearly not a huge dataset. So you could just use your favorite text editor to do a search and replace to switch quote-tab and tab-quote to tab...

    What I personally find obnoxious is that it doesn't quote the other stuff. If it did all or none, it would be a whole lot easier to deal with.
     
  6. Feb 15, 2006 #5

    Doc Al

    User Avatar

    Staff: Mentor

    About 15K records. Don't ask. :uhh:
    Editing the output, while easy enough, would be my last choice. Good idea, though. (It's just bugging me that I can't turn that stuff off!) But I think I've found how to write a macro that removes the quotes.
     
  7. Feb 15, 2006 #6

    NateTG

    User Avatar
    Science Advisor
    Homework Helper

    I know it's ugly, but unless you're going to be doing this sort of thing regularly, the effort in finding the 'nice' answer isn't going to save you any time.
     
  8. Feb 15, 2006 #7

    Math Is Hard

    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Which database system are you moving it into? I usually work with SQL Server and Access so I can just easily import data directly from Excel. I used to have to move around a lot of data, and even if my final format needed to be a delimited text file, I often pulled the data into a database system first, creating a table, and then did the export to text file from there. It gave me a chance to check and clean the data, and also better control of the export process.
     
  9. Feb 16, 2006 #8

    Doc Al

    User Avatar

    Staff: Mentor

    The Excel data is being moved into a SQL Server table. That's its final destination, for use by another program. (Is there an easy way to move the data directly into SQL Server?)
     
  10. Feb 16, 2006 #9

    Math Is Hard

    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Yes, it's super easy. You can use DTS (Data Transformation Services) in Enterprise Manager. Have you tried out the DTS import wizard?

    Here's how you can quickly move an Excel spreadsheet into a new table. In Enterprise Manager, select your database, and then right click and you should see an option for All Tasks and then Import Data. That will bring up the Wizard. Hit next and then make sure you choose Excel for your data source. Then browse to the file you want to import.

    Then you hit next and you'll see all the destination info, probably won't need to change anything here, just double check that the target database is correct. Hit next and you'll see an option to just simply copy everything over or you can construct a SQL Query for the transfer.Then hit next and you should get a choice for which worksheets to import. After you have chosen a worksheet, you can edit the name in the path in that destination column to be whatever name you want the new table to have. Hit Next, choose "run immediately" and hit next, check everything over and hit finish.. babdaboom-badabing! You should get a message that it was successful. Refresh Enterprise Manager and you should see your table.
     
    Last edited: Feb 16, 2006
  11. Feb 16, 2006 #10
    What version are you using? My Danish 2002 sp3 have no problem exporting as a tab-delimited text file, where Doe, John appears without quotes as needed.
     
  12. Feb 19, 2006 #11

    Doc Al

    User Avatar

    Staff: Mentor

    Thanks! I'll check it out.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?



Similar Discussions: Problem saving Excel as text file
  1. Text File Editing (Replies: 14)

Loading...