{"id":512,"date":"2018-10-28T08:00:51","date_gmt":"2018-10-28T14:00:51","guid":{"rendered":"https:\/\/practicalpowershell.com\/?p=512"},"modified":"2020-03-14T10:41:39","modified_gmt":"2020-03-14T16:41:39","slug":"working-with-csv-files","status":"publish","type":"post","link":"https:\/\/practicalpowershell.com\/working-with-csv-files\/","title":{"rendered":"Working with CSV Files"},"content":{"rendered":"

\"\"<\/a>For this week’s tip we will review the use of CSV’s and PowerShell. While it is almost always better to not use CSV files for output or object manipulations, there are other uses of CSVs that can come in handy when working with PowerShell.

\nCSV Operations<\/em>

\nExporting a CSV<\/u>

\nOne csv operation we can perform is exporting to a CSV file. This file could be used for reporting purposes, historical data, for future conversion to HTML or even for future processing in this script or another script. Exporting to a CSV file can be as simple as this one liner:
\n[sourcecode language=”powershell”]
\n$Results | Export-CSV c:\\reporting\\results.csv
\n[\/sourcecode]
\nAnd can be as complex as specifying Append or NoClobber, choosing an encoding type, settings delimiter and more like this:
\n[sourcecode language=”powershell”]
\n$Results | Export-CSV -NoClobber -Delimiter "|" -NoInformation c:\\reporting\\results.csv
\n[\/sourcecode]
\nIt is important to note, that the parameters above do serve a purpose and could be key. Here is quick explanation of these options:

\nNoClobber – this ensures that when exporting to a CSV file, we do not overwrite and existing CSV file that is located at the exported location.
\nDelimiter – this can make it easier to work with especially if there are fields with standard delimiters like ‘,’
\nNoType – this is used to remove a line that is inserted into a file that contains information on the CSV file.

\nImporting a CSV<\/em>

\nPowerShell also has the option to import CSV data to be used by PowerShell cmdlets and scripts. Similar to the Export cmdlet, there are parameters and switches that can be of use when importing a CSV file.

\nDelimiter – this can make it easier to work with especially if there are fields with standard delimiters like ‘,’
\nEncoding – The encoding method that was used to create the original CSV file
\nHeader – To create an alternate set of column headers to replace the ones in the CSV file.

\nExamples<\/em>

\n[sourcecode language=”powershell”]
\n$MailboxData = Import-CSV c:\\reports\\mailboxdata.csv
\n$UsersToImport = Import-CSV c:\\reports\\users.csv -Delimiter "|"
\n[\/sourcecode]
\nOnce these files are now stored in variables, PowerShell could reference the variables for processing. Some quick examples:
\n[sourcecode language=”powershell”]
\nForeach ($Line in $MailboxData) {
\n<Insert Code here>
\n}
\n$UsersToImport | ConvertTo-HTML > UserDataTable
\n[\/sourcecode]
\nSample Usage of CSV files<\/em>

\nCSV files can be used for many purposes. They are a convenient vehicle for storing data for the current script or even future script references and manipulation. CSV files can also be used for reporting and general information gathering. Handled properly, data stored in a CSV could be used for historical purposes as well.

\nCleaning up CSV files<\/em>

\nRefer to the previous PowerShell tip of the Week that can be found here:

\nhttps:\/\/www.practicalpowershell.com\/blog\/powershell-cleanup-tips<\/a>

\nMore examples of how to use CSV files as well as how to use PowerShell for output files can be found the books written and sold on this site. Thanks again for reading our blog.<\/p>\n","protected":false},"excerpt":{"rendered":"

For this week’s tip we will review the use of CSV’s and PowerShell. While it is almost always better to not use CSV files for output or object manipulations, there are other uses of CSVs that can come in handy when working with PowerShell. CSV Operations Exporting a CSV One csv operation we can perform […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rop_custom_images_group":[],"rop_custom_messages_group":[],"rop_publish_now":"initial","rop_publish_now_accounts":[],"rop_publish_now_history":[],"rop_publish_now_status":"pending","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-512","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nWorking with CSV Files - Practical PowerShell<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/practicalpowershell.com\/working-with-csv-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working with CSV Files - Practical PowerShell\" \/>\n<meta property=\"og:description\" content=\"For this week’s tip we will review the use of CSV’s and PowerShell. While it is almost always better to not use CSV files for output or object manipulations, there are other uses of CSVs that can come in handy when working with PowerShell. CSV Operations Exporting a CSV One csv operation we can perform […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/practicalpowershell.com\/working-with-csv-files\/\" \/>\n<meta property=\"og:site_name\" content=\"Practical PowerShell\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-28T14:00:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-14T16:41:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles-150x150.jpg\" \/>\n<meta name=\"author\" content=\"damian\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"damian\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/\"},\"author\":{\"name\":\"damian\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#\\\/schema\\\/person\\\/4d0733c81966e744aabbb49f56d64deb\"},\"headline\":\"Working with CSV Files\",\"datePublished\":\"2018-10-28T14:00:51+00:00\",\"dateModified\":\"2020-03-14T16:41:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/\"},\"wordCount\":506,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#\\\/schema\\\/person\\\/4d0733c81966e744aabbb49f56d64deb\"},\"image\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/practicalpowershell.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/CSVFiles-150x150.jpg\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/\",\"url\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/\",\"name\":\"Working with CSV Files - Practical PowerShell\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/practicalpowershell.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/CSVFiles-150x150.jpg\",\"datePublished\":\"2018-10-28T14:00:51+00:00\",\"dateModified\":\"2020-03-14T16:41:39+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#primaryimage\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/practicalpowershell.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/CSVFiles.jpg?fit=329%2C422&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/practicalpowershell.com\\\/wp-content\\\/uploads\\\/2020\\\/03\\\/CSVFiles.jpg?fit=329%2C422&ssl=1\",\"width\":329,\"height\":422},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/working-with-csv-files\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/practicalpowershell.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working with CSV Files\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#website\",\"url\":\"https:\\\/\\\/practicalpowershell.com\\\/\",\"name\":\"Practical PowerShell\",\"description\":\"PowerShell books written by experts\",\"publisher\":{\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#\\\/schema\\\/person\\\/4d0733c81966e744aabbb49f56d64deb\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/practicalpowershell.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/practicalpowershell.com\\\/#\\\/schema\\\/person\\\/4d0733c81966e744aabbb49f56d64deb\",\"name\":\"damian\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g\",\"caption\":\"damian\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Working with CSV Files - Practical PowerShell","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/practicalpowershell.com\/working-with-csv-files\/","og_locale":"en_US","og_type":"article","og_title":"Working with CSV Files - Practical PowerShell","og_description":"For this week’s tip we will review the use of CSV’s and PowerShell. While it is almost always better to not use CSV files for output or object manipulations, there are other uses of CSVs that can come in handy when working with PowerShell. CSV Operations Exporting a CSV One csv operation we can perform […]","og_url":"https:\/\/practicalpowershell.com\/working-with-csv-files\/","og_site_name":"Practical PowerShell","article_published_time":"2018-10-28T14:00:51+00:00","article_modified_time":"2020-03-14T16:41:39+00:00","og_image":[{"url":"https:\/\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles-150x150.jpg","type":"","width":"","height":""}],"author":"damian","twitter_card":"summary_large_image","twitter_misc":{"Written by":"damian","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#article","isPartOf":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/"},"author":{"name":"damian","@id":"https:\/\/practicalpowershell.com\/#\/schema\/person\/4d0733c81966e744aabbb49f56d64deb"},"headline":"Working with CSV Files","datePublished":"2018-10-28T14:00:51+00:00","dateModified":"2020-03-14T16:41:39+00:00","mainEntityOfPage":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/"},"wordCount":506,"commentCount":0,"publisher":{"@id":"https:\/\/practicalpowershell.com\/#\/schema\/person\/4d0733c81966e744aabbb49f56d64deb"},"image":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#primaryimage"},"thumbnailUrl":"https:\/\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles-150x150.jpg","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/practicalpowershell.com\/working-with-csv-files\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/","url":"https:\/\/practicalpowershell.com\/working-with-csv-files\/","name":"Working with CSV Files - Practical PowerShell","isPartOf":{"@id":"https:\/\/practicalpowershell.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#primaryimage"},"image":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#primaryimage"},"thumbnailUrl":"https:\/\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles-150x150.jpg","datePublished":"2018-10-28T14:00:51+00:00","dateModified":"2020-03-14T16:41:39+00:00","breadcrumb":{"@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/practicalpowershell.com\/working-with-csv-files\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#primaryimage","url":"https:\/\/i0.wp.com\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles.jpg?fit=329%2C422&ssl=1","contentUrl":"https:\/\/i0.wp.com\/practicalpowershell.com\/wp-content\/uploads\/2020\/03\/CSVFiles.jpg?fit=329%2C422&ssl=1","width":329,"height":422},{"@type":"BreadcrumbList","@id":"https:\/\/practicalpowershell.com\/working-with-csv-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/practicalpowershell.com\/"},{"@type":"ListItem","position":2,"name":"Working with CSV Files"}]},{"@type":"WebSite","@id":"https:\/\/practicalpowershell.com\/#website","url":"https:\/\/practicalpowershell.com\/","name":"Practical PowerShell","description":"PowerShell books written by experts","publisher":{"@id":"https:\/\/practicalpowershell.com\/#\/schema\/person\/4d0733c81966e744aabbb49f56d64deb"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/practicalpowershell.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/practicalpowershell.com\/#\/schema\/person\/4d0733c81966e744aabbb49f56d64deb","name":"damian","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g","caption":"damian"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/d5a8cc64a5aa27558a897b108e3be1a89859511a3fd26176dac292f26e7a4ae4?s=96&d=mm&r=g"}}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/posts\/512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/comments?post=512"}],"version-history":[{"count":1,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/posts\/512\/revisions"}],"predecessor-version":[{"id":514,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/posts\/512\/revisions\/514"}],"wp:attachment":[{"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/media?parent=512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/categories?post=512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/practicalpowershell.com\/wp-json\/wp\/v2\/tags?post=512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}