lundi 20 avril 2015

Copying an entire column from one CSV file to another using Powershell

I have two CSV files: File1.csv has one column with 4000+ rows. File2.csv has 200 columns with 10000+ rows of content. I want to add the one column in file1.csv as an additional column on File2.csv. I am OK adding it to the end (rightmost) of the existing file. I have found several options online, but none has worked as desire. I can get it done with the Input-CSV cmdlet and adding a Property but that is taking more than ~1 hour to execute. Is there any way to do this without having to convert the CSV content into PSobjects? I have used Get-Content and Set-Content in the past, but that will append one file to the bottom of the other one. Is there any way I could do something similar but appending to the right of the existing file?

Here is the piece of code that has gotten me closer to what I need. The problem with this one is Excel is not saving or closing. Any ideas on how this problem can be solved either by fixing the code below or an easier/more efficient way to do it?

$source = "C:\Users\Desktop\Script_Development\04-16-2015\Bit.csv"
$dest =  "C:\Users\Desktop\Script_Development\04-16-2015\MergedwithHeader_04-16-2015.csv"
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $false
$Workbooksource = $excel.Workbooks.open($source) 
$Worksheetsource = $Workbooksource.WorkSheets.item("Bit") 
$Worksheetsource.activate()  
$range = $Worksheetsource.Range("A1").EntireColumn 
$range.Copy() | out-null 

$Workbookdest = $excel.Workbooks.open($dest) 
$Worksheetdest = $Workbookdest.Worksheets.item("MergedwithHeader_04-16-2015") 
$Range = $Worksheetdest.Range("FT1") 
$Worksheetdest.Paste($range)  
$Workbookdest.SaveAs("C:\Users\Desktop\Script_Development\04-16-2015\MergedwithHeader_04-16-2015.xls")
$Excel.quit()

Aucun commentaire:

Enregistrer un commentaire