Convert txt file to xlsx

Welcome Forums General PowerShell Q&A Convert txt file to xlsx

Viewing 3 reply threads
  • Author
    Posts
    • #211179
      Participant
      Topics: 2
      Replies: 5
      Points: 32
      Rank: Member

      Hello,

      Hit my next roadblock.

      I am trying to take a text file, that seems to be delimited by a random number of spaces and convert to xlsx.

      In Excel the normal txt import doesn’t work on Office 265, I have to use the legacy import to text in Office 365.

      I downloaded Doug’s module, but am having problems finding how I read the text file in and export to xlsx. Tried finding some documentation to help but could not.

      Can someone point me in the right direction?

       

      Thanks in advance,

      -og

    • #211182
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      that seems to be delimited by a random number of spaces and convert to xlsx.

      Are you sure it is a random number of spaces? It could be tab delimited as well.

      If this is the case you can import the csv with something like this:

      Import-Csv -Path D:\sample\sample1.csv -Delimiter "`t"

      If it’s actually a random number of spaces you could convert the spaces into proper delimiter charachters with the operator -replace … something like this:

      Get-Content -Path D:\sample\mycsvfile.csv | 
          ForEach-Object{$_ -replace "\s+",';'} | 
              ConvertFrom-Csv -Delimiter ';'

      For the future: most of the times it would be helpful to see the code you already have and a few sanitized but still representative sample lines of the source data you’re dealing with – formatted as code as well. 😉 That would make it possible to get an idea of what you’re dealing with and would reduce wild guesses. 😉

    • #211281
      Participant
      Topics: 2
      Replies: 5
      Points: 32
      Rank: Member

      Noted 🙂 Will try to put an example in next time.

      This just about worked on the first try (your first option was closer). I figured out that the file is “fixed width” which is why the legacy import wizard works and the newer wizard with office 365 does not.

      Does Doug’s module support the older import type? I’m googling around to to try and find out

       

      -og

    • #211287
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      I figured out that the file is “fixed width” which is why the legacy import wizard works and the newer wizard with office 365 does not.

      For fixed width strings you could use the .substring() and .trim() method to cut out the desired columns.

      Does Doug’s module support the older import type?

      I don’t know. I actually never needed to work with it. You might simply try it. 😉

      What’s hard to believe is that Micrsoft cutted such a simple function like the import of fixed width csv files in newer versions of Office. I just tested it with my installation and it’s still there …

Viewing 3 reply threads
  • You must be logged in to reply to this topic.