CODEWITHSUNDEEP
excelpowershell
user2109307
user2109307

Reputation: 123

Excel functions in powershell

I am new to Powershell and I need to parse text file into excel and edit content.

I managed to open file with the following script: 

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Excel.DisplayAlerts = $false
$Excel.Workbooks.OpenText( "Documents\myfile.txt")

It runs without exception, but the data I'm left with tells me the file is not loaded properly.

I need to set parameters like FieldInfo for export.

How do I do this?

Thanks for help.

Upvotes: 0

Views: 513

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

When you bring data into Excel via text file or manual input, Excel will try to interpret what type of data it is, text, date or number. You are bringing in text that looks like a number to Excel.

One solution is to make Powershell do all the work, get it to load the file and then loop through each row. You can then force the format before loading the data.

Something like this:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$wb = $Excel.Workbooks.add()
$ws = $wb.Worksheets[1]

Import-Csv "Documents\myfile.txt" | % {$i = 1}{

  $ws.Range("A" + $i).NumberFormat = '@'
  $ws.Range("A" + $i).Value = $_.Field1

  #Repeat for each field

  $i++
)

Upvotes: 0

Ranadip Dutta
Ranadip Dutta

Reputation: 9173

You have to get the item from the file in order to load it in excel. DO like this: 

$excel = new-object -comobject excel.application
$file = get-item "E:\myfile.txt" # Mention the path of the file
$excel.Visible=$true
$excel.displayalerts = $False
$wb = $excel.workbooks.open($file)

Hope it helps you.

Upvotes: 1

Related Questions