Powershell Excel ComObject Open Custom Delimiters

Question

I am facing a problem on the Powershell Excel ComObject where I am unable to accept a delimiter other than comma ,. Some of the external .csv files received use a different delimiter such as semicolon ;, pipeline | etc.

So, is there any solution on it to make it accepting custom delimiters? I tried using Import-Csv $fileloop -Delimiter ';' and it works. However I wish to remain using -ComObject to proceed as my script are all written using ComObject, and I need it to parse and check for columns and rows and some extra function.

Below is the relevant code snippet of opening .csv file for further processes:

$path      = "C:\Users\1.csv"
$objexcel  = New-Object -ComObject Excel.Application
$workbook  = $objexcel.Workbook.Open($path)
$worksheet = $workbook.activesheet
$colMax    = ($WorkSheet.UsedRange.Columns).count
$intcolMax = $colMax
$intRowMax = ($WorkSheet.UsedRange.Rows).count
.....

---

I did research on some related topics and tested, none of them were working:

1. PowerShell Workbooks.Open with delimiter semicolon
2. https://kb.paessler.com/en/topic/2293-i-have-trouble-opening-csv-files-with-microsoft-excel-is-there-a-quick-way-to-fix-this

Testing results:
1. The script need high portability, which means the script will pass to another users to use, so need to avoid using setting method on the region & language settings on windows.
2. The script need process large size and large amount of .csv file, hence need to avoid using method such as Import-Csv $fileloop -Delimiter ';'| Export-Csv $commadelimiteroutput -Delimiter ',' to rebuild a new .csv file into comma delimiter and then use the ComObject to process.

If this is lacking information or not clear, kindly let me know.

Answer 1

Instead of using the Open method, use OpenText. This allows the ability to set custom delimiters but this is not required as it also allows $true/$false for some standard delimiters.

Set the argument for semicolon ;delimiter to $true, and other delimiters to $false.

$objexcel.WorkBooks.OpenText(`
                $path,        # Filename
                2,            # Origin
                1,            # StartRow
                1,            # DataType
                1,            # TextQualifier
                $false,       # ConsecutiveDelimiter
                $false,       # Tab
                $true,        # Semicolon
                $false,       # Comma
                $false,       # Space
                $false,       # Other - $true/$false
                $false,       # OtherChar - specify the character if Other is $true
                @(@(3,3),@(1,2))         # FieldInfo
)

For FieldInfo, you can get the enumerations here. I have used the same example as documentation example specifying column 3 as and xlMDYFormat column 1 as xlTextFormat, but in PowerShell.