When using PowerShell’s Import-Csv cmdlet, it is ideal for the column headings in the source file to not have any spaces. While the import will work, later referencing values in each column heading is messy. Let me demonstrate.
Messy Column Headers with spaces
Let’s say your source file looks like this. Note that there are 10 column headings, but seven of them contain spaces (highlighted in yellow):
If you used Import-Csv to import this file, your imported data would like something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # Source Folder $SourceFolder = "C:\Temp" # Source File $SourceFile = "Sample-Addresses.csv" # Source Path $SourcePath = $SourceFolder + "\" + $SourceFile # Import-CSV $SourceData = Import-CSV -Path $SourcePath # Write Host $SourceData | Format-Table -AutoSize ID Last Name First Name Middle Initial Address 1 Address 2 City State Zip Code Phone Work -- --------- ---------- -------------- --------- --------- ---- ----- -------- ---------- 00001 Beaver Bobby B 123 Valley Rd Apt A Indianapolis IN 46260 (317)555-0001 00002 Stout Stewart S 234 Plains Ave Ste B Indianapolis IN 46260 (317)555-0002 00003 Connor Chris C 345 Foothill Cir Fl C Indianapolis IN 46260 (317)555-0003 00004 Davis Donald D 456 Hill St Bld D Indianapolis IN 46260 (317)555-0004 00005 Pierce Peter P 567 Mountain Pl Unit E Indianapolis IN 46260 (317)555-0005 |
Not bad, right? Well, retrieving all IDs would be easy; but doing the same with Last Names would be a bit messier:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | # List all IDs $SourceData.ID 00001 00002 00003 00004 00005 # List all Last Names $SourceData."Last Name" Beaver Stout Connor Davis Pierce |
Both work and retrieve the correct results, but the dot-notation for a column heading that contains a space requires double-quotes to reference.
Clean Column Headers Without Spaces
The solution I propose involves the following steps:
- Import just the first two rows using Get-Content and ConvertFrom-Csv
- Use Trim() and -Replace to remove unwanted spaces
- Use Import-CSV with the new -Header to now use the cleaned headers without spaces
Here’s some detail regarding the dot-notation and how Trim() and -Replace cleaned up the column Name values.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | $SourceHeadersDirty | Format-Table -AutoSize ID Last Name First Name Middle Initial Address 1 Address 2 City State Zip Code Phone Work -- --------- ---------- -------------- --------- --------- ---- ----- -------- ---------- 00001 Beaver Bobby B 123 Valley Rd Apt A Indianapolis IN 46260 (317)555-0001 $SourceHeadersDirty.PSObject Members : {string ID=00001, string Last Name=Beaver, string First Name=Bobby, string Middle Initial=B...} Properties : {string ID=00001, string Last Name=Beaver, string First Name=Bobby, string Middle Initial=B...} Methods : {string ToString(), bool Equals(System.Object obj), int GetHashCode(), type GetType()} ImmediateBaseObject : BaseObject : TypeNames : {System.Management.Automation.PSCustomObject, System.Object} $SourceHeadersDirty.PSObject.Properties | Format-Table -AutoSize MemberType IsSettable IsGettable Value TypeNameOfValue Name IsInstance ---------- ---------- ---------- ----- --------------- ---- ---------- NoteProperty True True 00001 System.String ID True NoteProperty True True Beaver System.String Last Name True NoteProperty True True Bobby System.String First Name True NoteProperty True True B System.String Middle Initial True NoteProperty True True 123 Valley Rd System.String Address 1 True NoteProperty True True Apt A System.String Address 2 True NoteProperty True True Indianapolis System.String City True NoteProperty True True IN System.String State True NoteProperty True True 46260 System.String Zip Code True NoteProperty True True (317)555-0001 System.String Phone Work True $SourceHeadersDirty.PSObject.Properties.Name | Format-Table -AutoSize ID Last Name First Name Middle Initial Address 1 Address 2 City State Zip Code Phone Work $SourceHeadersDirty.PSObject.Properties.Name.Trim(' ') -Replace '\s','' | Format-Table -AutoSize ID LastName FirstName MiddleInitial Address1 Address2 City State ZipCode PhoneWork |
End Result
Put it all together and you have a nice and simple way to clean your CSV column names upon import.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | # Import-Csv without spaces in column header (by jasonpearce.com) # Source Folder $SourceFolder = "C:\Temp" # Source File $SourceFile = "Sample-Addresses.csv" # Source Path $SourcePath = $SourceFolder + "\" + $SourceFile # Source Headers Dirty (source CSV has unwanted spaces) $SourceHeadersDirty = Get-Content -Path $SourcePath -First 2 | ConvertFrom-Csv # Source Headers Cleaned (removed spaces) $SourceHeadersCleaned = $SourceHeadersDirty.PSObject.Properties.Name.Trim(' ') -Replace '\s','' # Import-CSV $SourceData = Import-CSV -Path $SourcePath -Header $SourceHeadersCleaned | Select-Object -Skip 1 # Write Host $SourceData | Format-Table -AutoSize ID LastName FirstName MiddleInitial Address1 Address2 City State ZipCode PhoneWork -- -------- --------- ------------- -------- -------- ---- ----- ------- --------- 00001 Beaver Bobby B 123 Valley Rd Apt A Indianapolis IN 46260 (317)555-0001 00002 Stout Stewart S 234 Plains Ave Ste B Indianapolis IN 46260 (317)555-0002 00003 Connor Chris C 345 Foothill Cir Fl C Indianapolis IN 46260 (317)555-0003 00004 Davis Donald D 456 Hill St Bld D Indianapolis IN 46260 (317)555-0004 00005 Pierce Peter P 567 Mountain Pl Unit E Indianapolis IN 46260 (317)555-0005 |