Excel functions in powershell

Question

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.

It loads columns with ID-values as numbers and that means loss of inital zeroes in ID numbers. For example employee number 00051 is loaded as employee number 51. — user2109307
– user2109307, Commented Dec 7, 2016 at 14:36

Ranadip Dutta · Accepted Answer · 2016-12-07 14:33:02Z

1

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.

answered Dec 7, 2016 at 14:33

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2109307 Over a year ago

How can I set column type as text before loading data? I have problem with excel auto-casting column as number instead of text

Ranadip Dutta Over a year ago

Share the text file and tell what exactly you want to get displayed in Excel. Its very generic to answer that without having the actual data. The sample I posted its just to load the data from the text file. You can use it for any file type

user2109307 Over a year ago

Imagine input like this: pastebin.com/eBcNpuwy. Inital zeroes are lost when its parsed into excel. I need to specify 1st column as text. How can I do that ?

Ranadip Dutta Over a year ago

I saw the data in pastebin. So what you can do is. Take the data as it is and load in the excel using the script I have given. It will populate the data in the single column. Now go to the Excel , go to Data pane , click on Text to Columns. Split using the delimiter Space. I think that is what you are looking for. Even if you want you can save it as csv and you can use the content as import-csv...

Dave Sexton · Accepted Answer · 2016-12-07 16:17:15Z

0

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++
)

answered Dec 7, 2016 at 16:17

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

Collectives™ on Stack Overflow

Excel functions in powershell

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related