Import CSV data into Excel from CMD (example given in Powershell)

Question

I found a really good Powershell code on another post here on Stack Overflow. It's below; it loads .csv data in an .xlsx. It does exactly what I want. Is it possible to do this in CMD? I've been looking for this in CMD equivalent but I only keep finding the powershell version.

### Set input and output path
$inputCSV = "C:\Users\username\Desktop\LSGTransactions.CSV"
$outputXLSX = "C:\Users\username\Desktop\Book1.XLSX"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = 
$worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 
2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

If you already have what you need why changing it? You could even run a Powershell script from a CMD batch file ... — Olaf
– Olaf, Commented Mar 22, 2019 at 17:24
Oh...that's a good idea. I know that I can submit CMD code to the place where this code will be called; I wasn't sure how Powershell code would fit in. I didn't think about calling one from the other. Thanks. It would be nice for learning CMD better though. — PBJ
– PBJ, Commented Mar 22, 2019 at 17:28

Ben Ootjers · Accepted Answer · 2019-03-23 18:16:35Z

1

Generally COM Objects (Which Excel is in powershell) are not available in CMD

However a workaround could be to call a powershell script from the batch file, and pass the filenames as parameters.

powershell.exe yourscript.ps1

You may also need to set the excecution policy on your system, to enable running PS scripts (https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.security/set-executionpolicy?view=powershell-6)

Another kind of language supporting COM could be vbscript which you can run from

cscript.exe script.vbs

edited Mar 23, 2019 at 18:16

Ben Ootjers

3531 silver badge15 bronze badges

answered Mar 22, 2019 at 17:27

Sam Washington

6604 silver badges17 bronze badges

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

2 Comments

lit Over a year ago

To avoid unexpected output, using powershell -NoLogo -NoProfile -File .\yourscript.ps1 may be helpful.

Sam Washington Over a year ago

same with cscript //Nologo script.vbs

Collectives™ on Stack Overflow

Import CSV data into Excel from CMD (example given in Powershell)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related