Save txt file as excel

Your PowerGUI Community: Simplify your PowerShell related GUI and script editing needs.


Save txt file as excel

This question is not answered
Hi all,

I have seen plenty of posts on how to open a tab delimited file in excel, but I need help on how to save that excel file.  Is there a way to convert the txt file to excel without powershell actually opening excel?

Also I am going to possibly import a new txt file daily into excel so does anyone have tips on how to create a new tab with the current date when I import the text file?  Thanks!
All Replies
  • There are lots of ways to do things like this. It all depends upon how your text file is oganized. For example, I often look at SMTP log files for specific information (I used to use LogParser, but PowerShell is easier for me than SQL syntax). Here's how I convert a raw log file into a CSV file that Excel will open natively.

    SMTP log files begin with a number of lines beginning with "#". In Exchange 2003 and earlier, the 4th one has the list of fields in the data (it's the 5th starting with 2007). So, this will get the column headers, then the data, then export it as a CSV file (note that the field separator for this file format is a space):

    #    Sample E2K3 SMTP Log file header:
    #Software: Microsoft Internet Information Services 6.0
    #Version: 1.0
    #Date: 2010-02-23 06:00:07
    #Fields: date time c-ip cs-username s-sitename s-computername s-ip s-port cs-method cs-uri-stem cs-uri-query sc-status sc-bytes cs-bytes time-taken cs-version cs-host cs(User-Agent)

    $file = ex100223.log
    $Headers = (Get-Content $file)[3].Substring(9) # get the header line minus "#Fields: "
    $RawData = @($Headers) # start an array with the headers
    $RawData += Get-Content $file | Where_Object {$_ -notMatch "^`#"} # add the non-comment lines
    $RawData | ConvertFrom-CSV -Delimiter " " | Export-CSV ex100223.csv -NoTypeInformation # convert to comma separated and export

    Double-click on the exported file and it will open in Excel.

  • Since I mentioned the Exchange 2007 SMTP logs, you could do the same, but the offset to the field list is 4, and the delimeter is "," (which is the default, so you can remove the -delimiter phrase). Unlike the 2003 logs, where there's a single log file per day, there are separate SEND and RECV  2007 logs (and potentially multiple of each) for a singe day. Here's a semple 2007 file header:

    #Software: Microsoft Exchange Server
    #Version: 8.0.0.0
    #Log-type: SMTP Receive Protocol Log
    #Date: 2010-02-23T00:00:24.366Z
    #Fields: date-time,connector-id,session-id,sequence-number,local-endpoint,remote-endpoint,event,data,context