csv to xlsx

Post Reply
User avatar
Terkelsen
Advanced member
Posts: 297
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

csv to xlsx

Post by Terkelsen »

I need a simple script to convert osv to xlsx and I do have the scripting module for Switch. Any help?
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: csv to xlsx

Post by jan_suhr »

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.
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
sander
Advanced member
Posts: 274
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander »

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()
User avatar
Terkelsen
Advanced member
Posts: 297
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

Re: csv to xlsx

Post by Terkelsen »

Hi Sander,

Excuse my ignorence but could you share your setup for Execute command when using this script?
sander
Advanced member
Posts: 274
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander »

Sure:
Edit: wrong script, this is the correct one:
Image
User avatar
Terkelsen
Advanced member
Posts: 297
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

Re: csv to xlsx

Post by Terkelsen »

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?
sander
Advanced member
Posts: 274
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander »

Haha excuse me. I only made a screenshot of my xlsx to csv script instead of csv to xlsx.

Settings are slightly different.
Post Reply