VMware Cloud Community
TheVMinator
Expert
Expert

Import Data with Extra Spaces

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!

Reply
0 Kudos
3 Replies
LucD
Leadership
Leadership

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?


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

TheVMinator
Expert
Expert

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?

Reply
0 Kudos
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos