[Solved] Exporting: excel to comma delimited text file

    Hi,


    I got some great help from this forum before, but have hit another problem:


    I would like to convert an excel s/sheet containing a LONG list of URLs into a comma delimited text file, such that:


    URL1
    URL2
    URL3
    URL4


    becomes a .txt file:


    URL1,URL2,URL3,URL4


    Any suggestions (other than by hand!) would be enormously appreciated.


    BTW: The reason for this is that I wish to feed these URLs into fping - a multiple hostname ping program, and thus I will be wanting to convert the output back into excel.



    Thanks,


    Mike

    Under the drop-down menu in the save as dialog there is an option to save as a comma delimited or tab delimited file. I don't know if that's what you were looking for and just couldn't find it, or if this doesn't help you at all.


    Kabong

    Thanks Kabong - I have got that far, but it still doesn't give me the output in the "URL,URL,URL" format that i need... ?


    Perhaps I am missing something?

    Hi Mike,


    To get the output you want, your URL's all have to be in 1 row instead of 1 column. The CSV format treats each row as a new record, but writes each cell in a row as a single record separated by commas.


    To convert you column of data to a row:
    1. Make sure there is nothing in Row 1 (insert a row if neeed to make that one blank)
    2. Highlight the range of your URL's (I'm assuming they will be in cells A2:A### - as long as there are not more than 256 of them you're OK)
    3. Click on Copy
    4 Select Cell A1
    5 Click on Edit / Paste Special - click the box next to Transpose - and click OK


    Data should now be in Row 1.


    Delete the duplicate data in Col A, then save the file in comma delimited format.


    Hope this helps


    .....Ralph

    That is great Ralph.


    BUT....


    There are more than 256 of them. There are 22,000 of which 4,800 are unique URLs. The spreadsheet is 25MB and just opening it takes five minutes...


    :(


    Thanks VERY much for your input. Any other ideas?

    That will be a big file :yes:


    The following macro will create a csv file like you describe by reading and writing each value in Col A. (You'll have to adjust the range to meet your requirements)





    .....Ralph

    That is really good of you Ralph. Thanks.



    Right before I give you the gold star, could you just <ahem&gt; remind me what to do with the macro?


    If I goto Tools &gt; Macros &gt; Record New Macro


    and then copy and paste the (corrected) code, will that just fire up automatically?


    Apologies for my ignorance, I had no idea excel was quite so versatile. I am actually doing my analysis in Stata - a command line package, but find excel is good (and getting better) for manipulation.


    Thanks again.:yes:

    Happy to help.


    Can't use copy/paste with the Record a Macro.....


    In your workbook, press Alt-F11 to open the Visual Basic Editor. In the "Project" pane, click once in the tree where it says VBA Project (yourworkbookname). On the top menu, click on Insert / Module. Module1 should appear and be highlighted inthe Project pane and you should have a blank code pane on the right.


    Copy the macro code from this thread and paste it in the blank window, make any mods you need to the code and close the VBE window.


    When you go to Alt-F8 from your workbook, you should get a macro listing with the new macro there. If you save the workbook, the macro will be saved as well.


    .....Ralph

    Gah!


    Now I really am showing my ignorance...


    Your instructions were so clear, I have carried them out to the letter. Unfortunately, macros have been disabled for security purposes.


    I am logged into Office XP as administrator, and in excel have gone to tools &gt; macros &gt; security and reduced it to low (for the time being)


    When I then goto tools &gt; macros and try to run the macro, it still says:


    "macros in this workbook are disabled because the security level is high, and the macro has not been dgitally signed or verified safe..."


    Thanks for all this hand holding, I really appreciate all the help I get from this forum.

    < EDIT..&gt; That really is fast! Thankyou very much for all your help. Solved:o </EDIT...&gt;





    Okay,


    The trusty Microsoft Close-then-Reopen routine worked, and macro status is now medium.



    However, Alt-F8 brings up the macro, and clicking "run" gives me the hourglass for all of two seconds, then nothing.


    If it has actually created the file (which would make it the fastest excel function ever), then where is it?


    If not, any ideas?


    Here is the modified (slightly) code:


    ---------------------------------------
    Sub TextFileWrite()
    Dim myrng As Range
    Set myrng = Range("B2:B21020") 'CHANGE THIS RANGE TO MEET YOUR SPECS

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fs, f, ts, s
    Dim cellv As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CreateTextFile "C:\urlfile.csv" 'You can change the output name to anything you want
    Set f = fs.GetFile("C:\urlfile.csv")
    Set ts = f.OpenAsTextStream(ForWriting, TristateFalse)

    For Each cell In myrng
    cellv = cell.Value
    ts.Write (cellv & Chr(44))
    Next cell

    ts.Close

    End Sub
    ----------------------------------


    Cheers,


    Mike:o

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login