Dynamically adding a column to a CSV output file

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
Bitshir Ling
Posts: 5
Last visit: Wed Dec 16, 2020 1:11 pm
Has voted: 1 time

Dynamically adding a column to a CSV output file

Post by Bitshir Ling »

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.
Customerfile.ps1
(3.27 KiB) Downloaded 64 times

jvierra
Posts: 14867
Last visit: Fri Jul 23, 2021 7:52 pm
Answers: 12
Has voted: 3 times
Been upvoted: 10 times

Re: Dynamically adding a column to a CSV output file

Post by jvierra »

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

Bitshir Ling
Posts: 5
Last visit: Wed Dec 16, 2020 1:11 pm
Has voted: 1 time

Re: Dynamically adding a column to a CSV output file

Post by Bitshir Ling »

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.

Bitshir Ling
Posts: 5
Last visit: Wed Dec 16, 2020 1:11 pm
Has voted: 1 time

Re: Dynamically adding a column to a CSV output file

Post by Bitshir Ling »

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.

jvierra
Posts: 14867
Last visit: Fri Jul 23, 2021 7:52 pm
Answers: 12
Has voted: 3 times
Been upvoted: 10 times

Re: Dynamically adding a column to a CSV output file

Post by jvierra »

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.

Bitshir Ling
Posts: 5
Last visit: Wed Dec 16, 2020 1:11 pm
Has voted: 1 time

Re: Dynamically adding a column to a CSV output file

Post by Bitshir Ling »

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

jvierra
Posts: 14867
Last visit: Fri Jul 23, 2021 7:52 pm
Answers: 12
Has voted: 3 times
Been upvoted: 10 times

Re: Dynamically adding a column to a CSV output file

Post by jvierra »

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.

Bitshir Ling
Posts: 5
Last visit: Wed Dec 16, 2020 1:11 pm
Has voted: 1 time

Re: Dynamically adding a column to a CSV output file

Post by Bitshir Ling »

Enjoy your day.

Locked