Powershell to add extra column when doing re-order of column field

Hi all,
I have following powershell script that I use to re-order column, its actually works pretty fine and fast.  There is  few changes that I need to work on this scripts.

1: How do I introduce new column in output file although existing file did not have those column ? I need to add two more column between $data[4], $data[7], $data[6], I also need to add column name header in this new column  since the rest of the column has column header.




$ImportFile="DWH_Import_File.csv"
$ImportDate = (Get-Date).AddDays(-1).ToString('yyyy-MM-dd')
$DWHFile = "DWH_Import_File_$ImportDate"
$DWHFile1 ="$ImportFile-Convert.csv"
$Import = [System.IO.File]::OpenText("F:\DWH\HRISEMP\$ImportFile")
$DWHdata = New-Object System.IO.StreamWriter "F:\DWH\HRISEMP\Tool\$DWHFile1"
for(;;) {
    $line = $Import.ReadLine()
    if ($null -eq $line) {
        break
    }
    $data = $line.Split(",")
    $DWHdata.WriteLine('{0},{1},{2},{3},{4},{5},{6},{7},{8}', $data[0], $data[8], $data[5], $data[4], $data[7], $data[6], $data[2], $data[1], $data[3])
}

$Import.Close()
$DWHdata.Close()

Open in new window

motioneyeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
It's not 100% clear where you want the new columns but this will add a new column named NewCol1 between $data[4] and $data[7] and a new column named NewCol2 between $data[7], $data[6].

for(;;) {
    $line = $Import.ReadLine()
    if ($null -eq $line) {
        break
    }
    $data = $line.Split(",")
    $DWHdata.WriteLine('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}', $data[0], $data[8], $data[5], $data[4],'NewCol1',  $data[7],'NewCol2', $data[6], $data[2], $data[1], $data[3])
}

$Import.Close()
$DWHdata.Close()

Open in new window

1
motioneyeAuthor Commented:
Hi Norie,
yes its working, but how   if I dont want the column header to be printed as column values ? right now 'NewCol1' and 'NewCol2' also get printed at each row as  column values.
0
NorieAnalyst Assistant Commented:
Good point.

What needs to be done is to split things up a little and write the header separately from the data.
$ImportFile="DWH_Import_File.csv"
$ImportDate = (Get-Date).AddDays(-1).ToString('yyyy-MM-dd')
$DWHFile = "DWH_Import_File_$ImportDate"
$DWHFile1 ="$ImportFile-Convert.csv"
$Import = [System.IO.File]::OpenText("F:\DWH\HRISEMP\$ImportFile")
$DWHdata = New-Object System.IO.StreamWriter "F:\DWH\HRISEMP\Tool\$DWHFile1"

$header = $Import.ReadLine()
$data = $header.Split(",")

$DWHdata.WriteLine('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}', $data[0], $data[8], $data[5], $data[4],'NewCol1',  $data[7],'NewCol2', $data[6], $data[2], $data[1], $data[3])

for(;;) {
    $line = $Import.ReadLine()
    if ($null -eq $line) {
        break
    }
    $data = $line.Split(",")
    $DWHdata.WriteLine('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}', $data[0], $data[8], $data[5], $data[4],'',  $data[7],'', $data[6], $data[2], $data[1], $data[3])
}

$Import.Close()
$DWHdata.Close()

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
motioneyeAuthor Commented:
Thanks Norie :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.