Problem saving Excel as text file

  • Thread starter Doc Al
  • Start date

Doc Al

Mentor
44,656
989
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?
 

chroot

Staff Emeritus
Science Advisor
Gold Member
10,097
33
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

Mentor
44,656
989
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

Science Advisor
Homework Helper
2,448
5
Doc Al said:
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

Mentor
44,656
989
NateTG said:
Since you're using Excel, it's clearly not a huge dataset.
About 15K records. Don't ask. :uhh:
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

Science Advisor
Homework Helper
2,448
5
Doc Al said:
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.
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

Staff Emeritus
Science Advisor
Gold Member
4,435
23
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

Mentor
44,656
989
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

Staff Emeritus
Science Advisor
Gold Member
4,435
23
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:
80
0
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

Mentor
44,656
989
Math Is Hard said:
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.
 

Want to reply to this thread?

"Problem saving Excel as text file" You must log in or register to reply here.

Physics Forums Values

We Value Quality
• Topics based on mainstream science
• Proper English grammar and spelling
We Value Civility
• Positive and compassionate attitudes
• Patience while debating
We Value Productivity
• Disciplined to remain on-topic
• Recognition of own weaknesses
• Solo and co-op problem solving

Top Threads

Top