d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Asses - Organize - Excel File Sorting
Add Reply New Topic New Poll
Member
Posts: 5,575
Joined: Dec 30 2019
Gold: 2,125.50
Apr 6 2023 04:47pm
I believe that my want is beyond the scope of traditional excel formulas. So I am posting here.
We export customer orders from our big commerce store in an excel file format. 1 column of this sheet has 3 variables that are not further organized in columns. SKU - Color - Quantity. Is there a way to asses, determine, organize this data autonomously?

Currently the standard procedure is to physically transfers the data from excel sheet to paper with human handwriting. Which can be swift but error rate is high. Any insight or direction is appreciated.
Retired Moderator
Posts: 7,027
Joined: Dec 19 2013
Gold: 877.50
Trader: Trusted
Apr 8 2023 11:11am
Quote (vacantdesires @ 6 Apr 2023 18:47)
I believe that my want is beyond the scope of traditional excel formulas. So I am posting here.
We export customer orders from our big commerce store in an excel file format. 1 column of this sheet has 3 variables that are not further organized in columns. SKU - Color - Quantity. Is there a way to asses, determine, organize this data autonomously?

Currently the standard procedure is to physically transfers the data from excel sheet to paper with human handwriting. Which can be swift but error rate is high. Any insight or direction is appreciated.


What does this mean? What is the actual issue that you're encountering that's stopping you from using the typical excel export methods? Just trying to understand a little bit better
Member
Posts: 5,575
Joined: Dec 30 2019
Gold: 2,125.50
Apr 8 2023 11:20am
Quote (Soroush @ Apr 8 2023 12:11pm)
What does this mean? What is the actual issue that you're encountering that's stopping you from using the typical excel export methods? Just trying to understand a little bit better


Let me share an example;

This is the file; notice how there is a single parameter for every column.


Now here, column AV has multiple parameters per the 1 column
ID/QTY/SKU/COLOR


I had hired an alleged excel professional and they informed me that it couldnt be done with excels current formulas. I am not certain since I do not know excel very well.
Retired Moderator
Posts: 7,027
Joined: Dec 19 2013
Gold: 877.50
Trader: Trusted
Apr 8 2023 11:25am
Quote (vacantdesires @ 8 Apr 2023 13:20)
Let me share an example;

This is the file; notice how there is a single parameter for every column.
https://i.imgur.com/rIdJSeB.png

Now here, column AV has multiple parameters per the 1 column
ID/QTY/SKU/COLOR
https://i.imgur.com/VLWGYBR.png

I had hired an alleged excel professional and they informed me that it couldnt be done with excels current formulas. I am not certain since I do not know excel very well.


Okay I understand the data much better now, but I am still wondering what it is you need to achieve.

Do you need to export every single column, but the problem is specifically with the AV column which holds multiple values, rather than just one like the rest of the sheet. So you want to export every column, and then treat AV how?

To be transparent, I'm not gifted in excel or anything, but I'm pretty sure it would be fairly straightforward to use a python script to do the export for you.. so the way I am thinking of this is less in excel terms and more in object oriented programming
Member
Posts: 5,575
Joined: Dec 30 2019
Gold: 2,125.50
Apr 8 2023 11:40am
Quote (Soroush @ Apr 8 2023 12:25pm)
Okay I understand the data much better now, but I am still wondering what it is you need to achieve.

Do you need to export every single column, but the problem is specifically with the AV column which holds multiple values, rather than just one like the rest of the sheet. So you want to export every column, and then treat AV how?

To be transparent, I'm not gifted in excel or anything, but I'm pretty sure it would be fairly straightforward to use a python script to do the export for you.. so the way I am thinking of this is less in excel terms and more in object oriented programming


This is wonderful and I want to thank you for taking the time.
I only need the information from the AV column sorted and organized.
Here is an example of the data converted to paper via written hand. You may notice the error I made just in creating the example.


This is the first 3 lines of data from the AV Column. Which is as follows;
Product Details
Product ID: 78, Product Qty: 1, Product SKU: G36, Product Name: redact, Product Weight: 5.0000, Product Variation Details: Color: Carbon Fiber, Make & Model: redact, Handing: Right Hand, Product Unit Price: 39.96, Product Total Price: 39.96
Product ID: 78, Product Qty: 1, Product SKU: G43X, Product Name:redact, Product Weight: 5.0000, Product Variation Details: Color: Tactical Black, Make & Model: redact, Handing: Right Hand, Product Unit Price: 39.96, Product Total Price: 39.96
Product ID: 78, Product Qty: 1, Product SKU: 3.8, Product Name: redact, Product Weight: 5.0000, Product Variation Details: Color: OD Green, Make & Model: redact, Handing: Right Hand, Product Unit Price: 39.96, Product Total Price: 39.96
Retired Moderator
Posts: 7,027
Joined: Dec 19 2013
Gold: 877.50
Trader: Trusted
Apr 11 2023 11:10am
Just posting to update anybody who might need to solve this in the future, or has any ideas for improvement on what I did:

Made a simple python script to turn the excel into json, but directly pointing at the column in question so it didn't return too much data. Broke that column down and then just looped through the json to print the desired values to a text file.
Go Back To Programming & Development Topic List
Add Reply New Topic New Poll