Convert tab delimiter to semicolon

Question

I have updated a piece of software for our T&A system, this produces a CSV file in tab-delimited format. The payroll software needs this in the older format which was semicolon-delimited. I have been in touch with both vendors and neither one has a way to accommodate the other so I need to convert the CSV file to suit the payroll software. I have tried to do this with PowerShell with mixed results.

First I tried

Import-Csv ".\desktop\new version.csv" -Delimiter `t |
  Export-Csv ".\converted.csv" -NoTypeInf

which removed the tab delimiter but didn't do the ;. So I then tried

Import-Csv ".\desktop\new version.csv" -Delimiter `t |
  Export-Csv ".\desktop\converted.csv" -NoTypeInformation -Delimiter ";"

which did convert it from tabbed to ;, but only for the headers. It totally ignored the rest of the data. I then tried a different approach and used

$path = ".\desktop\new.csv"
$outPath = ".\desktop\converted.csv"
Get-Content -path $path |
  ForEach-Object {$_ -replace "`t",";" } |
  Out-File -filepath $outPath

which formatted the file correctly, but put an extra empty row between each row of data. I'm not sure what I'm doing wrong!

Answer 1

I'm pretty sure you are having an encoding issue with your last example. Get-Content reads in as Ascii whereas Out-File defaults to Unicode. Either set the -Encoding on Out-File or just use Set-Content.

Get-Content -path $path |
ForEach-Object {$_ -replace "`t",";" } |
Set-Content -filepath $outPath

You could even trim this down a bit if need be.

(Get-Content -path $path) -replace "`t",";" | Set-Content -filepath $outPath

However your 2nd code example...

Import-Csv ".\desktop\new version.csv" -Delimiter `t | Export-Csv ".\desktop\converted.csv" -NoTypeInformation -Delimiter ";"

should have worked just fine to replacing the tabs to semicolons. If it is not working then I would think your source data has an issue.

---

About the source file

Based on comments the code above is creating a trailing column. Most likely reason for that is trailing tabs on each row that are being converted. If that is the case then a little more manipulation would be required. Easier to use the foreach loop in this case.

Get-Content -path $path |
ForEach-Object {$_.Trim() -replace "`t",";" } |
Set-Content -filepath $outPath

That would remove the last tab/whitespace of each line. There is a potential enormous caveat doing it this way though. I think it has the potential to drop data if you have empty columns on the end. However if those columns were already empty it should not matter as long as the header is formed well and the input program can account for this. Else you are looking at reading in the file with Import-CSV and dropping the last column which can be done.

Answer 2

Here's a function I used to replace strings in text files like you're doing. This is assuming there's no tabs inside the text file other than those that are delimiting the columns. I'm assuming there's not. You can use it like this:

Find-InTextFile -FilePath C:\MyFile.csv -Find "`t" -Replace ';'

function Find-InTextFile
{
    <#
    .SYNOPSIS
        Performs a find (or replace) on a string in a text file or files.
    .EXAMPLE
        PS> Find-InTextFile -FilePath 'C:\MyFile.txt' -Find 'water' -Replace 'wine'

        Replaces all instances of the string 'water' into the string 'wine' in
        'C:\MyFile.txt'.
    .EXAMPLE
        PS> Find-InTextFile -FilePath 'C:\MyFile.txt' -Find 'water'

        Finds all instances of the string 'water' in the file 'C:\MyFile.txt'.
    .PARAMETER FilePath
        The file path of the text file you'd like to perform a find/replace on.
    .PARAMETER Find
        The string you'd like to replace.
    .PARAMETER Replace
        The string you'd like to replace your 'Find' string with.
    .PARAMETER UseRegex
        Use this switch parameter if you're finding strings using regex else the Find string will
        be escaped from regex characters
    .PARAMETER NewFilePath
        If a new file with the replaced the string needs to be created instead of replacing
        the contents of the existing file use this param to create a new file.
    .PARAMETER Force
        If the NewFilePath param is used using this param will overwrite any file that
        exists in NewFilePath.
    #>
    [CmdletBinding(DefaultParameterSetName = 'NewFile')]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateScript({ Test-Path -Path $_ -PathType 'Leaf' })]
        [string[]]$FilePath,

        [Parameter(Mandatory = $true)]
        [string]$Find,

        [Parameter()]
        [string]$Replace,

        [Parameter()]
        [switch]$UseRegex,

        [Parameter(ParameterSetName = 'NewFile')]
        [ValidateScript({ Test-Path -Path ($_ | Split-Path -Parent) -PathType 'Container' })]
        [string]$NewFilePath,

        [Parameter(ParameterSetName = 'NewFile')]
        [switch]$Force
    )
    begin
    {
        if (!$UseRegex.IsPresent)
        {
            $Find = [regex]::Escape($Find)
        }
    }
    process
    {
        try
        {
            foreach ($File in $FilePath)
            {
                if ($Replace)
                {
                    if ($NewFilePath)
                    {
                        if ((Test-Path -Path $NewFilePath -PathType 'Leaf') -and $Force.IsPresent)
                        {
                            Remove-Item -Path $NewFilePath -Force
                            (Get-Content $File) -replace $Find, $Replace | Add-Content -Path $NewFilePath -Force
                        }
                        elseif ((Test-Path -Path $NewFilePath -PathType 'Leaf') -and !$Force.IsPresent)
                        {
                            Write-Warning "The file at '$NewFilePath' already exists and the -Force param was not used"
                        }
                        else
                        {
                            (Get-Content $File) -replace $Find, $Replace | Add-Content -Path $NewFilePath -Force
                        }
                    }
                    else
                    {
                        (Get-Content $File) -replace $Find, $Replace | Add-Content -Path "$File.tmp" -Force
                        Remove-Item -Path $File
                        Rename-Item -Path "$File.tmp" -NewName $File
                    }
                }
                else
                {
                    Select-String -Path $File -Pattern $Find
                }
            }
        }
        catch
        {
            Write-Error -Message $_.Exception.Message
        }
    }
}