Register to reply

Problem saving Excel as text file

by Doc Al
Tags: excel, file, saving, text
Share this thread:
Doc Al
#1
Feb14-06, 06:54 PM
Mentor
Doc Al's Avatar
P: 41,579
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?
Phys.Org News Partner Science news on Phys.org
'Office life' of bacteria may be their weak spot
Lunar explorers will walk at higher speeds than thought
Philips introduces BlueTouch, PulseRelief control for pain relief
chroot
#2
Feb14-06, 07:22 PM
Emeritus
Sci Advisor
PF Gold
chroot's Avatar
P: 10,427
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
Doc Al
#3
Feb15-06, 08:10 AM
Mentor
Doc Al's Avatar
P: 41,579
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!)

NateTG
#4
Feb15-06, 09:48 AM
Sci Advisor
HW Helper
P: 2,537
Problem saving Excel as text file

Quote Quote by Doc Al
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!)
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.
Doc Al
#5
Feb15-06, 10:08 AM
Mentor
Doc Al's Avatar
P: 41,579
Quote Quote by NateTG
Since you're using Excel, it's clearly not a huge dataset.
About 15K records. Don't ask.
So you could just use your favorite text editor to do a search and replace to switch quote-tab and tab-quote to tab...
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.
NateTG
#6
Feb15-06, 12:27 PM
Sci Advisor
HW Helper
P: 2,537
Quote Quote by Doc Al
About 15K records. Don't ask.

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.
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.
Math Is Hard
#7
Feb15-06, 01:15 PM
Emeritus
Sci Advisor
PF Gold
Math Is Hard's Avatar
P: 4,922
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.
Doc Al
#8
Feb16-06, 07:37 AM
Mentor
Doc Al's Avatar
P: 41,579
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?)
Math Is Hard
#9
Feb16-06, 11:35 AM
Emeritus
Sci Advisor
PF Gold
Math Is Hard's Avatar
P: 4,922
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.
Triss
#10
Feb16-06, 01:42 PM
P: 85
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.
Doc Al
#11
Feb19-06, 08:25 AM
Mentor
Doc Al's Avatar
P: 41,579
Quote Quote by Math Is Hard
Yes, it's super easy. You can use DTS (Data Transformation Services) in Enterprise Manager. Have you tried out the DTS import wizard?
Thanks! I'll check it out.


Register to reply

Related Discussions
Matlab file saving. Math & Science Software 3
HTML - posting from a form to a text file Programming & Computer Science 17
Password for excel file Computing & Technology 7
BitTorrent file saving problem Programming & Computer Science 3
Excel file won't open under java script tree menu Computing & Technology 0