How do I Import a Text file to Excel via Powershell

You can try this:

$XL.workbooks.Open($file)

But that does not always work, Especially if you have some delimiter that you want to use.
I opened up Excel and recorded a macro to see how Excel does it.
Here is the Macro it recorded with some editing:
 

# Sub Macro2()
# ‘
# ‘ Macro2 Macro
# ‘
#
# ‘
#     Workbooks.OpenText Filename:=”Z:\MBSA_ReportExcluded.txt”,
#     Origin:=437, _
#     StartRow:=1,
#     DataType:=xlDelimited,
#     TextQualifier:=xlDoubleQuote, _
#     ConsecutiveDelimiter:=[True,False],
#     Tab:=True,
#     Semicolon:=False,
#     Comma:=False,
#     Space:=False,
#     Other:=False,
#     FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
#     TrailingMinusNumbers:=True
# End Sub

I looked up the Excel constants (xlDelimited, xlDoubleQuote) and they are both equal to 1.
So I tried several different ways in Powershell and this one works for a Tab delimited file.
 

$oXL = New-Object -comobject Excel.Application
$oXL.Visible = $true
$oXL.DisplayAlerts = $False
$file = “Z:\MBSA_ReportExcluded.txt”
$oXL.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$False,$False)

The first $True sets whether you want consecutive delimiters to be treated as one.

(This is useful mostly with space delimited files.)

The last five $True/$False are the ones that select the delimiter.

#  1   Tab:=True,
#  2   Semicolon:=False,
#  3   Comma:=False,
#  4   Space:=False,
#  5   Other:=False,

Advertisements

7 thoughts on “How do I Import a Text file to Excel via Powershell

  1. it would be great to see how to save this kind of file. because just $oXL..ActiveWorkbook.saveas(“location”) – save it as txt

      • HI
        this script is interesting but i want the content from a text file as rows and columns in a excel file. the above script copies the content from notepad to excel in a single row.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s