How to import csv replace characters and export csv that is saved in blob in powershell

Question

I am very new to powershell. I am trying to replace characters in a csv that is saved in blob storage. Take the file from one folder, do the replace and save in another.

I have 2 sets of code, one for replacing the characters and one for creating blob context. However, I am unsure how to put them together to perform the task. Meaning what do I use instead of 'import-csv' and 'set-content' and how do I tell it to do this for each file in the folder? Any advice would be appreciated.

$dest = "C:\test\Test - Copy\"

Function RemoveStripScrape ($CsvFileName, $csvLoc)
{
    $CsvFile = $csvLoc + $CsvFileName + ".csv"
    $ExportLoc = $csvLoc + $CsvFileName + ".csv"

    $Csv = Import-Csv $CsvFile 

    $regex = '(?<!"),|,(?!")'
    $regex1 = '(?<!\r)\n'

    $Csv | Select-Object * |ConvertTo-Csv -NoTypeInformation | %{$_ -replace $regex,' '} |%{$_ -replace $regex1, ' '} | Set-Content $ExportLoc 
    }

$ens = Get-ChildItem $dest -Recurse -include Let*.csv
foreach($e in $ens)
{
    $e.Basename
    RemoveStripScrape -CsvFileName $e.BaseName -csvLoc $dest
}

get context:

$azurepw = ConvertTo-SecureString "xyz" -AsplainText -Force
$cred = New-Object -typename pscredential -argumentlist "[email protected]",$azurepw
Login-AzureRmAccount -credential $cred | Out-Null

$resourcegroup = "xyz"
$storageaccountname = "xyz"

$storageaccountkey = `
    (Get-AzureRmStorageAccountKey `
    -ResourceGroupName $resourcegroup `
    -Name $storageaccountname).Value[0]

$sourceContext = New-AzureStorageContext $storageaccountname $storageaccountkey
$container = "xyz"

$FileDate = $($(Get-Date).ToString('yyyyMMdd'))
$SourceFileName = "*activity*.csv"
$SinkFileName = "processed_$($FileDate).csv"
$BlobSourceFileNamePath = "Data/Revenue/Archive/CorethreeNew/$($SourceFileName)"
$BlobSinkFileNamePath = "Data/Revenue/Archive/CorethreeNew/Processed/$($SinkFileName)"
#$BlobArchiveSinkFileNamePath = "Data/Revenue/Archive/CorethreeNew/Archive/$($SinkFileName)"

marival marival · Accepted Answer · 2019-05-31T13:00:43

solved it this way: #Getting files from local directory to perform replace $ent = Get-ChildItem -Path $afLocalPath2 -Recurse -include aircoach.csv

#Importing csv and replacing regex for each file in the local store and saving it in 
local Processed folder
foreach($e in $ent)
{
     $FileName = $e.Name
     $CsvFile = "$($afLocalPath2)$($FileName)"
     $Csv = Import-Csv $CsvFile 

     $Csv | Select-Object * |ConvertTo-Csv -NoTypeInformation | %{$_ -replace $regex,' '} |%{$_ -replace $regex1, ' '} |%{$_ -replace $regex2, ' '} | %{$_ -replace $regex3, ''}| Set-Content "$($ExportLoc)$($FileName)" -Force



   #Uploading the updated files from local Processed directory to blob
   Set-AzureStorageBlobContent -Context $sourceContext -Container $container -File "$($ExportLoc)$($FileName)" -Blob "$($BlobSinkFileNamePath)$($FileName)" -Force


}

How to import csv replace characters and export csv that is saved in blob in powershell

1 Answers