# Microsoft Office Application Help - Excel Help forum > Excel General >  >  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.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"ChrisK" <chris@nospam.ntlworld.com> wrote in message news:IX7Ge.5253$SO4.1203@newsfe4-win.ntli.net...
> 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
>
>

----------


## 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

ChrisK wrote:
>
> 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

--

Dave Peterson

----------


## Dodo2u

"ChrisK" <chris@nospam.ntlworld.com> wrote in
news:IX7Ge.5253$SO4.1203@newsfe4-win.ntli.net:

> 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?


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

----------


## 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


"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
news:OKqt0X5kFHA.2860@TK2MSFTNGP15.phx.gbl...
> 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.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "ChrisK" <chris@nospam.ntlworld.com> wrote in message
news:IX7Ge.5253$SO4.1203@newsfe4-win.ntli.net...
> > 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
> >
> >
>
>

----------


## Dodo2u

"ChrisK" <chris@nospam.ntlworld.com> wrote in
news:P0aGe.5279$SO4.2450@newsfe4-win.ntli.net:

> 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.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

----------


## 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
"Dodo2u" <dodo2u@-takethisNOSPAMout-freemail.nl> wrote in message
news:Xns96A1D93DF10F3dodo2ufreemailnl@62.179.104.135...
> "ChrisK" <chris@nospam.ntlworld.com> wrote in
> news:P0aGe.5279$SO4.2450@newsfe4-win.ntli.net:
>
> > 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.
>
>
> --
>
> It is I, DeauDeau
> (Free after monsieur Leclerc in 'Allo, 'allo)

----------


## ChrisK

Dave,

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

Thanks for the help and best regards.

Chris K

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E91918.4FED8B22@verizonXSPAM.net...
> 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
>
> ChrisK wrote:
> >
> > 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
>
> --
>
> Dave Peterson

----------


## 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.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"ChrisK" <chris@nospam.ntlworld.com> wrote in message news:P0aGe.5279$SO4.2450@newsfe4-win.ntli.net...
> 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
>
>
> "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
> news:OKqt0X5kFHA.2860@TK2MSFTNGP15.phx.gbl...
> > 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.
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> > My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "ChrisK" <chris@nospam.ntlworld.com> wrote in message
> news:IX7Ge.5253$SO4.1203@newsfe4-win.ntli.net...
> > > 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
> > >
> > >
> >
> >
>
>

----------


## 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

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
news:OEXLr96kFHA.3828@TK2MSFTNGP12.phx.gbl...
> 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.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "ChrisK" <chris@nospam.ntlworld.com> wrote in message
news:P0aGe.5279$SO4.2450@newsfe4-win.ntli.net...
> > 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
> >
> >
> > "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
> > news:OKqt0X5kFHA.2860@TK2MSFTNGP15.phx.gbl...
> > > 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.
> > > ---
> > > HTH,
> > > David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> > > My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> > > Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
> > >
> > > "ChrisK" <chris@nospam.ntlworld.com> wrote in message
> > news:IX7Ge.5253$SO4.1203@newsfe4-win.ntli.net...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

----------


## Dave Peterson

I'm sure Earl appreciates the nice words.

ChrisK wrote:
>
> Dave,
>
> Earl's macro is perfect, does exactly what it says.
>
> Thanks for the help and best regards.
>
> Chris K
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42E91918.4FED8B22@verizonXSPAM.net...
> > 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
> >
> > ChrisK wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

----------

