Remove spaces from column names when using PowerShell’s Import-Csv

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):

column-headers-with-spaces

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

Leave a Reply

Your email address will not be published. Required fields are marked *