How to create an array like this in powershell?

Question

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)

Bro code-2, code-3, your code also working fine to assign values but thats not working with Text-To-Columns function.... giving error — Dinesh Kumar
– Dinesh Kumar, Commented Jul 3, 2021 at 15:32
if I run e.g. $var1 = (1,2),(2,2),(3,2); $var2 = @(1..3) | ForEach-Object { ,$(@($_,2)) then Compare-Object $var1 $var2 -IncludeEqual shows that objects $var1 and $var2 are identical… — JosefZ
– JosefZ, Commented Jul 3, 2021 at 18:31
check this code bro, i have placed two files in below link you can take and run it on your system github.com/dinesh-ponnusamy/powershell-code comment line-32 and uncomment line-29 vice-versa to see the error — Dinesh Kumar
– Dinesh Kumar, Commented Jul 3, 2021 at 19:34
Sorry, I can't use Excel.Application COM object not having valid Excel license… — JosefZ
– JosefZ, Commented Jul 3, 2021 at 21:20

iRon · Accepted Answer · 2021-07-05 07:48:07Z

2

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 ',')

edited Jul 5, 2021 at 7:48

answered Jul 5, 2021 at 7:31

iRon

24.4k10 gold badges60 silver badges107 bronze badges

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

1 Comment

Daniel Over a year ago

It works! Nice find. I tried many things myself but Invoke-Expression didn't even cross my mind +100 to iRon

mclayton · Accepted Answer · 2021-07-03 22:11:24Z

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:

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:

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

do have any idea how to achieve this because, i m currently using this code $var=(1,2),(2,2),.....(350,2) I want to optimize the process that's y I raised the question. I m ok with for-loop, for-each, or single line assignment anything......

Collectives™ on Stack Overflow

How to create an array like this in powershell?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related