Powershell - Workbook.OpenText with parameter FieldInfo-CodePudding

I'm trying to use the Workbook.OpenText passing the parameter FieldInfo but with no success

I want to open a recent created csv file(I also tried with txt extension) with the fieldinfo parameter so I can put the text format on some columns but it keeps giving me the following error:

You cannot call a method on a null-valued expression.

The code

` $array = @(Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 2), Array(11, 1) )

    $MyWorkbook = $Excel.workbooks.OpenText($file,2,1,1,1,$True,$True,$True,$False,$False,$False,$False,$array)
    
    $MyWorksheet = $MyWorkbook.WorkSheets.item(1)

Many thanks for any help G

CodePudding user response：

The problem seems to be that Excel, even though you specify the comma as field delimiter, ALWAYS looks at what is set as the systems ListSeparator.. Even if you specify the 'Other' and 'OtherChar' parameters to use the comma, it still fails and expects a semicolon..

On my Dutch machine that is set to the semicolon ; so even if my csv file is using commas, AND I set the below parameter to Comma, Excel 'translates' that to be whatever is set in [cultureinfo]::CurrentCulture.TextInfo.ListSeparator

For me this works when I use the semicolon character as delimiter in the input CSV file, so I need to change the format of the csv first:

$Delimiter = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator
$file = 'D:\Test\comma.csv'  # the original comma delimited csv
(Import-Csv -Path $file) | Export-Csv -Path $file -Delimiter $Delimiter -NoTypeInformation

Next problem is that method OpenText() does not seem to return anything, explaining the error message You cannot call a method on a null-valued expression

# define an array of two-element arrays, in which the first element is the column number (1-based), 
# and the second element is one of the XlColumnDataType constants specifying how the column is parsed.
# see: https://learn.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype
$FieldInfo = @(1, 1), @(2, 1), @(3, 1), @(4, 1), @(5, 1), @(6, 1), @(7, 1), @(8, 1), @(9, 1), @(10, 2), @(11, 1)

$Excel = New-Object -ComObject Excel.application
$Excel.DisplayAlerts = $false
$Excel.Visible = $true

# there are many parameters to the OpenText() method, but as we cannot use these as Named parameters
# we will have to provide all up to and including the FieldInfo parameter.
# note that for any variant parameter you wish to omit, we use [type]::Missing
$Excel.WorkBooks.OpenText($file,                # The absolute full file path
                          2,                    # Origin: xlWindows
                          1,                    # StartRow (default 1)
                          1,                    # DataType: xlDelimited
                          1,                    # TextQualifier: xlTextQualifierDoubleQuote
                          [type]::Missing,      # ConsecutiveDelimiter
                          [type]::Missing,      # Tab ($true if the file is Tab delimited; default = $false)
                          [type]::Missing,      # Semicolon ($true if the file is Semicolon delimited; default = $false)
                          [type]::Missing,      # Comma  ($true if the file is comma delimited; default = $false)
                          [type]::Missing,      # Space ($true if the file is space delimited; default = $false)
                          $true,                # Other ($true if the file delimited by the OtherChar; default = $false)
                          $Delimiter,           # OtherChar (Required if Other is True; Specifies the delimiter character)
                          $FieldInfo)

# now get the workbook and worksheet in variables
$MyWorkbook  = $Excel.WorkBooks.Item(1)
$MyWorkSheet = $MyWorkBook.WorkSheets.Item(1)