Forum Discussion
Remove a comma from a CSV file for a particular column only
Hi manny213 ,
You can't provide a wildcard specification such as "*.csv" to the Import-Csv commandlet. That's why you're getting that error.
Given Import-Csv can only accept a single file, you have to wrap it within an outer ForEach-Object statement.
Get-ChildItem -Path "d:\data\temp\forum\*.csv" | ForEach-Object { # Name of the temporary working file. $tempFile = [regex]::Replace($_.FullName, ".csv$", "-temp.csv"); try { # Parse the original file and pipe to the temporary file. Import-Csv -Path $_.FullName -ErrorAction:Stop | ForEach-Object { [PSCustomObject] @{ SYMBOL = $_.SYMBOL; INTERVAL = $_.INTERVAL; NAME = $_.NAME.Replace(",", ""); DATE = $_.DATE; OPEN = $_.OPEN; HIGH = $_.HIGH; LOW = $_.LOW; CLOSE = $_.CLOSE; VOLUME = $_.VOLUME; } } | Export-Csv -Path $tempFile -NoTypeInformation -ErrorAction:Stop; # Remove original file. Remove-Item -Path $_.FullName -ErrorAction:Stop; # Rename temporary file to the original file's name. Rename-Item -Path $tempFile -NewName $_.Name -ErrorAction:Stop; } catch { Write-Error -Exception $_.Exception; } }This basic script will attempt to parse the original file before replacing it, and will throw an error if it's unable to do so.
It will repeat this process for each file within the directory, as illustrated by the outer ForEach-Object block on line 1, which is where you can specify wildcards via the Get-ChildItem commandlet.
This is the sample original file I used.
This is the resulting file after parsing.
Cheers,
Lain
I see your point. I just need the commas gone and the CSV structure retained so I can import the data. I tried the script. I am getting an error. The script is running on a folder full of CSV files. How do I fix it?
Script:
Import-Csv -Path C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\*.csv | ForEach-Object {
[PSCustomObject] @{
SYMBOL = $_.SYMBOL;
INTERVAL = $_.INTERVAL;
NAME = $_.NAME.Replace(",", " ");
DATE = $_.DATE;
OPEN = $_.OPEN;
HIGH = $_.HIGH;
LOW = $_.LOW;
CLOSE = $_.CLOSE;
VOLUME = $_.VOLUME;
}
}Error:
Import-Csv : Cannot perform operation because the path resolved to more than one file. This command cannot operate on multiple files.
At line:1 char:1
+ Import-Csv -Path C:\Users\Me\Documents\Trading\Trading_Data\Sto ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Import-Csv], PSInvalidOperationException
+ FullyQualifiedErrorId : ReadWriteMultipleFilesNotSupported,Microsoft.PowerShell.Commands.ImportCsvCommandThank you
Hi manny213 ,
You can't provide a wildcard specification such as "*.csv" to the Import-Csv commandlet. That's why you're getting that error.
Given Import-Csv can only accept a single file, you have to wrap it within an outer ForEach-Object statement.
Get-ChildItem -Path "d:\data\temp\forum\*.csv" | ForEach-Object {
# Name of the temporary working file.
$tempFile = [regex]::Replace($_.FullName, ".csv$", "-temp.csv");
try
{
# Parse the original file and pipe to the temporary file.
Import-Csv -Path $_.FullName -ErrorAction:Stop | ForEach-Object {
[PSCustomObject] @{
SYMBOL = $_.SYMBOL;
INTERVAL = $_.INTERVAL;
NAME = $_.NAME.Replace(",", "");
DATE = $_.DATE;
OPEN = $_.OPEN;
HIGH = $_.HIGH;
LOW = $_.LOW;
CLOSE = $_.CLOSE;
VOLUME = $_.VOLUME;
}
} | Export-Csv -Path $tempFile -NoTypeInformation -ErrorAction:Stop;
# Remove original file.
Remove-Item -Path $_.FullName -ErrorAction:Stop;
# Rename temporary file to the original file's name.
Rename-Item -Path $tempFile -NewName $_.Name -ErrorAction:Stop;
}
catch
{
Write-Error -Exception $_.Exception;
}
}
This basic script will attempt to parse the original file before replacing it, and will throw an error if it's unable to do so.
It will repeat this process for each file within the directory, as illustrated by the outer ForEach-Object block on line 1, which is where you can specify wildcards via the Get-ChildItem commandlet.
This is the sample original file I used.
This is the resulting file after parsing.
Cheers,
Lain
Thanks for this. It worked. All the records got loaded. Small request. Is it possible to have the output file not have double quotes? If it is too much hassle its ok. I can clean it up in SQL Server. I just thought I would ask in case its a quick fix. You have been super awesome and I appreciate your help.
Thank you!!
Hi manny213 ,
It's unusual to being using a tool that cannot automatically handle the processing of the double-quotes. Still, here's some points to consider.
I'm not sure if you're aware, but there's two versions of PowerShell:
- Windows PowerShell: The version that has been included with Windows for nearly two decades;
- PowerShell: The downloadable version that does not ship with Windows.
The version of Export-Csv from Windows PowerShell does not allow you to exclude the double-quotes.
The version of Export-Csv from PowerShell does include additional parameters that can help control the behaviour of the double-quotes:
There are other options as well.
If you're working with SQL Integration Services (IS), handling double-quotes isn't something you should have to worry about at the scripting level as IS can easily perform any necessary transformations.
You can apply a half-baked solution at the scripting level, as shown below, but the flaw is that if any value genuinely was supposed to contain a double-quote, it will be stripped out in taking this approach. As such, I don't recommend it, but I've included it for completeness only.
Get-ChildItem -Path "d:\data\temp\forum\*.csv" | ForEach-Object { # Name of the temporary working file. $tempFile = [regex]::Replace($_.FullName, ".csv$", "-temp.csv"); try { # Parse the original file and pipe to the temporary file. Import-Csv -Path $_.FullName -ErrorAction:Stop | ForEach-Object { [PSCustomObject] @{ SYMBOL = $_.SYMBOL; INTERVAL = $_.INTERVAL; NAME = $_.NAME.Replace(",", ""); DATE = $_.DATE; OPEN = $_.OPEN; HIGH = $_.HIGH; LOW = $_.LOW; CLOSE = $_.CLOSE; VOLUME = $_.VOLUME; } } | ConvertTo-Csv -NoTypeInformation | ForEach-Object { $_.Replace('"', ""); } | Out-File -FilePath $tempFile -ErrorAction:Stop; # Remove original file. Remove-Item -Path $_.FullName -ErrorAction:Stop; # Rename temporary file to the original file's name. Rename-Item -Path $tempFile -NewName $_.Name -ErrorAction:Stop; } catch { Write-Error -Exception $_.Exception; } }Cheers,
LainThanks for the insight. I wasn't aware of that. It is ok. I am going to use SQL Server to clean up the double quotes. All is good. Thank you so much for your help!