Row 1 and row 3 are empty - Oracle export to Powershell

André Borgeld 431 Reputation points
2020-12-24T12:04:12.61+00:00

I have an Oracle export where the first row is empty and the third row is empty. this is because these are two queries exporting
I would like to solve this in powershell so i've used the code below:

But he is messing up the results and only comes back with one column (MAIL) and the error: Warning one or more headers where not specified (and comes back with an H above the row Mail)
Well the headers are in row 2, exactly like this:

Empty row (or line if you want to call it)
Mail;User;Room;
Empty row
;a.borgeld@Karima ben .com;Andre;202
;mborgeld@something .nl;Michel;204

Code:
Import-Csv -Path "C:\Temp\oracle.csv" -Encoding UTF8 -Delimiter ';' | Where-Object { $_.PSObject.Properties.Value -ne '' } | Export-Csv -Path "C:\Temp\importWindows.csv" -Encoding UTF8 -Delimiter ';' -NoTypeInformation

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,546 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. André Borgeld 431 Reputation points
    2020-12-24T12:10:27.313+00:00

    I could leave the headers from oracle and build them in powershell, but maybe there is a solution for this.

    0 comments No comments

  2. André Borgeld 431 Reputation points
    2020-12-24T12:29:20.31+00:00

    Solved with simple replace

    $pathToCsv = 'export.csv'
    
    $content = Get-Content -Path $pathToCsv
    
    $content -notmatch '(^[\s,-]*$)|(rows\s*affected)' | Set-Content -Path $pathToCsv
    
    0 comments No comments

  3. Rich Matheisen 46,806 Reputation points
    2020-12-24T23:51:23.813+00:00

    Your problem is that the populated rows begin with the character ";" which is the column delimiter. That means that the populated rows contain 4 columns but the header row contains only three columns. The Import-CSV is adding a "H1" column name to accommodate this.

    See if this helps:

    get-content c:\junk\text.csv |
        Select-Object -skip 1 |
            ForEach-Object{
                $_.TrimStart(';')
            } | Out-File c:\junk\Text2.csv
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.