PowerShell is a cross-platform task automation and configuration management framework, consisting of a command-line shell and scripting language. Unlike most shells, which accept and return text, PowerShell is built on top of the .NET Common Language Runtime (CLR), and accepts and returns .NET objects. This fundamental change brings entirely new tools and methods for automation.
Let’s write script to import CSV data inot an Excel using Powershell. Given example kept in a file named as “OpenCSV.ps1“
$csv = Get-Item $args $excel = New-Object -ComObject excel.application $excel.visible = $true $workbook = $excel.Workbooks.Add() $worksheet = $workbook.worksheets.Item(1) $worksheet.Name = $csv.basename $arrFormats = ,2 * $worksheet.Cells.Columns.Count $TxtConnector = ("TEXT;" + $csv.fullname) $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1")) $query = $worksheet.QueryTables.item($Connector.name) $query.TextFileOtherDelimiter = $Excel.Application.International(5) $query.TextFileParseType = 1 $query.TextFileColumnDataTypes = $arrFormats $query.AdjustColumnWidth = 1 $query.Refresh() $query.Delete() Remove-Item variable:arrFormats [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers()
This script can be called using various methods but I always thing through automated solutions and trying to call the same using a Batch file as shown below, Put following code in a text file and save it as .bat
Powershell -Command "D:\Folder_Path\OpenCSV.ps1" %1
Look through your .bat file at saved location and double click it. That’s all it works.