locked
Export Txt file with no delimiter to csv file RRS feed

  • Question

  • I have a text file whose format is similar to:

    computername  userid filename

    Just whitespace in between. I need to split the content and export into a CSV file for further use

    Thursday, August 22, 2013 7:34 PM

Answers

  • The other way is to just open it in Excel. Go to the data tab and click text to columns and use a Space as the delimiter. Then you can just save it as a CSV file.

    Thank you,

    Wheres the fun in that lol

    Here is another version which would check for mulitple spaces

    Get-Content <pathToFile> | ForEach-Object {
      $new = $_ -replace "\s{1,}", ","
      Out-File -InputObject $new -FilePath C:\Temp\newfile.csv -Append
    }


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Don't Retire Technet


    • Edited by clayman2 Thursday, August 22, 2013 7:57 PM edit
    • Proposed as answer by Mike Laughlin Thursday, August 22, 2013 8:00 PM
    • Marked as answer by AnnaWY Friday, August 30, 2013 2:03 PM
    Thursday, August 22, 2013 7:52 PM

All replies

  • if it is just one space you could try

    Get-Content <pathToFile> | ForEach-Object {
      $new = $_.Replace(" ", ",")
      Out-File -InputObject $new -FilePath C:\Temp\newfile.csv -Append
    }


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Don't Retire Technet



    • Edited by clayman2 Thursday, August 22, 2013 7:58 PM edit
    Thursday, August 22, 2013 7:39 PM
  • The other way is to just open it in Excel. Go to the data tab and click text to columns and use a Space as the delimiter. Then you can just save it as a CSV file.

    Thank you,

    Thursday, August 22, 2013 7:43 PM
  • The other way is to just open it in Excel. Go to the data tab and click text to columns and use a Space as the delimiter. Then you can just save it as a CSV file.

    Thank you,

    Wheres the fun in that lol

    Here is another version which would check for mulitple spaces

    Get-Content <pathToFile> | ForEach-Object {
      $new = $_ -replace "\s{1,}", ","
      Out-File -InputObject $new -FilePath C:\Temp\newfile.csv -Append
    }


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Don't Retire Technet


    • Edited by clayman2 Thursday, August 22, 2013 7:57 PM edit
    • Proposed as answer by Mike Laughlin Thursday, August 22, 2013 8:00 PM
    • Marked as answer by AnnaWY Friday, August 30, 2013 2:03 PM
    Thursday, August 22, 2013 7:52 PM
  • Sorry the above script works with the replacement but not with the creation of the csv file. If you open it up in notepad it looks fine, but in excel it looks as one string of text. Looking into it.

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Don't Retire Technet

    Thursday, August 22, 2013 8:10 PM
  • Can you show us a part of that file?

    Thursday, August 22, 2013 11:26 PM
  • Hi,
    Just whitespace in between. I need to split the content and export into a CSV file for further use
    How much whitespace are we talking about? Also, is it consistent throughout the file?

    Don't retire TechNet!

    Friday, August 23, 2013 12:06 AM
  • Hi,

    this would work regardless of the amount of whitespaces:

    (Get-Content $txtPath -Raw) -replace '[^\S^\r\n]{1,}',',' | Set-Content ([IO.Path]::ChangeExtension($txtPath,".csv"))


    • Edited by Dirk_74 Friday, August 23, 2013 6:56 AM forgot parenthesis
    Friday, August 23, 2013 6:35 AM
  • Kind of late but this is what I found, tested and did. It was an AD query to pull all machines in the domain, export to excel for checklist building.

    At first all my data was returned in one column, A1, B1, C1 and it was a mess.

    <powershellquerycode> | select name, operatingsystem,operatingsystemservicepack | EXPORT-CSV c:\blah\blah\blah\filename.csv -notypeinformation -USECULTURE

    It was -USECULTURE that fixed it for me.

    Good Luck friends.

    Friday, May 6, 2016 7:14 PM