Import CSV into Excel using Power Shell Script

Import CSV into Excel using Power Shell Script

Power Shell:

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

Code Example:

$csv = Get-Item $args[0]

$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.

Leave a Reply

Your email address will not be published.