csv to xlsx

Post Reply
User avatar
Terkelsen
Member
Posts: 161
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

csv to xlsx

Post by Terkelsen » Wed Jul 11, 2018 11:47 am

I need a simple script to convert osv to xlsx and I do have the scripting module for Switch. Any help?

jan_suhr
Member
Posts: 136
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: csv to xlsx

Post by jan_suhr » Wed Jul 11, 2018 12:17 pm

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: 254
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander » Wed Jul 11, 2018 12:38 pm

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()
Part of my playground:
- HP Indigo 12.000 | HP Indigo 10.000 | HP Indigo 7600's | HP Production Center
- Scodix Ultra 2 Pro | Highcon Euclid III | CP Bourg booklet maker (inline w/ 7600)

User avatar
Terkelsen
Member
Posts: 161
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

Re: csv to xlsx

Post by Terkelsen » Thu Jul 12, 2018 9:13 am

Hi Sander,

Excuse my ignorence but could you share your setup for Execute command when using this script?

sander
Advanced member
Posts: 254
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander » Thu Jul 12, 2018 8:26 pm

Sure:
Edit: wrong script, this is the correct one:
Image
Part of my playground:
- HP Indigo 12.000 | HP Indigo 10.000 | HP Indigo 7600's | HP Production Center
- Scodix Ultra 2 Pro | Highcon Euclid III | CP Bourg booklet maker (inline w/ 7600)

User avatar
Terkelsen
Member
Posts: 161
Joined: Thu Sep 08, 2011 5:08 pm
Contact:

Re: csv to xlsx

Post by Terkelsen » Fri Jul 13, 2018 9:19 am

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: 254
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: csv to xlsx

Post by sander » Fri Jul 13, 2018 11:11 am

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

Settings are slightly different.
Part of my playground:
- HP Indigo 12.000 | HP Indigo 10.000 | HP Indigo 7600's | HP Production Center
- Scodix Ultra 2 Pro | Highcon Euclid III | CP Bourg booklet maker (inline w/ 7600)

Post Reply