Run Powershell to convert all the csv files to tab delimited text within the same folder

Question

I'm a newbie to Powershell. Somehow I've managed to use the following script to convert the csv to text with tab delimited. Now I would like to save the executable script within a folder where the csv's are saved. If the script is executed then it should convert all the files with.csv should be converted within the same folder.

My working script at the moment with the path specified is below (Powershell verion 5.1)

$source = "C:\test.csv"
$destination = "C:\newfile.txt"
(Import-CSV $source| 
 ConvertTo-csv -Delimiter "`t" -NoTypeInformation | 
 Select-Object -Skip 1) -replace '"' | Set-Content $destination

Just use `.\file.csv` for your source and location, and save the script as a .ps1 file — Jonathon Anderson, Jul 02 '20 at 17:39
What is the delimiter used in all these csv files you want to convert to TAB delimited? Apparently, you also want to strip off the headers line and remove all quotes. I would change the output files extension to `.tab`, so as not to overwrite the original files. Also, it is dangerous to simply remove all quote characters, because sometimes fields include the delimiter character and these fields MUST be quoted. If you don't, the field alignment will break and you will no longer have valid files.. — Theo, Jul 02 '20 at 17:55
The file I'm using doesn't have quotes. And it is good to save it as tab file as you say. I don't want to modify old file and only the "newfile" needs modified. — Raj, Jul 02 '20 at 18:34

score 2 · Accepted Answer · answered Jul 02 '20 at 18:32

Looping though the files in the folder where the script is in, you can make use of the $PSScriptRoot automatic variable.

As commented, it is unsafe to simply remove all quotes, because the csv file may have fields that contain the TAB delimiter character and if you do not quote these values, the resulting file will have misaligned rows of data.

You can use my function ConvertTo-CsvNoQuotes to remove the quotes safely.

function ConvertTo-CsvNoQuotes {
    # returns a csv delimited string array with values unquoted unless needed
    [OutputType('System.Object[]')]
    [CmdletBinding(DefaultParameterSetName = 'ByDelimiter')]
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)]
        [PSObject]$InputObject,

        [Parameter(Position = 1, ParameterSetName = 'ByDelimiter')]
        [char]$Delimiter = ',',

        [Parameter(ParameterSetName = 'ByCulture')]
        [switch]$UseCulture,
        [switch]$NoHeaders,
        [switch]$IncludeTypeInformation  # by default, this function does NOT include type information
    )
    begin {
        if ($UseCulture) { $Delimiter = (Get-Culture).TextInfo.ListSeparator }
        # regex to test if a string contains a double quote, the delimiter character,
        # newlines or has whitespace at the beginning or the end of the string.
        # if that is the case, the value needs to be quoted.
        $needQuotes = '^\s|["{0}\r\n]|\s$' -f [regex]::Escape($Delimiter)
        # a boolean to check if we have output the headers or not from the object(s)
        # and another to check if we have output type information or not
        $doneHeaders = $doneTypeInfo = $false
    }

    process {
        foreach($item in $InputObject) {
            if (!$doneTypeInfo -and $IncludeTypeInformation) {
                '#TYPE {0}' -f $item.GetType().FullName
                $doneTypeInfo = $true
            }
            if (!$doneHeaders -and !$NoHeaders) {
                $row = $item.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Name -match $needQuotes) { '"{0}"' -f ($_.Name -replace '"', '""') } else { $_.Name }
                }
                $row -join $Delimiter
                $doneHeaders = $true
            }
            $item | ForEach-Object {
                $row = $_.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Value -match $needQuotes) { '"{0}"' -f ($_.Value -replace '"', '""') } else { $_.Value }
                }
                $row -join $Delimiter
            }
        }
    }
}

With that in place on top of your script, use it like this:

# get all CSV files in the path this script is currently in
Get-ChildItem -Path $PSScriptRoot -Filter '*.csv' -File | ForEach-Object {
    $tabCsv = (Import-Csv -Path $_.FullName) | ConvertTo-CsvNoQuotes -Delimiter "`t" -NoHeaders
    $tabOut = [System.IO.Path]::ChangeExtension($_.FullName, ".tab")
    $tabCsv | Set-Content -Path $tabOut
}

Run Powershell to convert all the csv files to tab delimited text within the same folder

1 Answers1