Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting with quotes

    Hi - I need to create an import file that requires quote marks around each field in a CSV format - Is there any easy way to do that with the information in Excel? I am exporting the data from one database into Excel; manipulating it, and importing it to a different database. This is a process that I will need to repeat weekly for a month or so. Any help would be appreciated!!

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    Hi Gail

    Put the following code in a module in a new workbook.
    Open your file with the data you want to format.
    Select the entire range you want to format.
    Press "Alt F8", select the macro and Run.

    ----Code----
    Sub MyCustomFormat()
    Set r1 = Selection
    For Each cl In r1
    cl.Value = Chr(34) & cl.Value & Chr(34)
    Next cl
    End Sub
    ----End of Code----

    Use Save As to save in csv format.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    Given that your data might lie in individual cells, one row of data to one row of CSV output, you could try the following:

    =CHAR(34)&A1&CHAR(34)&","&CHAR(34)&B1&CHAR(34)&"," &CHAR(34)&C1&CHAR(34)

    This would give you: <font color=blue>"Alpha","Beta","Gamma"</font color=blue>

    I use something like this routinely to convert table-based data to formatted text data to upload into our mainframe for additional processing. I create a new sheet in the workbook, expand the above formula to account for all of the fields in each record, and then copy the formula down to encompass all of the data. I then save this second sheet as a CSV text file.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    That formula looks like a good candidate for conversion to a function. <pre>Function QuoteMark(fRange As Range) As String
    For Each Cell In fRange
    QuoteMark = QuoteMark & Chr(34) & (Cell.Value) & Chr(34) & ","
    Next
    QuoteMark = Left(QuoteMark, (Len(QuoteMark) - 1))
    End Function</pre>

    It might save a but of typing if there are numerous columns.

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    What I usually do if there are a lot of columns to be included in the formula is to set up the first couple of columns, and then copy and paste in the formula bar, changing column letters as necessary. Not as fancy as your idea, but for someone who is not used to functions and such it is a bit of an easier solution.

  6. #6
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    Thanks Frits - That was the perfect answer!! You have saved me so much work <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    Blipping a three-year-old thread.... because it's still relevant!

    A guy I work with needs to do something quite similar to that function (export comma-delimited with quotes), but he doesn't want quotes around Number cells, only text. Is there a way to make the thing smart enough to do that?

    Dave

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting with quotes

    No, see my response <!post=Here,355975>Here<!/post>.
    Legare Coleman

Posting Permissions

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