CODEWITHSUNDEEP
excelpowershellcsvtexttxt
edward zabinski
edward zabinski

Reputation: 39

Convert TXTto CSV

I'm working on a project where I need to take a text file and make it an excel file. So far what I've come up with is this.

cls

Remove-Item -path D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv

Add-Content -path D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv -Value '"Part_Number","Cost","Price"'

$csvPath = 'D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv'


#region Excel Test
If (test-path HKLM:SOFTWARE\Classes\Word.Application) {
    Write-host "Microsoft Excel installed"
} else {
    Write-host "Microsoft Excel not installed"
}
#endregion

#region Patterns
$mainpattern1 = '(?<Partnumber>\d*\s*\w*,)(?<Cost>\d*.\d*),(?<Price>\d*.\d*)'
$mainpattern2 = '(?<Part_number>\d*-\d*-\d*),(?<Cost>\d*.\d*),(?<Price>\d*.\d*)'

#endregion

get-Content 'D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv' | #grabs the content
    Select-String -Pattern $mainpattern1, $mainpattern2 | #selects the patterns
    Foreach-Object {
        $Part_Number, $Cost, $Price = $_.Matches[0].Groups['Part_number', 'Cost','Price']



        [PSCustomObject] @{
     
     part_number = $Part_Number
     Cost = $Cost
     Price = $Price

   
    }
    $objResults | Export-Csv -Path $csvPath -NoTypeInformation -Append
    
}

some sample data is here

00001143 SP,136.41,227.35
00001223 SP,48.66,81.10
00001236 SP,149.72,249.53
0001-0003-00,100.95,168.25
00015172 W,85.32,142.20

I'm getting the file created and the header values are correct but I'm not sure how to get the values to import.

Upvotes: 1

Views: 66

Answers (2)

postanote
postanote

Reputation: 16106

Continuing from my comment... with the resources and a simple example.

Find-Module -Name '*excel*' | Format-Table -AutoSize
# Results
<#
Version     Name            Repository Description                
-------     ----            ---------- -----------                
7.1.1       ImportExcel     PSGallery  PowerShell module to import/export Excel spreadsheets, without Excel....                  
0.1.12      PSWriteExcel    PSGallery  Little project to create Excel files without Microsoft Excel being installed.             
1.0.2       PSExcel         PSGallery  Work with Excel without installing Excel        
...    
0.6.9       ExcelPSLib      PSGallery  Allow simple creation and manipulation of XLSX file                  
2.1         Read-ExcelFile  PSGallery  PowerShell module to import Excel spreadsheets, without Excel....    
...
#>

MSExcel will read properly formatted CSV's natively. So, to convert to a true XLS file, use PowerShell, MSOffice COM to open MSExcel with the CSV file, then save it as an XLS format.

$FileName = "$env:temp\Report"

Get-Process | 
Export-Csv -UseCulture -Path "$FileName.csv" -NoTypeInformation -Encoding UTF8

$excel         = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open("$FileName.csv").SaveAs("$FileName.xlsx",51)
$excel.Quit()

explorer.exe "/Select,$FileName.xlsx"

Your use case is of course as noted:

Import-Csv -Path 'D:\temp\book1.txt' -header Title, Author

Then using COM as noted above.

Upvotes: 1

edward zabinski
edward zabinski

Reputation: 39

I ended up solving this after I asked the question I had alot of flaws in this code

Add-Content -path D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv -Value '"Part_Number","Cost","Price"'

$csvPath = 'D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\MainWarehouse.csv'


#region Excel Test
If (test-path HKLM:SOFTWARE\Classes\Excel.Application) {#these next few lines will check if excel is installed on the system
    Write-host "Microsoft Excel installed"
} else {
    Write-host "Microsoft Excel not installed"
}
#endregion

#region Patterns
$mainpattern1 = '(?<Part_number>\d*\s*\w*),(?<Cost>\d*.\d*),(?<Price>\d*.\d*)'#These two line will use REGEX to help seperate the data
$mainpattern2 = '(?<Part_number>\d*-\d*-\d*),(?<Cost>\d*.\d*),(?<Price>\d*.\d*)'

#endregion

get-Content 'D:\Users\zabin\OneDrive\Desktop\ITS3410\WEEK8\Main.rtf' | #grabs the content
    Select-String -Pattern $mainpattern2, $mainpattern1 | #selects the patterns
   Foreach-Object {
        $Part_number, $Cost, $Price = $_.Matches[0].Groups['Part_number', 'Cost','Price']     #Gets the groups of a call to select-string



  $results = [PSCustomObject] @{#the list here is what i use to seperate the data onto the CSV file
     part_number = $Part_Number
     Cost = $Cost
     Price = $Price

   
    }
    
$results | Export-Csv -Path $csvPath -NoTypeInformation -Append #moves the results to the CSV file
    
}

Upvotes: 1

Related Questions