Admin Power Level: Intermediate Chapter 15: Small Commands, Big Everyday Wins
CSV: import and export
CSV files are tables in text form. Import-Csv turns rows into objects, and Export-Csv turns objects into rows. Together, they connect PowerShell to spreadsheets and to other tools.
Lesson 5 of 5 in Chapter 15: Small Commands, Big Everyday Wins. Previous step in this path: Host, files, time, and keyboard. 3 learning objectives, 6 examples, and a knowledge check.
Start: what you will learn
CSV files are tables in text form. Import-Csv turns rows into objects, and Export-Csv turns objects into rows. Together, they connect PowerShell to spreadsheets and to other tools.
Learning objectives
- Import a CSV file into objects
- Export objects to a CSV file
- Explain why headers and delimiters matter
Concept in simple words
What it is: a CSV file is a table stored as text, where each line is one row and commas, or sometimes semicolons, separate the columns. The letters CSV stand for comma separated values, but in real life you will also meet semicolon files, especially from tools that use a comma as a decimal mark. Import-Csv turns each row into an object, with properties named after the headers, and Export-Csv turns objects into rows, with headers taken from the property names.
What problem it solves: PowerShell works with objects, and spreadsheets work with tables, and CSV is the simple bridge between them. Without CSV, moving a list from PowerShell to a spreadsheet, or from a spreadsheet back into PowerShell, would mean retyping or copy pasting, with errors at every step. With CSV, you can prepare a list by hand in a spreadsheet, import it, act on it in a loop, and export the result, all without retyping a single row.
What PowerShell does behind Import-Csv: it opens the file, reads the first row as headers, and then builds one object per later row. Each column becomes a property of that object, and each cell becomes the value of that property, so a row is no longer just text, it is something you can filter, sort, and select like any other object. That is why you can write Import-Csv and then pipe to Where-Object or Select-Object, and the commands just work: they are working on objects, not on lines of text.
What PowerShell does behind Export-Csv: it does the reverse. It takes objects from the pipeline, reads their property names, writes those names as the header row, and then writes one row per object, with one value per property. The order of the columns follows the order of the properties you selected, so Select-Object before Export-Csv is how you control which columns appear, and in which order. If you skip Select-Object, you get every property, including many you did not want in a spreadsheet.
Headers, the most important row: the header row decides every property name in the imported objects. A spelling mistake in a header becomes a wrong property everywhere, so a filter looking for Department will find nothing if the header says Departement. If a header entry is empty, PowerShell inserts a default header name and shows a warning, which is your clue that the file needs fixing before you act on it. If a file has no header row at all, Import-Csv will treat the first row of real data as if it were headers, unless you give it headers yourself with -Header, which is shown in a later example.
Delimiters and encoding, the two quiet settings: the delimiter is the character that separates columns, and the default is a comma. If your file uses semicolons, you must say so with -Delimiter ";", or PowerShell will read each whole row as one single column, and your table will look broken. Encoding is how text characters are stored in the file, and Import-Csv uses the byte order mark in the file to detect it, falling back to UTF8 when there is no mark. If a file shows garbled characters for names with accents, the delimiter or the encoding is the first place to check, not the data itself.
Values are text, even when they look like numbers: every value Import-Csv reads arrives as text, so a column of numbers is really a column of number shaped text. That matters when you sort or compare, because text sorting puts "10" before "2", since it compares character by character, while number sorting puts 2 before 10. If you need real number sorting or maths, convert the value first, for example by casting it to a number, and only then sort or calculate. The same idea applies to dates: a date column arrives as text, so sort it as a date only after you have turned it into a real date value.
Think of it like this
Think of it like this: a class list on paper. The top row names the columns, such as Name and Department, and every row below is one student. Importing the list turns each row into a card you can sort and filter, and exporting turns your cards back into a neat paper list that a spreadsheet can open. If the top row is misspelled, every card gets the wrong label, and if the columns are separated by semicolons but you read them as commas, every card ends up with one long jumbled label instead of neat fields.
How it works, step by step
How it works, step by step, for import: First, open the CSV file and check the header row, because the headers become the property names, and a spelling mistake there becomes a wrong property everywhere. Second, check the delimiter by looking at one row: are the values separated by commas, semicolons, or tabs. Third, import with Import-Csv and preview the result with Select-Object, showing only a few rows and a few properties, using -First to limit the preview. Fourth, only act on the imported objects after that preview looks right, because one bad header changes every row.
How it works, step by step, for export: First, get the objects you want, such as processes or services. Second, use Select-Object to keep only the properties you want in the file, in the order you want them, because those properties become the columns. Third, pipe to Export-Csv with -Path for the file, and always add -NoTypeInformation, so PowerShell leaves out the extra type header line it adds by default, and other tools read the file cleanly. Fourth, open the exported file, or import it back, and check that the headers and the first rows look right before you send it on.
How a round trip works: export a list, edit it by hand in a spreadsheet, save it as CSV again, import it back, preview it, and then act on it, for example by creating or changing accounts in a loop. The round trip only stays safe if the headers survive the spreadsheet edit unchanged, and if the delimiter stays the same. If the spreadsheet changes a header spelling, or saves with a different delimiter, the import back will look wrong, and the preview step is where you catch that, before any changes are made.
Let us walk through the first example: take Import-Csv -Path "C:\Temp\users.csv" | Select-Object Name, Department -First 2. Import-Csv reads the file at -Path and returns one object per row. The pipe sends those objects to Select-Object, which keeps only the Name and Department properties, and -First 2 limits the preview to two rows. In the output, look for a table with Name and Department as column headings, and check that the values under them look right, because a bad header or a stray comma will show up here before it causes damage. If you change -First 2 to -First 5, you simply preview five rows instead of two, which is useful when the first two rows happen to look fine but a later row is broken.
Let us walk through the second example: take Get-Process | Select-Object Name, CPU -First 3 | Export-Csv -Path "C:\Temp\procs.csv" -NoTypeInformation. Get-Process lists running processes as objects. Select-Object keeps only Name and CPU, and -First 3 limits the export to three rows, so the file stays small while you practise. Export-Csv writes those objects to a CSV file at -Path, and -NoTypeInformation leaves out the extra type header line, so a spreadsheet opens the file with Name and CPU as the first row, not a strange type line. If you leave -NoTypeInformation out, the first row of the file will be a type line, and a spreadsheet will show it as if it were data.
Let us walk through the third example: take Get-Service | Select-Object Name, Status -First 2 | Export-Csv -Path "C:\Temp\services.csv" -NoTypeInformation. Get-Service lists services as objects. Select-Object keeps only Name and Status, and -First 2 limits the export to two services, using the same select and export pattern as the process example in the lesson. Export-Csv writes those objects to a CSV file at -Path, and -NoTypeInformation leaves out the extra type header line so a spreadsheet reads the file cleanly. The pattern is the same in both examples on purpose: get objects, select properties, limit rows while practising, export without type information.
Let us walk through the fourth example: take Import-Csv -Path "C:\Temp\users.csv" -Delimiter ";" | Select-Object Name, Department -First 2. This is the same preview as the first example, except -Delimiter ";" tells Import-Csv that semicolons, not commas, separate the columns. If you leave -Delimiter out for a semicolon file, each row arrives as one single property, with the whole row crammed into it, and Select-Object Name will show nothing useful, because there is no Name property, only one jumbled property. If you change ";" to "`t" for a tab, you get the same idea for tab separated files, where the backtick plus t means a tab character.
Let us walk through the fifth example: take Import-Csv -Path "C:\Temp\users.csv" | Where-Object { $_.Department -eq "IT" } | Export-Csv -Path "C:\Temp\it-users.csv" -NoTypeInformation. Import-Csv turns rows into objects, Where-Object keeps only the objects where the Department property equals IT, using $_ to mean the current object in the pipeline, and Export-Csv writes only those kept rows to a new file, again without type information. In the output, look for a file that holds only IT rows, not the whole list, and check the row count after, because a filter with a misspelled property name keeps nothing, and an empty file is the clue that the property name was wrong.
Let us walk through the sixth example: take Import-Csv -Path "C:\Temp\plain.csv" -Header "Name", "Department" | Select-Object Name, Department -First 2. -Header supplies the property names yourself, as a list, with a comma between each name. Use this only when the file truly has no header row, because -Header treats the first row of the file as data, not as headers. If the file already has a header row and you add -Header anyway, the original header row becomes a data row, and you will see a row with Name and Department as values, which is the clue that you should not have used -Header for that file.
When to use it
When to use it, and when not to: use CSV when you need to move a list between PowerShell and a spreadsheet, or when you need to prepare a list, edit it by hand, and import it back to create or change accounts in a loop. Do not act on an imported CSV without previewing it first, because one bad header changes every row. Do not export formatted output, such as the result of Format-Table, to CSV, because formatting objects are not your data, and the CSV will contain formatting details instead of the values you wanted. Do not use CSV for deeply nested data either, because a property that holds a complex object will not store its inner details usefully in one CSV cell, so pick simple properties first.
Edge cases to watch for: a stray comma inside a value, such as a name with a comma in it, can shift columns unless the value is quoted, and PowerShell handles quoted values for you, but a hand edited file can break that quoting. Duplicate headers give you duplicate property names, which makes selecting one of them confusing, so fix the headers in the file instead of working around them. Appending with -Append adds rows to an existing file, but exporting twice without -Append replaces the file, so a second export can wipe the first one if you meant to add. Always check whether you meant to replace or to append, just as you do with Set-Content and Add-Content.
Worked examples
Read each example in order. Each one explains its parts, then shows what to look for in the output.
Syntax
Import-Csv -Path <File> | Export-Csv -Path <File> -NoTypeInformationExample 1
Reads rows as objects, then shows two properties from the first two rows as a preview. -Path names the file, Select-Object keeps only Name and Department, and -First 2 limits the preview, so a bad header or a stray comma shows up here before it causes damage.
Import-Csv -Path "C:\Temp\users.csv" | Select-Object Name, Department -First 2Illustrative output: Name Department ---- ---------- Izaz IT
Example 2
Writes three process rows to a CSV file, without the type header line. Get-Process gives objects, Select-Object keeps Name and CPU and limits to three rows, and Export-Csv writes them to -Path, with -NoTypeInformation leaving out the extra type line so a spreadsheet reads the file cleanly.
Get-Process | Select-Object Name, CPU -First 3 | Export-Csv -Path "C:\Temp\procs.csv" -NoTypeInformationIllustrative output: CSV written to C:\Temp\procs.csv.
Example 3
Get-Service lists services as objects. Select-Object keeps only Name and Status, and -First 2 limits the export to two services, using the same select and export pattern as the process example in the lesson. Export-Csv writes those objects to a CSV file at -Path, and -NoTypeInformation leaves out the extra type header line so a spreadsheet reads the file cleanly.
Get-Service | Select-Object Name, Status -First 2 | Export-Csv -Path "C:\Temp\services.csv" -NoTypeInformationIllustrative output: Name,Status Spooler,Running
Example 4
-Delimiter ";" tells Import-Csv that semicolons separate the columns, instead of the default commas. Without it, a semicolon file arrives as one jumbled property per row, and Select-Object Name shows nothing useful. Change ";" to "`t" for a tab separated file, where backtick plus t means a tab.
Import-Csv -Path "C:\Temp\users.csv" -Delimiter ";" | Select-Object Name, Department -First 2Illustrative output: Name Department ---- ---------- Izaz IT
Example 5
Import-Csv turns rows into objects, Where-Object keeps only rows where Department equals IT, with $_ meaning the current object, and Export-Csv writes only those rows to a new file, without type information. If the result file is empty, check the property spelling first, because a misspelled property keeps nothing.
Import-Csv -Path "C:\Temp\users.csv" | Where-Object { $_.Department -eq "IT" } | Export-Csv -Path "C:\Temp\it-users.csv" -NoTypeInformationIllustrative output: CSV written to C:\Temp\it-users.csv.
Example 6
-Header supplies property names yourself, as a list, for a file with no header row. Use it only for a truly headerless file, because -Header treats the first row as data. If the file already has headers and you add -Header anyway, the original header row shows up as a data row, which is the clue to drop -Header.
Import-Csv -Path "C:\Temp\plain.csv" -Header "Name", "Department" | Select-Object Name, Department -First 2Illustrative output: Name Department ---- ---------- Izaz IT
Nothing on this page executes PowerShell code. The outputs above are illustrative only.
Common mistakes and troubleshooting
Read these before you try the examples, so you can spot the same problems in your own work.
- Exporting without -NoTypeInformation, then wondering why a spreadsheet shows a strange first row.
- Importing a CSV without checking its headers, then acting on properties that do not exist. Preview first.
- Piping formatted output, such as Format-Table, into Export-Csv, so the CSV file ends up full of formatting details instead of the real service or process data you wanted to save.
- Reading a semicolon or tab file with the default comma delimiter, so each row arrives as one single jumbled property, and every later select or filter finds nothing. Match -Delimiter to the file, and preview first.
- Using -Header on a file that already has a header row, so the original header row becomes a data row. Use -Header only for a truly headerless file.
- Exporting twice without -Append when you meant to add rows, so the second export replaces the first file and the first rows are lost. Decide replace or append first, just as with Set-Content and Add-Content.
- Treating imported values as numbers or dates when they are really text, so "10" sorts before "2". Convert a value to a number or a date first, before you sort it or do maths on it.
Knowledge check
Knowledge check
What does Import-Csv return?
Pick one answer. Feedback appears right away, and you can try again.
Remember this and next step
Remember this: headers become properties, rows become objects, preview before you act, and export with -NoTypeInformation. Treat the header row as the most important row in the file, because everything else follows from it, match the delimiter to the file, remember that values arrive as text, and never pipe formatted output into Export-Csv. With those habits, CSV becomes a reliable bridge, not a source of silent mistakes.
Next step: The Safe Enumeration Mindset