Powershell Text to Column

Question

I have a csv file which contains all data in 1 column.

This is the format,

EPOS SKU      QTY ReferenceNr
---- ---      --- -----------
717  30735002 1   S04-457312 
700  30777125 1   S06-457360 
700  25671933 1   S06-457389 
716  25672169 1   S09-457296 
716  25440683 1   S09-457296

I would like to separate those data into 4 columns with these following headers and save/export to csv or xlsx via powershell script.

Thank you for your help

If that is the actual raw text from the file then someone created it with something like `... | Format-Table | Out-File`. While your sample data could be converted to an actual CSV with relative ease, the better option would be to fix the data export where the file is created (use `Export-Csv` instead of writing a formatted table to a text file). — Ansgar Wiechers, Sep 04 '19 at 12:05
Possible duplicate of [Split string on arbitrary-length substrings (Powershell)](https://stackoverflow.com/questions/55731241/split-string-on-arbitrary-length-substrings-powershell) — Jeff Zeitlin, Sep 04 '19 at 12:14

score 0 · Answer 1 · answered Sep 04 '19 at 14:36

This should work:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$inputFile  = $PSScriptRoot + '\rawtext.txt'
$csvFile    = $PSScriptRoot + '\rawtext.csv'
$excelFile  = $PSScriptRoot + '\rawtext.xlsx'

# Create datatable
$dt = New-Object system.Data.DataTable
[void]$dt.Columns.Add('EPOS',[string]::empty.GetType() )
[void]$dt.Columns.Add('SKU',[string]::empty.GetType() )
[void]$dt.Columns.Add('QTY',[string]::empty.GetType() )
[void]$dt.Columns.Add('ReferenceNr',[string]::empty.GetType() )

# loop file
foreach($line in [System.IO.File]::ReadLines($inputFile))
{
    if( $line -match '^\d+' ) {

        $contentArray = $line -split ' +'

        $newRow = $dt.NewRow()
        $newRow.EPOS        = $contentArray[0]
        $newRow.SKU         = $contentArray[1]
        $newRow.QTY         = $contentArray[2]
        $newRow.ReferenceNr = $contentArray[3]
        [void]$dt.Rows.Add( $newRow )
    }
}

# create csv
$dt | Export-Csv $outputFile -Encoding UTF8 -Delimiter ';' -NoTypeInformation

#create excel
$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $false
$excel.DisplayAlerts = $false
[void]$excel.Workbooks.Open($csvFile).SaveAs($excelFile, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)
[void]$excel.Workbooks.Close()
[void]$excel.Quit()

# remove csv
Remove-Item -Path $csvFile -Force | Out-Null

score 0 · Answer 2 · edited Sep 04 '19 at 23:32

0

With the Export-Csv instead of Format-Table solved.

$ftr = Get-Content -Path $pathfile |
       select -Skip 1 |
       ConvertFrom-Csv -Delimiter '|' -Header 'Detail', 'LineNr', 'EPOS', 'SKU',
           'SKUName', 'QTY', 'StoreName', 'Contact', 'ReferenceNr' |
       Select-Object -Property EPOS, SKU, QTY, ReferenceNr |
       Export-Csv -Path $target$ArvName.csv -NoTypeInformation

edited Sep 04 '19 at 23:32

Ansgar Wiechers

193,178
25
254
328

answered Sep 04 '19 at 15:37

GeorgeG

1
1
2

score -1 · Answer 3 · answered Sep 04 '19 at 12:24

-1

If your question is regarding Excel... (It is not clear for me) Just rename the file from *.csv to *.txt and open it on Excel.

On the Text Assistant choose "My data has headers" and "Delimited" and it will be correctly imported with each data on one column. As you ask for.

Later on, save as csv or xlsx.

answered Sep 04 '19 at 12:24

David García Bodego

1,058
3
13
21

Sorry I mean this is a part of powershell script. You are right, but I have to creat a formatted/separated excel file as I mentioned and send via email. – GeorgeG Sep 04 '19 at 12:33

Powershell Text to Column

3 Answers3