csv to xlsx
csv to xlsx
I need a simple script to convert osv to xlsx and I do have the scripting module for Switch. Any help?
Re: csv to xlsx
There are different CLI scripts on the net that can be run through "Execute Command". You probably need to use my app "Execute command Friend" to get it to work.
It is tricky since the XSLX format is a form of XML so it is easier to use something that already is done for this.
It is tricky since the XSLX format is a form of XML so it is easier to use something that already is done for this.
Re: csv to xlsx
I'm using Powershell with execute command for it, it works like a charm.
Code: Select all
#
# Script: Save CSV as XLSX
#
# Last change: 28-10-2014, Sander - Wihabo
#
# Sources: http://stackoverflow.com/questions/17688468/how-to-export-a-csv-to-excel-using-powershell
#
param(
[string]$fileinput = "", # Switch geeft %1 mee als input variabele: %1: the absolute path of the input file/job folder
[string]$fileoutput = "" # Switch geeft %2 mee als output variabele: %2: the absolute path of the output file/job folder (which does not yet exist) including filename and extension
)
### Set input and output path
#$fileinput = "c:\users\test\desktop\00001.csv"
#$fileoutput = "c:\users\test\desktop\00001.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;" + $fileinput)
$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 = ","
### 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($fileoutput,51)
$excel.Quit()
Re: csv to xlsx
Hi Sander,
Excuse my ignorence but could you share your setup for Execute command when using this script?
Excuse my ignorence but could you share your setup for Execute command when using this script?
Re: csv to xlsx
Sure:
Edit: wrong script, this is the correct one:
Edit: wrong script, this is the correct one:
Re: csv to xlsx
Thanks for sharing but you are writing:
Edit: wrong script, this is the correct one:
...and then there is a screendump of you Execute command settings. That's very helpfull, but was there supposed to be an updated script attached as well?
Edit: wrong script, this is the correct one:
...and then there is a screendump of you Execute command settings. That's very helpfull, but was there supposed to be an updated script attached as well?
Re: csv to xlsx
Haha excuse me. I only made a screenshot of my xlsx to csv script instead of csv to xlsx.
Settings are slightly different.
Settings are slightly different.