Powershell Script to convert from csv to excel and then rename sheet1 the same as file name -


i have 2 scripts, , wanted know if can:

  1. combine them
  2. rename sheet1 file name

the issue running 1 excel in folder other based on specific names of individual excel files.

code 1 converts csv excel documents

$workingdir = "c:\users\user\desktop\output\*.csv" $csv = dir -path $workingdir foreach($inputcsv in $csv){ $outputxlsx = $inputcsv.directoryname + "\" + $inputcsv.basename + ".xlsx" ### create new excel workbook 1 empty sheet $excel = new-object -comobject excel.application  $excel.displayalerts = $false $workbook = $excel.workbooks.add(1) $worksheet = $workbook.worksheets.item(1)  ### build querytables.add command ### querytables same when clicking "data » text" in excel $txtconnector = ("text;" + $inputcsv) $connector = $worksheet.querytables.add($txtconnector,$worksheet.range("a1")) $query = $worksheet.querytables.item($connector.name)  ### set delimiter (, or ;) according regional settings ### $excel.application.international(3) = , ### $excel.application.international(5) = ; $query.textfileotherdelimiter = $excel.application.international(5)  ### set format delimited , text every column ### trick create array of 2s used preceding comma $query.textfileparsetype  = 1 $query.textfilecolumndatatypes = ,2 * $worksheet.cells.columns.count $query.adjustcolumnwidth = 1  ### execute & delete import query $query.refresh() $query.delete()  ### save & close workbook xlsx. change output extension excel 2003 $workbook.saveas($outputxlsx,51) $excel.quit() } ## exclude item, use '-exclude' parameter (wildcards if needed) remove-item -path $workingdir -exclude *crab4dq.csv 

script 2 re-names sheet1 whatever pass (currently 1 excel documents @ time)

$xlspath = "c:\users\user\desktop\output\test.xlsx" $xldoc = new-object -comobject excel.application $xldoc.visible = $false $xldoc.displayalerts = $false $workbook = $xldoc.workbooks.open($xlspath ) $worksheet = $workbook.worksheets.item(1) $worksheet.name = "result" $workbook.save() $workbook.close() $xldoc.quit() 

i in 1 step, , have every excel document have sheet1 (they ever have 1 sheet) named same document named. can done?

thanks in advance!

list item

check out importexcel - powershell module import/export excel spreadsheets, without excel

you can , more importexcel

to convert csv excel , name worksheet result, run command

import-csv filename.csv | export-excel filename.xlsx -worksheetname 'result' 

to convert csv files in folder .xlsx file , have name of each excel worksheet result use command below

ls *.csv | foreach-object {   import-csv $_.name | export-excel ($_.basename + ".xlsx") -worksheetname 'result' } 

Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -