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:

PowerShell Workbooks.Open with delimiter semicolon

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.

Is Excel currently opening the file with all the data in the first column? If so, you can use it's text to columns method, to define a custom delimiter. — David Rushton
– David Rushton, Commented Nov 17, 2017 at 9:42

G42 · Accepted Answer · 2017-11-17 10:53:47Z

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.

answered Nov 17, 2017 at 10:53

G42

10.1k2 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yong Cai Over a year ago

Tried this solution before and also tried your code to replace my current Open method which is working. Unfortunately, the code will stopped with exception: (System.Management.Automation.RuntimeException) - Error Message: You cannot call a method on a null-valued expression. Any idea about this exception?

Yong Cai Over a year ago

And I had done some research on opentext, opentext might not working well on .csv file extension directly, fyr: stackoverflow.com/questions/27491558/…

Collectives™ on Stack Overflow

Powershell Excel ComObject Open Custom Delimiters

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related