SAPIEN Forums

Good Morning

I am writing a script to read a csv file of customers with a single customer number but multiple account numbers. The end results should be a single row for each customer but multiple columns for each account number and type.

Cust_Num ACC_Num Acc_Type Customer_Name
352 10832 25 John Smith
352 10200 30 John Smith
352 32011 20 John Smith
352 84233 45 John Smith
211 11852 25 Julie Baxter
211 80731 25 Julie Baxter
843 43901 35 Cathy Right
332 50912 30 William Grant
332 73418 20 William Grant

The end results should look like below:
Cust_Num Customer_Name Acc_Num1 Acc_Type1 Acc_Num2 Acc_Type2 Acc_Num3 Acc_Type3 Acc_Num4 Acc_Type4
352 John Smith 10832 25 10200 30 32011 20 84233 45
211 Julie Baxter 11852 25 80731 25
843 Cathy Right 43901 35
332 William Grant 50912 30 73418 20

Note that the number of account numbers is unknown so I need to dynamically add columns for account number and types based on what is in the csv file. The result should be output to a new csv file. I have attached the code. I am stumped . Any assistance would be greatly appreciated. Note that I created a second CSV file with the customer number only to allow do a nested for loop.

A CSV cannot have a variable number of columns per row. You will need to determine the number of columns in advance.
This is an exercise in data relations and would normally be done with two CSV files that would be joined on the relational key.

To learn what a CSV is you can start with the following link:

https://en.wikipedia.org/wiki/Comma-separated_values

Actually I know what a CSV file is. What you are proposing is that every time I have to run this report is that I go in and count the number accounts and set the number columns accordingly. This seems highly inefficient and basically defeats the purpose of writing the script. Basically, your conclusion is that it cannot be done in powershell.

Below is an example of the input file:
Cust_Num ACC_Num Acc_Type Customer_Name
352 10832 25 John Smith
352 10200 30 John Smith
352 32011 20 John Smith
352 84233 45 John Smith
211 11852 25 Julie Baxter
211 80731 25 Julie Baxter
843 43901 35 Cathy Right
332 50912 30 William Grant
332 73418 20 William Grant

The desired result is as per below:
Cust_Num Customer_Name Acc_Num1 Acc_Type1 Acc_Num2 Acc_Type2 Acc_Num3 Acc_Type3 Acc_Num4 Acc_Type4
352 John Smith 10832 25 10200 30 32011 20 84233 45
211 Julie Baxter 11852 25 80731 25
843 Cathy Right 43901 35
332 William Grant 50912 30 73418 20

I did use two CSV files in the script.

You will have to learn what a CSV files is and what a data relation is. The code you request cannot be written in one line.
Almost all PS users with no programming or data experience ask a question like this. If you search you will find more information that will help you understand how to write the code. We cannot write the code for you. We can answer any questions you may have as you attempt to write the code. Start by searching for examples and articles on how to use CSV files.

I was not asking for you to write the code for me. Did you look at the code that I submitted?

Bitshir Ling wrote: ↑Wed Dec 16, 2020 9:37 am I was not asking for you to write the code for me. Did you look at the code that I submitted?

Your code doesn't make any sense. It is just a bunch of lines that cannot do anything due to coding, syntax and variable errors.

I suggest either Excel or starting by extracting the two relational data structures first. This can be done by doing a unique selection and a simple selection of the account and customer number. Then you would do a join cross-join on these two tables. Since PowerShell cannot do a cross-join then Excel would be best.

Enjoy your day.