Sign in
 
HomeWindows Server 2012Windows Server 2008 R2Windows Server 2003LibraryForums
Windows Server TechCenter >
> 
> 
Powershell script to transpose rows into columns in a xls file

Answered Powershell script to transpose rows into columns in a xls file

  • Wednesday, May 23, 2012 2:42 PM
     
     
    Sign In to Vote
    0

    Hi Gurus,

    Is there any way I can transpose rows to columns in a xls file by using Powershell? This is a sample data view of my xls file -

    Store# Item# Item# Item# Item# Item#

    2 10101 20202 30303 40404 50505

    3 121212 343434 454545 565656 787878

    4 10101 343434 12345 

    What I want in my xls is in a two-column format by Store# Item# as below -

    Store# Item#

    2 10101

    2 20202

    2 30303

    2 40404

    2 50505

    3 121212 

    3 343434

    3 454545

    3 565656

    3 787878

    4 10101

    4 343434

    4 12345

    ..........

    Some points to note -

    1. Item numbers can be repeating for stores. I.e We can have same item numbers for different stores.

    2. The row length for every stores is not the same. Which means one store can have different number of items listed with it.

    3. There are approximately 1800 stores like this all in the column A presently, and the items associated with them are all spread across the columns which go as far as 106th (max number of items in a store).

    4. My requirement is to transpose the items per store into column B. Repeat the store number in column A for as many items as it has in column B. Changeover to the next store number and continue the above processing for all the stores.

    I have written a small excel macro  to do this with the transposed data in different tabs on the same spreadsheet, but xls is complaining about the available resources on my computer when I am trying to do it for as many as 100 tabs at a time. It just doesnt like the amount of resources my Lenovo T410 is assigning to it, even with no other apps running on it for me.

    Any help is much appreciated.

    Thank You

    Sanders.

     

All Replies

  • Wednesday, May 23, 2012 4:10 PM
     
     
    Sign In to Vote
    0

    this isn't about transposing rows and colums, but about reading information from one file (a csv) and summarizing it in a report file (could be a csv).

    the trouble with your input csv file is that some of the column names are identical. That will, I am sure, wreak havoc for the import-csv cmdlet.

    can you show us the powershell code you have written so far?

    Al Dunbar

     
  • Wednesday, May 23, 2012 5:41 PM
     
     
    Sign In to Vote
    0

    Thanks Al. I guessed that could pose a problem for it. As I've mentioned, I have a VB macro written for doing this. It is working and is creating the transposed data into different tabs and then later merging all the tabs into one file. But, I cannot run that macro on more than 75 tabs at a time because of resource constraints. I was wondering if there could be a solution Powershell may offer which is not as resource-intensive as excel macros are.

    Sanders

     
  • Thursday, May 24, 2012 4:22 AM
     
     Answered
    Sign In to Vote
    0

    yes, it can be done with powershell. what part of the script are you having problems with? Or is it just that you need help getting started?

    Also, what do you mean by "transpose"? To me it means to convert rows to columns like this:

    store,item,item,item,item
    A,123,234,345
    B,131,2424

    to this:

    store,A,B
    item,123,131
    item,234,2424
    item,345
    item

    As for getting started, you need to find out how to read data in from a .xls file, and how to write out to a .xls file. I'm pretty rusty on that part.

    once read in you would process like this:

       for each record in the input:
          for each item column in the record:
             write: storenumber, itemnumber
          next
       next

    This is not powershell, but a rough form of "pseudocode" intended to define the logic and flow of the algorithm. To make it work, you'd just need to convert it into powershell.

    Al Dunbar