locked
Split one CSV into multiple RRS feed

  • Question

  • Hello! I am pretty new to Powershell, but am off to a good start!

    I've tried to find an answer to this online but haven't come up with anything yet. 

    I have a CSV file with about 3000 rows. In one of the Row's (the 5th), it has a building code. 

    I want to split this CSV file up into multiple CSV files based on the building code. Meaning, I want the new CSV files to only contain the rows that have the same building code in column 5.

    I am guessing I'd create an array with the school code (without adding it into the array if it's already there), then using the export-csv command, but I am not sure exactly how to piece it all together. Any help would be appreciated!

    Monday, August 20, 2018 6:34 PM

Answers

  • Do you mean "in one of the columns" is a building code.

    A CSV has named columns.  JUst roup the CSV o  that column.

    Import-Csv file.csv |
         Group-Object BuildingCode |
         ForEach-Object{ $_.Group| Export-CSv $_.Name.Csv }


    \_(ツ)_/


    • Proposed as answer by jrv Monday, August 20, 2018 8:16 PM
    • Edited by jrv Monday, August 20, 2018 8:16 PM
    • Marked as answer by hellbus Monday, August 20, 2018 11:06 PM
    Monday, August 20, 2018 7:24 PM

All replies

  • Do you mean "in one of the columns" is a building code.

    A CSV has named columns.  JUst roup the CSV o  that column.

    Import-Csv file.csv |
         Group-Object BuildingCode |
         ForEach-Object{ $_.Group| Export-CSv $_.Name.Csv }


    \_(ツ)_/


    • Proposed as answer by jrv Monday, August 20, 2018 8:16 PM
    • Edited by jrv Monday, August 20, 2018 8:16 PM
    • Marked as answer by hellbus Monday, August 20, 2018 11:06 PM
    Monday, August 20, 2018 7:24 PM
  • Import the csv file using Import-Csv, pipe to Group-Object and group by the column 5 header, pipe to Foreach-Object and export each group to its own file using Export-Csv.
    Monday, August 20, 2018 7:25 PM
  • Import-Csv C:\temp\file.csv | 
        Group-Object BuildingCode | 
        ForEach-Object {
            $_.Group | Export-Csv -Path "C:\temp\$($_.Name).csv" -NoTypeInformation 
        }

    In addition to the code that jrv supplied, above code will output each group to its own csv, where the name of the csv file is equal to the buildingcode, instead of exporting to set file ($Name). The -NoTypeInformation will also remove any additional type information of the new csv files.


    Monday, August 20, 2018 8:04 PM
  • Thank you for the replies! That really helps! Would there be any way to export it to a file name of my choosing? I’d like to give the building codes a more meaningful file name. So what I’m looking for is the ability to split a csv into multiple, based on a single column, and give it a name of my choosing (aside from the building code). Thanks again for all your help.
    Monday, August 20, 2018 9:06 PM
  • The question was answered. Please mark the answer.

    You are no asking for customization and no asking a question.

    I recommend that you first learn PowerShell before trying to get others to write code for you.


    \_(ツ)_/

    Monday, August 20, 2018 9:08 PM
  • I'm sorry if you think I am trying to get people to write code for me. Re-reading my post I can see how you can think that, but I am trying to learn it. Like many others, I don't have to luxury of scouring books, or taking classes to come up with a solution that needs to happen ASAP, so I turn to these forums like these for assistance.

    I've been searching, and toying around with code that I have found to come up with a solution but have yet to do so. I may not have been too clear with my initial question, and for that I apologize. 

    If I need to open another thread, asking a more specific question based off of the answers provided I can do so, but I'm not looking for people to just write code for me.  All of the info provided is valuable and helps me learn Powershell, and I hope it helps others too.


    Monday, August 20, 2018 11:06 PM
  • You don't have the "luxury"??? Are you not able to read. Perhaps  you are blind or deaf and can't use a video tutorial.  It is very unfair and unprofessional to ask others to give you personal lessons.

    Here is a basic PowerShell tutorial that anyone form at least 11 years up can understand.  Learn PowerShell  

    Here is some food for though about professional forums:

    This forum is for scripting questions rather than script requests


    \_(ツ)_/

    Monday, August 20, 2018 11:10 PM
  • You also need to decode what you mean by give it a name of your choosing.

    Suggestion:  Think about how to write code that selects a file name.  After thinking it out open a new question with a clear statement of what you want to do.

    The first step to solving technical problems is to come up with a clear statement of the problem.  Start with that thought in mind.


    \_(ツ)_/

    Monday, August 20, 2018 11:15 PM
  • Thank you for your reply and the tutorial link. I'll watch it, in hopes that it will help. I'm over 11 (physically, anyway), so I should be good! Sorry if I ruffled your feathers.

    Thank you to the second moderator as well. I'll definitely try to form my questions a bit better in the future. 

    Monday, August 20, 2018 11:36 PM
  • My feathers are not ruffled and I am not trying to pick on you.  You will just do much better in technical forums if you try to learn the technology. As noted we cannot give you personal instruction and you should not waste people's time asking beginner questions that can be learned in tutorial or a book. 

    Once you comprehend the basics PowerShell will be fun and much less painful.


    \_(ツ)_/

    Monday, August 20, 2018 11:55 PM
  • If anybody is trying to do the same thing, I was able to accomplish this by tossing an if statement in the foreach-object statement.

    if ($_.Name -eq "BuildingCode") {
    $_.Group | Export-Csv -Path "C:\Temp\Building Name\Building Name.CSV" -NoTypeInformation }

    Thank you for your help!


    • Edited by hellbus Tuesday, August 21, 2018 2:15 PM
    Tuesday, August 21, 2018 2:14 PM