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 to quoted text file

Discussion in 'Business Applications' started by caznan, Dec 27, 2010.

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

    caznan Thread Starter

    Joined:
    Oct 21, 2009
    Messages:
    10
    I need to convert an excel file to a quoted comma-delimited format as a text file. When I use the formula:
    =concatenate("""",A1,"""",",","""",B1,"""",",","""",C1,"""",), I get an error message "error 528'. I was told in a previous forum to use this formula in the first cell next to the numbers to be converted.
    I have 1000 rows of 3 colums of numbers that have to read as follows:
    "1","400100000311","52"
    I can get it to a text format without quotes, but need the quotes as above.
    I'm running windows 7, and open office.
    Thanks for any help you can give me.
     
  2. TerryNet

    TerryNet Moderator

    Joined:
    Mar 23, 2005
    Messages:
    78,267
    First Name:
    Terry
    I moved your thread because I think Business Applications is more appropriate than Networking.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If you type a " into D1 (and drag it all the way down, so all 1000 rows have a " in D) and then enter this formula into a further column (E1 or wherever) I think you will get what you need:
    =(D1&A1&D1&","&D1&B1&D1&","&D1&C1&D1)
    (The & works to combine text - makes it easier to track than the concatenate formula and does the same thing.)

    Oh, I assumed that A1 had 1 in it, B1 had 400100000311 in it, and C1 had 52 in it in order to get the final outcome of "1","400100000311","52"
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    If Slurpee's suggestion doesn't resolve your issue please post a sample file and we'll get you taken care of.

    Rollin
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I just reread post 1 - sometimes open office treats formulas differently than Excel, which I made that formula in, but it usually is just a preference for having semi-colons instead of commas in certain places. However, if it doesn't work for you, I have OO on my PC at home and can tinker with it there.
     
  6. caznan

    caznan Thread Starter

    Joined:
    Oct 21, 2009
    Messages:
    10
    I got to the point of creating it on the excel spreadsheet and the columns looked good; however, when I converted it to a csv file then to a text file, something was lost in the translation. I think I need steps.
    Thanks for your work so far.
    Maybe open office would work better?
     
  7. caznan

    caznan Thread Starter

    Joined:
    Oct 21, 2009
    Messages:
    10
    I used the formula, it looked right. Then I copied the quoted fields and pasted it on to notepad. That also looked OK, but it wouldn't import into the software program, saying "1" is an invalid number. I must have missed a step???
     
  8. caznan

    caznan Thread Starter

    Joined:
    Oct 21, 2009
    Messages:
    10
    I'm attaching the excel file to be converted to the quoted comma delimited text file as below:

    "1",400100000311",52" as an example
     

    Attached Files:

  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Either Excel or OO will allow you to sae the file as you uploaded it as a CSV/text file, without needing a formula at all. I opened your file in OO and saved it as a CSV - see attached.
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, the site doesn't like csv files - I had to zip it in order to upload it. :rolleyes:
     

    Attached Files:

  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is a macro that can be run in Excel. Just change the text in red to reflect your desired save path and filename. The code assume the row 1 contains the first record and only the first three columns used (A,B,C) exactly as your sample shows. I've included the sample output from the file you attached earlier.

    Code:
    Sub SaveText()
    
    Dim fso, OutputFile
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set OutputFile = fso.CreateTextFile("[COLOR="Red"]C:\YourFilename.txt[/COLOR]", True)
    
    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    
    vString = Chr(34) & Range("A" & i).Value & Chr(34) & "," & _
    Chr(34) & Range("B" & i).Value & Chr(34) & "," & _
    Chr(34) & Range("C" & i).Value & Chr(34)
    
    OutputFile.WriteLine (vString)
    
    Next i
    
    OutputFile.Close
    
    End Sub
    Rollin
     

    Attached Files:

  12. caznan

    caznan Thread Starter

    Joined:
    Oct 21, 2009
    Messages:
    10
    By using the formula Slurpee55 suggested, converting it to a csv file, then opening it in Notepad....it worked and imported seamlessly.
    Thanks
     
  13. 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/971043

  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