Solved

Need a Powershell Script to Covert .csv to .xlsx dcouments

Posted on 2014-02-06
26
679 Views
Last Modified: 2014-02-14
Greeting Experts,

Does anybody have a script that can change the extension of excel file from “.csv” to “.xlsx “.  I have a list of excel documents that I manually change daily (i.e.  open, and save with .xlsx) but that requires me to at  work every day… the script needs to look at creation date on documents properties, change only that document (for that day), and then delete .csv file .  Does anybody have a script that can do this?
0
Comment
Question by:amstoots
  • 12
  • 5
  • 5
  • +1
26 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39839430
Get-Item -path x:\your\path\*.csv | Where-Object {$_.LastWriteTime -gt ((Get-Date).AddDays(-1))} | %{Rename-Item -path $_.FullName -NewName ($_.FullName.Substring(0,$_.FullName.Length-3) + 'xlsx')}

Open in new window


This will rename all csv files from today to a xlsx extensions

HTH,
Dan
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39839432
The only way is to do exactly what you do per script. That is, read the CSV into Excel, and store it as xlsx. This can be done in VBS and PS.
I do not understand your date restriction - do you mean the CSV file needs to be created today or yesterday or such?
0
 

Author Comment

by:amstoots
ID: 39839565
I receive daily reports in "report.csv" format and need to covert them over to "report.xlsx" format. I don't need for the script to to do the entire list in Network Folder. Just the one that has been received for that day (i.e. Monday, Tuesday, Wed, etc)..... Just so the script does not have to long period of time. Then I want the script to delete the .csv file once the excel document has been covered over .xlsx
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 69

Expert Comment

by:Qlemo
ID: 39839628
Still not getting it. If the file is always "reports.csv", it is overwritten the next day if not processed, or if processed does not exist any more (since it has been deleted).
set-StrictMode -Version latest
$file = 'C:\Daily Reports\reports'

$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.Workbooks.Open($file + '.csv')
$wb.SaveAs($file + '.xlsx')
$excel.Quit()
remove-item $file + '.csv'

Open in new window

0
 

Author Comment

by:amstoots
ID: 39839792
Thanks Dan Craciun, it works perfect
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39839796
Yes, it does rename the file. But can you actually open it?
Cause an xlsx file is an archive (rename it to zip and you'll see) and the csv is a text file.

I don't think Excel will like your newly renamed file...
0
 

Author Comment

by:amstoots
ID: 39839915
I was able to run the script to covert the file from .csv to .xlsx using the same name and then delete the .csv file....   Original File name did not change
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39840047
That cannot work! You will get complaints by Excel about having the wrong format. As stated, xlsx is a completely different format from CSV.
0
 

Author Comment

by:amstoots
ID: 39840062
I will have to take back what I said... it did change the ext. of the file but it did not convert the file.... now they will not open up... error message  " the file format or file extension is not valid. Verify the file has not been corrupted and the file extension matches the format of the file.
0
 

Author Comment

by:amstoots
ID: 39840066
I jumped the gun too soon.... sorry about that Qlemo.....  Is there a way to modify this script to covert it over...?
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39840078
You asked for
a script that can change the extension of excel file from “.csv” to “.xlsx “

My script does that, but that was not what you needed.

I have not tested Qlemo's script, but it looks like it should give you what you need.
0
 

Author Comment

by:amstoots
ID: 39840122
what I need is to covert a .csv formatted file over to .xlsx formated excel file that i am able to open .... sorry i was not more clear..... thanks for your help head time on this issue...
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 39840533
I'm confused, didn't the last PS1 script from Qlemo do just that?  I was getting ready to fashion one in VBS when I saw that and thought we were all set.

~bp
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39840563
Yes, Bill, but the PS1 script had some flaws. This is the improved one:
set-StrictMode -Version latest
$file = 'C:\temp\ee\reports'

if (Test-Path "$file.csv")
{
  Remove-Item "$file.xlsx"
  $excel = New-Object -ComObject excel.application
  $excel.visible=$true
  $wb = $excel.Workbooks.Open("$file.csv")
  $wb.SaveAs($file, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook)
  $wb.Close()
  $excel.Quit()
  Remove-Variable wb, excel
  Remove-Item "$file.csv"
}

Open in new window

It does not throw any error, runs only if there is a reports.csv file, and is able to overwrite the existing reports.xlsx file.

The VBS code would be almost the same.
0
 

Author Comment

by:amstoots
ID: 39840636
I ran the script on a test folder with a few .csv files and did not see anything change. did not get any error messages as well...


set-StrictMode -Version latest
$file = 'C:\test'

if (Test-Path "$file.csv")
{
  Remove-Item "$file.xlsx"
  $excel = New-Object -ComObject excel.application
  $excel.visible=$true
  $wb = $excel.Workbooks.Open("$file.csv")
  $wb.SaveAs($file, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook)
  $wb.Close()
  $excel.Quit()
  Remove-Variable wb, excel
  Remove-Item "$file.csv"
  }

Open in new window

0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39840641
That's because the script looks for C:\test.csv

Make $file a parameter and run the script with the -file option.

HTH,
Dan
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
ID: 39840664
Param (
[string]$file = 'C:\test'
)

$filesToConvert = Get-Item -path ($file + "\*.csv") | Where-Object {$_.LastWriteTime -gt ((Get-Date).AddDays(-1))}
foreach ($fileToConvert in $filesToConvert)
{
  $excel = New-Object -ComObject excel.application
  $excel.visible=$true
  $wb = $excel.Workbooks.Open("$fileToConvert")
  $wb.SaveAs(($fileToConvert.FullName.Substring(0,$fileToConvert.FullName.Length-3) + 'xlsx'), [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook)
  $wb.Close()
  $excel.Quit()
  Remove-Variable wb, excel
  Remove-Item $fileToConvert
  }

Open in new window


This is Qlemo's script, modified to look for ALL csv files from today and convert them to xlsx, then delete them.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 39840696
As the folder is (probably) static, there is no need for the parameter - and if, it should be named $file. Further, $fileToConvert is a FileInfo object, and not the file name any longer - that needs some more work now.

If you really want to look for all files created today:
$folder = 'C:\Test'

$today = (get-date).Date
$excel = New-Object -ComObject excel.application
$excel.visible=$true

Get-Item $folder -include '*.csv' |
  ? { $_.LastWriteTime.Day -eq $today }  |
  % { 
    Remove-Item ($folder + '\' + $_.BaseName + '.xlsx')
    $wb = $excel.Workbooks.Open($_)
    $wb.SaveAs($folder + '\' + $_.BaseName, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook)
    $wb.Close()
    Remove-Item $_
  }
$excel.Quit()
Remove-Variable wb, excel

Open in new window

0
 

Author Comment

by:amstoots
ID: 39840784
I can say both of you came up with a solution that works (prof of concept) for my needs... It does cover the files from .csv to .xlsx file and then deletes the original .csv file once the first step is done...

Param (
[string]$file = '\\Network Share\folder1'
)

$filesToConvert = Get-Item -path ($file + "\*.csv") | Where-Object {$_.LastWriteTime -gt ((Get-Date).AddDays(-5))}
foreach ($fileToConvert in $filesToConvert)
{
  $excel = New-Object -ComObject excel.application
  $excel.visible=$false
  $wb = $excel.Workbooks.Open("$fileToConvert")
  $wb.SaveAs(($fileToConvert.FullName.Substring(0,$fileToConvert.FullName.Length-3) + 'xlsx'), [Microsoft.Office.Interop.Excel.xlFileFormat]::xlOpenXMLWorkbook)
  $wb.Close()
  $excel.Quit()
  Remove-Variable wb, excel
  Remove-Item $fileToConvert
  }

Open in new window

0
 

Author Comment

by:amstoots
ID: 39853445
I've requested that this question be closed as follows:

Accepted answer: 250 points for Qlemo's comment #a39840696
Assisted answer: 0 points for amstoots's comment #a39840636

for the following reason:

thank you
0
 

Author Comment

by:amstoots
ID: 39853446
thank you
0
 

Author Comment

by:amstoots
ID: 39853571
yes... thanks...
0
 

Author Closing Comment

by:amstoots
ID: 39860898
Thanks,....for the help on this problem.....guys... :)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

"Migrate" an SMTP relay receive connector to a new server using info from an old server.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question