Problem saving Excel as text file

[Doc Al]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.