1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Converting Excel data to ASCII comma delimited text file format

Discussion in 'Business Applications' started by jsolo, Aug 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. jsolo

    jsolo Thread Starter

    Joined:
    Aug 15, 2008
    Messages:
    3
    I need to convert Excel data into ASCII comma delimited text file format. I know that if I save as csv. that the data can be pasted into Wordpad with the comma delimiter, but how do I get the quotes around the data fields?

    Any help would be appreciated.

    Thanks
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Not sure I know what you mean.
    A csv file would have the data stored as (as an example)
    1,ghas
    2,dfaha
    3,fkdfgyi
    with a comma representing a column break.
    Quotes aren't part of a csv file.
    Are you wanting something like
    "1","ghas"
    "2","dfaha"
    "3","fkdfgyi"
    ???
     
  3. jsolo

    jsolo Thread Starter

    Joined:
    Aug 15, 2008
    Messages:
    3
    Sorry for not being clear in my question. Yes I am looking for a way to put my data into this format "name","age","weight","height",....

    Thanks for your help.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I would probably just concatenate the values along with quotes as needed in a new column. For instance, if the data is in A and B, put " in C and in d and e have the combined data like this
    1 a " "1" "a"
    which comes from
    1 a " =C1&A1&C1 =C1&B1&C1
     
  5. jsolo

    jsolo Thread Starter

    Joined:
    Aug 15, 2008
    Messages:
    3
    Thanks for the help. I was just curious if anyone had an existing macro or another tool to help with this.

    When I do the concatenate method and open the .csv file in wordpad the data looks like this:

    """Name""","""Height""","""Weight""","""Race""","""Age"""

    It's adding 2 quotes per side. Why is that?

    Thanks.
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I would guess that in column C (in my example) you have "" rather than ".
    The formula puts whatever is in C on each side of the words, so you are ending up with ""&Name&""
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is a sample macro that will write to a text file.

    Code:
    Sub WriteFile()
    
        Dim vNumber As Integer
        Dim vFileName As String
        vNumber = FreeFile
        
        'Change text file save location
        vFileName = "C:\Test.txt"
    
        Open vFileName For Output As #vNumber
        
        'Change Start and End Rows in Excel file
        For i = 1 To 3
        
        'Add additional columns as needed.  This example uses columns A-D only
        Print #vNumber, Chr(34) & Range("A" & i).Value & Chr(34) & "," & Chr(34) & Range("B" & i).Value & Chr(34) & "," _
        & Chr(34) & Range("C" & i).Value & Chr(34) & "," & Chr(34) & Range("D" & i).Value & Chr(34)
        
        Next i
    
        Close vNumber
    
    End Sub
    Regards,
    Rollin
     
  8. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Hi to every one
    you could use this code in excel to get a single column with the text in the way you need it and after that to save it as text file
    Code:
    Sub ComaSeparatedText()
    
        Columns("A:A").Insert Shift:=xlToRight
        Columns("A:A").NumberFormat = "General"
        rws = Range("B2").End(xlDown).Row
        Cln = Range("B1").End(xlToRight).Column
        
        f1 = ""
        For i = 2 To Cln - 1
            f1 = f1 & "&"","" & """""""" &RC[" & i & "] &"""""""""
        Next
        f1 = "=" & Right(f1, Len(f1) - 6)
        Range("A1:A" & rws).FormulaR1C1 = f1
        Columns("A:A").Copy
        Range("A1").PasteSpecial xlPasteValues
        Columns("B:IV").Delete Shift:=xlLeft
    End Sub

    or you could use this one:
    Code:
    Sub ComaSeparatedText2()
    
        rws = Range("A2").End(xlDown).Row
        Cln = Range("A1").End(xlToRight).Column
        Range(Columns("A:A"), Columns("A:A").End(xlToRight)).Insert Shift:=xlToRight
        
        With Range(Cells(1, 1), Cells(rws, Cln))
            .FormulaR1C1 = "=""""""""& RC[" & Cln & "]&"""""""""
            .Copy
            .PasteSpecial xlPasteValues
        End With
        Range(Cells(1, Cln + 1), Cells(1, Cln * 2)).EntireColumn.Delete Shift:=xlLeft
    End Sub
    To have all cells added quotation marks at begin and end of the each cell value, and after that just save it as comma separated values.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/740384

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice