Save as csv with text in quotes

[ChrisK]

I am having dificulty finding a way of saving a file in the format I need
for another application.
I have a file which has a number of fields enclosed in quotes and seperated
by commas. Some of these fields are text and some numeric though treated as
text, eg phone numbers. I can open the file with Excel, make changes I need
to but when it comes to saving I cannot figure out how to get the same
format again. It has to be comma seperated with each field in quotes and
carriage return at the end of a record.

Thanks for any help.

ChrisK

 

[David McRitchie]

Hi Chris,
Are you saying you start with a file that can be read into Excel okay
and into the other application okay, but when you make changes
in Excel and save it again as a CSV file it is no good to the original
application. If the modified file accepted okay in Excel after saving
it an reopening it.

If you look at Save as CSV, you will probably see a few choices,
PC and Mac.

If you stated what you saw for some of the fields in the flat file (text file)
before bringing it into Excel and what you see after saving it from Excel
you might get a faster more reliable answer.

 

[Dave Peterson]

Maybe you could use a macro that writes your data:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

======
Earl's may be sufficient right out of the box. He supports lots of options.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dodo2u]

I am having dificulty finding a way of saving a file in the format I
need for another application.
I have a file which has a number of fields enclosed in quotes and
seperated by commas. Some of these fields are text and some numeric
though treated as text, eg phone numbers. I can open the file with
Excel, make changes I need to but when it comes to saving I cannot
figure out how to get the same format again. It has to be comma
seperated with each field in quotes and carriage return at the end of
a record.

Thanks for any help.

ChrisK

Why not edit it in WordPad?

 

[ChrisK]

Thanks for the reply and sorry if what I posted wasn't very clear.

I start off with a file containing for example:

"S/N","Modem#","DeviceName","Chain","UserName"
"EX02830306","01224550968","U.S. Robotics 56K FAX EXT","0","ADMIN" etc

When opening the file in Excel depending on whether I select Text qualifier
as " or none I get a spreadsheet which has cells like this:

"S/N" "Modem#" "DeviceName"
"Chain" "UserName"
"EX02830306" "01224550968" "U.S. Robotics 56K FAX EXT" "0" "ADMIN"
etc

or like this

S/N Modem# DeviceName
Chain UserName
EX02830306 01224550968 U.S. Robotics 56K FAX EXT 0 ADMIN etc

When I save as a CSV file I will then end up with this

"""S/N""","""Modem#""","""DeviceName""","""Chain""","""UserName"""
"""EX02830306""","""01224550968""","""U.S. Robotics 56K FAX
EXT""","""0""","""ADMIN""" etc

or this

S/N,Modem#,DeviceName,Chain,UserName
EX02830306,01224550968,U.S. Robotics 56K FAX EXT,0,ADMIN etc

Either of these 2 files can be opened again in Excel but are no good for the
application I need them for.

Hope this makes it clearer.

Chris K

 

[Dodo2u]

Thanks for the reply and sorry if what I posted wasn't very clear.

I start off with a file containing for example:

"S/N","Modem#","DeviceName","Chain","UserName"
"EX02830306","01224550968","U.S. Robotics 56K FAX EXT","0","ADMIN"
etc

It's a couple of years back but when I remember correctly Excel has
problems with producing genuine CSV-files. I think I used a different
program in between.

From Excel saved as dbf and in CrystalReports converted to the real CSV.

 

[ChrisK]

Thanks Dodo2u

I'll look at that but it still seems odd to me that Excel can't save in the
format I want. By the way the reason I want to edit in Excel is so that I
can sort, filter, delete duplicate records, etc.

ChrisK

 

[ChrisK]

Dave,

Earl's macro is perfect, does exactly what it says.

Thanks for the help and best regards.

Chris K

 

[David McRitchie]

Hi Chris,
That's simple, you are saving as a (print) text file not as a CSV file.

File, Save As,
filename: -- ______________________
Save as type: -- CSV (Comma Delimited (*.csv)

There are also flavors for MS-DOS and Mac, but
I doubt that it will matter to you. It just CR LF (and possibly NL) usage.

Of course when you read the file into Excel you probably want to
manually change the extension to .txt so you can use the Wizard
options and have control.

 

[ChrisK]

Thanks David,

I been saving as a CSV not a print file so not too sure about your reply.
I want to end up with a file which is in the same format as the one I
started with, ie each field enclosed in quotes and seperated by commas. The
CSV files I have saved give either no quote marks or too many when each
existing quote is then enclosed in quote marks.
Anyway the reply from Dave Peterson gave me a macro which will give exactly
the result I want.

Chris K

 

[Dave Peterson]

I'm sure Earl appreciates the nice words.

Dave,

Earl's macro is perfect, does exactly what it says.

Thanks for the help and best regards.

Chris K