• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 40
  • Last Modified:

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

0
motioneye
Asked:
motioneye
  • 2
  • 2
2 Solutions
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
motioneyeAuthor Commented:
Thanks Norie :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now