CODEWITHSUNDEEP
arrayspowershellpowershell-4.0excel-automation
Dinesh Kumar
Dinesh Kumar

Reputation: 72

How to create an array like this in powershell?

I need to store data like below for TextToColumns Excel automation. I need to implement Code-2 or Code-3 or Code-4 is that any way to achieve? I have more than 350+ data so I cant use Code-1, that's not fair for me.


Code-1: working fine

$var = (1,2),(2,2),(3,2),(4,2),(5,2),(6,2)........(300,2)
$ColumnA.texttocolumns($colrange,1,-412,$false,$false,$false,$false,$false,$true,"|",$var)


Code-2: not Working

$var = @((1,2)..(300,2))
$ColumnA.texttocolumns($colrange,1,-412,$false,$false,$false,$false,$false,$true,"|",$var)


Code-3: not Working

$var = @()
   
#forloop upto 300 
{ $var += ($i,2) }

$ColumnA.texttocolumns($colrange,1,-412,$false,$false,$false,$false,$false,$true,"|",$var)


Code-4: not Working

[array]$var = 1..300 | foreach-object { ,@($_, 2) }
$ColumnA.texttocolumns($colrange,1,-412,$false,$false,$false,$false,$false,$true,"|",$var)

Upvotes: 1

Views: 197

Answers (2)

iRon
iRon

Reputation: 23788

I can't fully explain what happens here but I guess that it is related to the fact that the texttocolumns requires an (deferred) expression rather than an (evaluated) object.

Meaning that the following appears to work for the Minimal, Reproducible Example from @mclayton:

$Var = Invoke-Expression ((1..6 |% { "($_, `$xlTextFormat)" }) -Join ',')

And expect the following to work around the issue in the initial question:

$Var = Invoke-Expression ((1..300 |% { "($_, 2)" }) -Join ',')

Upvotes: 2

mclayton
mclayton

Reputation: 10075

Not an answer - just documenting some research to save others some time...

I can repro the issue here with the following code:

$xl = new-object -com excel.application;
$xl.Visible = $true;

$workbook  = $xl.Workbooks.Add();

$worksheet = $workbook.Worksheets.Item(1);

$worksheet.Range("A1") = "aaa|111";
$worksheet.Range("A2") = "bbb|222";
$worksheet.Range("A3") = "ccc|333";
$worksheet.Range("A4") = "ddd|444";
$worksheet.Range("A5") = "eee|555";
$worksheet.Range("A6") = "fff|666";

which builds a new spreadsheet like this:

enter image description here

If you then run the following it will parse the contents of column A and put the results into columns B and C:

$range  = $worksheet.Range("A:A");
$target = $worksheet.Range("B1");

# XlColumnDataType enumeration
# see https://learn.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype
$xlTextFormat = 2;

# XlTextParsingType enumeration
# see https://learn.microsoft.com/en-us/office/vba/api/excel.xltextparsingtype
$xlDelimited = 1;

# XlTextQualifier enumeration
# https://learn.microsoft.com/en-us/office/vba/api/excel.xltextqualifier
$xlTextQualifierNone = -4142;

$var = (1,$xlTextFormat),(2,$xlTextFormat),(3,$xlTextFormat),(4,$xlTextFormat),(5,$xlTextFormat),(6,$xlTextFormat);

# parse the values in A1:A6 and puts the values in a 2-dimensional array starting at B1
# see https://learn.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns
$result = $range.TextToColumns(
    $target,              # Destination
    $xlDelimited,         # DataType
    $xlTextQualifierNone, # TextQualifier
    $false,               # ConsecutiveDelimiter
    $false,               # Tab
    $false,               # Semicolon
    $false,               # Comma
    $false,               # Space
    $true,                # Other
    "|",                  # OtherChar
    $var                  # FieldInfo
);

which then looks like this:

enter image description here

However, if you change the declaration for $var to

$var = 1..6 | % { ,@($_, $xlTextFormat) };

you get the following error:

OperationStopped: The remote procedure call failed. (0x800706BE)

and the Excel instance terminates.

So there's something different about these two declarations:

$var = (1,$xlTextFormat),(2,$xlTextFormat),(3,$xlTextFormat),(4,$xlTextFormat),(5,$xlTextFormat),(6,$xlTextFormat);

$var = 1..6 | % { ,@($_, $xlTextFormat) };

but what that is eludes me :-S

Upvotes: 1

Related Questions