CODEWITHSUNDEEP
powershellcsvexport-to-csv
branedge
branedge

Reputation: 135

exporting to csv with powershell -edited

I'm reading in an csv file (list of students, school, birthdays, etc) creating their login names and exporting the data to another csv that will be imported into another system. Everything is working great except i can only get one line of data in my csv file (the last user name). i assume it is overwriting the same line each time. Doing this in powershell. help. Here's my code:

Add-PSSnapin Quest.ActiveRoles.ADManagement

#import list of students from hourly IC extract
$users = Import-Csv C:\Users\edge.brandon\Desktop\enrollment\mbcextract.csv | 
  where {$_.'grade' -ne 'PK' -and $_.'name' -ne 'Ombudsman' -and $_.'name' -ne 'Ombudsman MS' -and $_.'name' -ne 'z Transition Services' -and $_.'name' -ne 'z Home Services'}

if ($users) {
  foreach ($u in $users) {

    # sets middle name and initial to null so that variable does not cary over to next student if they have no middle name
    $middle= $null
    $mi= $null

    $first= ($u.'firstname')
    $last= ($u.'lastname')
    $middle= ($u.'middlename')
    $birth= ($u.'birthdate')
    $grade= ($u.'grade')
    $id= ($u.'studentNumber')
    $schoolid= ($u.'sch.number') 

    #Removes spaces, apostrophes, hyphens, periods, commas from name
    $first=$first.Replace(" ", "")
    $middle=$middle.Replace(" ", "")
    $last=$last.Replace(" ", "")
    $first=$first.Replace("'", "")
    $middle=$middle.Replace("'", "")
    $last=$last.Replace("'", "")
    $first=$first.Replace("-", "")
    $middle=$middle.Replace("-", "")
    $last=$last.Replace("-", "")
    $first=$first.Replace(".", "")
    $middle=$middle.Replace(".", "")
    $last=$last.Replace(".", "")
    $first=$first.Replace(",", "")
    $middle=$middle.Replace(",", "")
    $last=$last.Replace(",", "")

    # sets 1st and middle initial. also sets mmdd of birth
    $fi= $first.substring(0,1)
    $mi= $middle.substring(0,1)
    $mmdd =$birth.substring(0,4)

    #sets username and then makes sure it truncates anything after 20 characters
    $un= ($last + $fi + $mi +$mmdd)
    $un= $un.substring(0,20)
  }
}  **$users | 
  select $id,$un,$first,$last,$schoolid,$grade,$upn,"1"," ","0" | export-csv MBC.csv**

Remove-PSSnapin Quest.ActiveRoles.ADManagement

i found a mistake i changed the $users to $u ($u | select $id,$un,$first,$last,$schoolid,$grade,$upn,"1"," ","0" | export-csv mbc.csv -NoTypeInformation)

and added a break point step thru the code and if i open the csv file as the code is running the first line of the csv file populates correctly with each pass. it continues to write on the first line...I tried the append statement but it didn't help...how do u make it go to the next line in the csv file (ie write a line of data on row 1, go to row 2 write another line of data)

Upvotes: 1

Views: 497

Answers (2)

GaryKlineCC
GaryKlineCC

Reputation: 1

I know this is nearly two years old, but others might find this topic.

The problem here is that the export-csv will recreate the file each time it is called. Combine that with the fact that you are sending export-csv the variable values calculated for the last record in $users, and that would explain your results.

I found an article that outlines how to do what you're intending to do (I have a similar project that I'm starting on). Working with Custom Objects: https://technet.microsoft.com/en-us/library/ff730946.aspx

As in the article, you would create a array of custom objects, then load each object with property values from your calculated variable values, as you read through the objects in $users

At the end, you would export your array of objects (not $users or $un) using the export-csv command.

Upvotes: 0

Etan Reisner
Etan Reisner

Reputation: 81042

I can't provide a full answer (I don't have csv data to test with offhand and don't know the relevant bits of powershell well enough from memory to comment specifically) but at the very least part of the problem is likely that you are using the values of variables in your select statement and I can't imagine you actually meant to do that.

I imagine you meant to grab the properties with those names instead (select id,un,first,last,... instead of select $id,$un,$first,$last,...).

Upvotes: 1

Related Questions