3 Replies Latest reply on Jul 11, 2018 10:11 PM by LucD

    Import Data with Extra Spaces

    TheVMinator Master

      I'm running this script to input a csv and get VM data:

       

      #Simplified version

      $report = @()

      Import-Csv -path c:\inputfile.csv -UseCulture | foreach{

      $vmname = $_.vmname

      $report += $vmname

      }

      $report | select vmname |  export-csv c:\outputfile.csv -notypeinformation -useculture

       

      The problem is that if the input file has a column called VMName with no spaces AFTER the column title, everything works fine, but if there are spaces after the column title, it won't pick up the vmnames in the input file.  Every time someone different creates the input file, they may add an extra space before or after the column title, and mess up the results.

       

      How can I make the import-csv statement read the column titles even if there are spaces before or after the column title?

       

      Thanks!

        • 1. Re: Import Data with Extra Spaces
          LucD Guru
          User ModeratorsCommunity WarriorsvExpert

          Can you provide a sample extract of such a CSV with spaces after the column header?

          For me it seems to work, independent of the number of spaces.

           

          Are you using the UseCulture switch or the Delimiter parameter on the Import-Csv cmdlet?

          1 person found this helpful
          • 2. Re: Import Data with Extra Spaces
            TheVMinator Master

            I can upload it but I've got to sanitize it first and remove all sensitive data and I'm just heading out for the day so need to work on this tomorrow - but note this - I'm not sure exactly how to describe the problem, but for the columns that have the issue, when I click on the cell with the column name in Excel, the cursor jumps to one space after the column name.  For the columns that don't have the issue, when I click on the cell with the column name in Excel, the cursor jumps to the character immediately after the column name. 

             

            I think I could solve the problem by inputting the input file and removing all spaces BEFORE or AFTER the column name, then writing a new input file with the spaces removed from the column headings.  But I need to do this without removing spaces from the actual data in the columns.

             

            Any way to do this?

            • 3. Re: Import Data with Extra Spaces
              LucD Guru
              Community WarriorsUser ModeratorsvExpert

              Provided these characters are actually spaces, you can do something like this.

              It removes starting and trailing spaces from the first row column headers.

               

              $fileName = 'C:\vmnames.csv'

               

              $content = Get-Content -Path $fileName

              $content[0] = ($content[0].Split(',') | %{$_.TrimStart(' ').TrimEnd(' ')}) -join ','

              $content | Out-File -FilePath $fileName