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, Jul 03 '21 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, Jul 03 '21 at 18:31
check this code bro, i have placed two files in below link you can take and run it on your system https://github.com/dinesh-ponnusamy/powershell-code comment line-32 and uncomment line-29 vice-versa to see the error — Dinesh Kumar, Jul 03 '21 at 19:34
Sorry, I can't use `Excel.Application` COM object not having valid _Excel_ license… — JosefZ, Jul 03 '21 at 21:20
Where does this data come from? Sounds like a CSV file that uses the pipe symbol `|` as delimiter. In that case, simply convert that to the delimiter char your Excel understands. Something like `Import-Csv -Path 'theOriginal.csv -Delimiter '|' | Export-Csv -Path 'theNew.csv -UseCulture -NoTypeInformation'`. Next, simply double-click this new file to open in Excel. NoTextToColumns needed then. — Theo, Jul 04 '21 at 09:57
Requirement is pipe delimited txt file to excel conversion. so i opened pipe delimited txt file in excel, then TextToColumn and adding some headers and some excel functionalities and saving as an xlsx. — Dinesh Kumar, Jul 06 '21 at 19:37

iRon · Accepted Answer · 2021-07-05T07:48:07.063

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 05 '21 at 07:48

answered Jul 05 '21 at 07:31

iRon

20,463
10
53
79

It works! Nice find. I tried many things myself but Invoke-Expression didn't even cross my mind +100 to iRon – Daniel Jul 05 '21 at 07:37

score 1 · Answer 2 · answered Jul 03 '21 at 22:11

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...... — Dinesh Kumar, Jul 03 '21 at 23:12

How to create an array like this in powershell?

2 Answers2