0

I have a CSV file which looks all fine until it is imported into PowerShell, when its imported each character is followed by a space like C : \ instead of C:\.

It would be easy enough to format the cells to text in Excel (which works) but this CSV file is created on multiple servers by in an automation policy so going through each of these files and formatting them will take a while as you can imagine.

I was wondering if there was a way in which I can format the cells first in PowerShell then import the CSV.

PowerShell code I am using:

$data = import-csv -Path $path -UseCulture -Header @("Path", "Folder", "Size")

CSV Snippet:

C:\,C:\,14.0GB C:\Program Files,Program Files,4.5GB C:\Program Files\Microsoft Office,Microsoft Office,2.8GB

Improve this question

4 Answers 4

Reset to default
2

It sounds like the file might be Unicode, but without the proper byte order marks, which would cause PowerShell to use the default ASCII encoding. If that is the case, you'll need to specify the encoding:

$data = import-csv -Encoding Unicode -Path $path ...

Another option is to convert the file to ASCII prior to the import [credit to OP for the command]:

Get-content C:\path\TestXml.csv | Set-Content -Encoding Ascii TestXml.csv

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Import-Csv : A parameter cannot be found that matches parameter name 'Encoding'. At line:5 char:29 + $data = import-csv -Encoding <<<< Unicode -Path $path -UseCulture -Header @("Path", "Folder", "Size") + CategoryInfo : InvalidArgument: (:) [Import-Csv], ParameterBindingException + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ImportCsvCommand
Apparently -Encoding cannot be found. It is a parameter though?
The Encoding parameter must not be available in the version of PowerShell you are using...
Open the csv file in Notepad and then do a File -> Save As. Look at the bottom and see what the encoding is set to. If it's not ANSI, or if Notepad also shows spaces between the characters, then the encoding is probably the problem. Other than upgrading PowerShell to get the -Encoding parameter, another option would be to convert the file to ANSI/ASCII before importing it. I don't know of any utilities that do that off the top of my head. One more option would be to fix the process that creates the file so that it saves it as ANSI/ASCII.
You put me on the right track I'll put the solution I used as an answer
|
0

this might present a different problem, but it may work for removing all the spaces

$data = import-csv -Path $path -UseCulture -Header @("Path", "Folder", "Size")
$data | % {
    $_.path = $_.path -replace '\s'
    $_.folder = $_.folder -replace '\s'
    $_.size = $_.size -replace '\s'
}
$data
Improve this answer

Comments

0

It would be beneficial to get a snippet of what the CSV looks like. Can you provide the header and 1 or 2 rows?

Is the header specified in the CSV file?

When using the Header parameter, delete the original header row from the CSV file. Otherwise, Import-Csv creates an extra object from the items in the header row. https://technet.microsoft.com/en-us/library/hh849891.aspx

You are specifying the UseCulture switch which will use the default delimiter specified by the environment. You can run the following command to find your culture's delimiter:

(Get-Culture).TextInfo.ListSeparator

https://technet.microsoft.com/en-us/library/hh849891.aspx

Improve this answer

7 Comments

I deliberately didn't give it a header as its supplying it with one in powershell. I've updated my question to include a couple of rows.
@SamLucas Are you using Import CSV to import an XML file?
No it's importing a CSV file
Well it would be nice to see some of the CSV file in order to troubleshoot.
I have included a couple of rows in my question
|
0

@Tony Hinkle sent me in the right direction so I have marked his answer as correct, here is the code that I have used:

Set the content of the CSV to ascii encoding

Get-content C:\Users\sam\Desktop\TestXml.csv | Set-Content -Encoding Ascii TestXml.csv

Then import the Csv

$data = Import-Csv C:\Users\sam\Desktop\TestXml.csv -Header @("Path", "Folder", "Size")
Improve this answer

1 Comment

Thanks for following up--glad I could help. I will update my answer with this information so it's all in one place.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.