Unable to SaveAs CSV file

Question

I am trying to save a CSV file using PowerShell, but I am unable to do so. I have tried various ways and spent a lots of hours but still I can't figure out why I am not able to save the resultant file. Below is the sample code for the same.

$file1 = "C:\Users\vicky\desktop\SourceFile.csv"     # CSV Source file  
$xl = new-object -c excel.application  
$xl.Visible = $true
$xl.DisplayAlerts = $false 
$wb=$xl.workbooks.open($file1)
$ws=$wb.worksheets.item("SourceFile")                # Sheet name
$ws.activate 
$rng = $ws.Cells.Item(1,6).EntireColumn
$rng.select    #selecting range
$filterval = "Y"   
$xl.selection.AutoFilter(9,$filterval)               # Applying filter on 9th column from left of the source file
$ws.SaveAs("C:\Users\vicky\Desktop\newFile.csv")     # Save filtered output as newFile
$wb.close()
$xl.quit()
$a= get-process -name EXCEL | select -expand Id      # Finding Process Id of Excel 
stop-process $a                                      # To kill Excel Process

I need to apply filter on a column and then store the filtered file with a new file name. I am able to apply filter but can't save the output to another CSV file.

Do you get an error? Does it create a file at all? It seems to work for me except the Excel wants to recover the contents of the file because a format wasn't specified. Is that the issue? — user2040424
– user2040424, Commented Jan 11, 2015 at 2:38
No I am not receiving any error. It filters the data and show required displays output momentarily but then it doesn't save it to the newFile.csv and the contents of newFile.csv remains same as that of Sourcefile.csv. — VickyRoch
– VickyRoch, Commented Jan 11, 2015 at 3:12
I can see the required output if I comment last four lines of the code i.e. #$wb.close() #$xl.quit() #$a= get-process -name EXCEL | select -expand Id #stop-process $a however when these lines are brought back into action then the filtered result is lost :( — VickyRoch
– VickyRoch, Commented Jan 11, 2015 at 3:35

Ansgar Wiechers · Accepted Answer · 2015-01-11 11:31:00Z

0

I don't think it's possible to save just the visible cells. You need to copy them to a new sheet and export that. Also, you should always specify the file format if you want to export to a particular format, otherwise the output file will be created using the default format (usually the format of the open file).

Change this:

$xl.selection.AutoFilter(9,$filterval)
$ws.SaveAs("C:\Users\vicky\Desktop\newFile.csv")
$wb.close()

into this:

$xl.selection.AutoFilter(9,$filterval)
$ws2 = $wb.Sheets.Add()
$ws.UsedRange.SpecialCells(12).Copy()
$ws2.Paste()
$ws2.SaveAs("C:\Users\vicky\Desktop\newFile.csv", 6)
$wb.Saved = $true
$wb.close()

As a side note: don't use Stop-Process to terminate the Excel instance. The proper way is to release the COM object:

$xl.Quit()
[Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

edited Jan 11, 2015 at 11:31

answered Jan 11, 2015 at 11:13

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to SaveAs CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related