+ Reply to Thread
Results 1 to 11 of 11

Save as csv with text in quotes

  1. #1
    ChrisK
    Guest

    Save as csv with text in quotes

    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



  2. #2
    David McRitchie
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message news:[email protected]...
    > 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
    >
    >




  3. #3
    Dave Peterson
    Guest

    Re: Save as csv with text in quotes

    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

  4. #4
    Dodo2u
    Guest

    Re: Save as csv with text in quotes

    "ChrisK" <[email protected]> wrote in
    news:[email protected]:

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

  5. #5
    ChrisK
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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
    > >
    > >

    >
    >




  6. #6
    Dodo2u
    Guest

    Re: Save as csv with text in quotes

    "ChrisK" <[email protected]> wrote in
    news:[email protected]:

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

  7. #7
    ChrisK
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message
    news:[email protected]...
    > "ChrisK" <[email protected]> wrote in
    > news:[email protected]:
    >
    > > 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)




  8. #8
    ChrisK
    Guest

    Re: Save as csv with text in quotes

    Dave,

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

    Thanks for the help and best regards.

    Chris K

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  9. #9
    David McRitchie
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message

    > news:[email protected]...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >




  10. #10
    ChrisK
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message

    > > news:[email protected]...
    > > > > 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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  11. #11
    Dave Peterson
    Guest

    Re: Save as csv with text in quotes

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1