About to start a new project & looking for advice

[McP2143]

I work at a lumber yard and I have a bunch of text files (called pick lists) that start with about twenty to forty lines of pointlessness then gets to the data needed. It looks like this:
1,2x4,16',2100
2,2x4,14',#2
3,2x6,12',#2
1,2x4,10',#2
1,2x4,16',2100
2,2x4,14',#2
1,2x4,10',#2
3,2x6,12',#2
3,2x6,12',#2
......
QTY,SIZE,LENGTH,GRADE
I'm the guy that gets handed this printout and gets to count all this out (they are much longer like 2-3 pgs.) I know I can write something, somewhere to sort through these files and just print out the added QTY of each instance of similar size, length & grade. I just don't know where to start.
Thanks for any input

 

[BriceH]

This could be done fairly easily with a Python script. I would be happy to write it for you, but I am extremely low on time right now. If you don't need it before June 10th, I can write it for you.

 

[McP2143]

Wow I didn't expect an offer to write it for me!! Thank you Brice. Waiting is not an issue at all. I was planning on teaching myself some lessons in code as I assumed this would be fairly easy. I will get a few of the full files and post them here Monday night after work.

 

[BriceH]

No problem. Just PM me around June 9th or June 10th. It won't take too long and will be really good practice for me.

 

[BriceH]

Can you tell me what you'd like the program to display for the sample you posted above?

 

[BriceH]

I just finished the script. I have attached it and a sample data file. This is how it would work for the sample data you gave me above. (The file Sample Data.txt must be in the same folder as this Python script.)

Please enter the path to a data file: [b]Sample Data.txt[/b]
Size Quantities:
 2x4: 8
 2x6: 9
Length Quantities:
 14': 4
 16': 2
 10': 2
 12': 9
Grade Quantities:
 #2: 15
 2100: 2

By the way, you must install Python before using this script. To do so, please click here and you will download the 64-bit version.

 

[McP2143]

1,2x4,16',2100
2,2x4,14',#2
3,2x6,12',#2
1,2x4,10',#2
1,2x4,16',2100
2,2x4,14',#2
1,2x4,10',#2
3,2x6,12',#2
3,2x6,12',#2
......

for this example an output would be

9,2x6,12',#2
2,2x4,10',#2
4,2x4,14',#2
2,2x4,16',2100

This is a trimmed down example the real deal has many different lengths, sizes, and grades. I would also like any output of this to be in a new file. Thanks again.

 

[BriceH]

Oh, okay. I'll make some modifications to it to do that.

 

[BriceH]

I have now modified it to do everything you want, including putting the output in a new file. Please report back on how well this works, and please rep me if you find it useful. Thanks.

IMPORTANT: Make sure to extract the zip file before using the script.

 

[dsperber]

I have now modified it to do everything you want, including putting the output in a new file. Please report back on how well this works.

Seems to work exactly as expected. Very nice.

Since the input TXT file is clearly in a CSV (comma-separated variable) format that is suitable for importing into Excel, it also would seem that a spreadsheet solution with a simple SUM/DSUM function of some kind should also exist... since the data is already prepared and it's just a matter of adding the values in cells from one column based on matching cells in other columns being the criteria.

Give me one day.

(very simple COBOL program as well)

 

[dsperber]

Pivot tables. That's the perfect Excel approach for this kind of data analysis.

I need to work out how to get the "answers" expressed line-by-line, showing the total quantity along with the detail column values of size, length, and grade, so that the "answers" can be exported as a TXT file to produce the final "pick list" for loading the truck.

 

[jimbo45]

Hi there
I might be being obtuse but surely the OBVIOUS way to do this is via an EXCEL spreadsheet as others have already posted.

Plenty of examples -- just google.

Should take you all of 90 Secs to finish it.


Excel exports to Text / CSV etc etc easily.

Actually you could be even more sophisticated -- download to say a Mobile smart phone or direct to the truck drivers "Way bill" -- this sort of app is becomming more and more into use.


Cheers
jimbo

 

[McP2143]

Thank you everyone for all the great advice.

I will try to work on this myself in excel, for a lesson if nothing else. For BriceH and anyone else interested I have attached a sample order. I have gone into much greater detail than before. So here it is to the best of my Sunday morning memory. I will post an EXACT .stk file tomorrow night. View attachment sampleorder.txt

 

[dsperber]

I will try to work on this myself in excel, for a lesson if nothing else.

Google "Excel pivot table" and take one of the many tutorials. This is the right approach to getting the answers (I have that much done).

Haven't had time yet to work on presenting the answers in a user-friendly set of cells to facilitate exporting the answers (with underlying details as qualifiers) to produce the final list.

For BriceH and anyone else interested I have attached a sample order. I have gone into much greater detail than before. So here it is to the best of my Sunday morning memory. I will post an EXACT .stk file tomorrow night.

I'll use this sample as a real prototype.

 

[dsperber]

Take just the pick list section TXT from your sheet... remove blank lines. Import it into an Excel spreadsheet. It is "delimited" using "commas". The rest of the dialog wizard you can just OK/next to.

I'd recommend starting from the prototype, and deleting the old detail data rows so that just the title row remains. When you import the next data it will be placed in the rows following the title. The presence of the title row is crucial to the pivot table concept.

Then, highlight the complete set of cells that should be part of the table, starting from the first title cell (Quantity) and running through the last detail row cell (Grade).

Then, click on the "Insert" item on the menu bar, and push the "PivotTable" button at the left of the "Tables" group on the left side of the button bar. The selected range from your just imported will appear in the table/range area, and the "new worksheet" radio button will be pre-checked. Just push OK.

The pivot table area (shown on the left side of the presentation) will initially be empty. The pivot table field list from which you can select the values to be "spun" are shown in the upper-right. Magically, Excel knows that size, length and grade are "row labels" so if you check those items in the "choose fields to add to report" they will appear down in the "row labels" quadrant area.

And if you then check the "quantity" field, it will be placed in the "sum values" quadrant area.

And magically, on the left side, the pivot table will appear... with all of the detail row values "spun" and "totaled", and it will look like this:

This matches your sample results (except that your 2x4, 6-0-0, #2 is wrong... it should be 4, not 3 as you show):

111024A Pick List
1,2x6,14-0-0,2100
2,2x6,14-0-0,1650
2,2x6,10-0-0,2100
6,2x4,16-0-0,2100
2,2x4,14-0-0,2100
1,2x4,14-0-0,#2
4,2x4,12-0-0,#2
3,2x4,6-0-0,#2
​

Since the table itself will be of varying length, you should then select the complete table range (starting from the first row label cell to the Grand Total number, push the Page Layout item on the Menu bar, and then push the Print Area -> Set print area item in the Page Setup group of buttons. You can experiment with printing the table, and you can also push the "additional page setup" arrow (at the right side of the "Page Setup" bar) to get additional printing options for titling as you see fit.

Should get you started.

I believe if you save your final table spreadsheet, then the next time you open it up and delete the existing data rows, and import the new data rows onto the data sheet, that the previously established and checked "row labels" and "sum items" will be all set from before, and the pivot table sheet should instantly rebuild itself with the new pick list row data. You should not have to do anything more, other than probably redefine the new print area and print it.

 

[McP2143]

This matches your sample results (except that your 2x4, 6-0-0, #2 is wrong... it should be 4, not 3 as you show)

Case and point!!
And I consider myself good at math too!!
You should see what happens when afternoon shift tries to do this!!

I did this in Excel! Thanks dsperber.
Any thoughts on how to quickly get dozens of these printed?

My first thoughts on this had my boss selecting the days orders > drag and drop > printed in new style. Or add a link to the send to menu.

I don't know if this is possible with excel I will see what i can do.

Simplicity is the key to making this happen for me on a daily occurrence.

 

[dsperber]

I did this in Excel! Thanks dsperber.

Yes, you can drag labels to the "columns" quadrant, as I see you've done, to make the presentation 2-dimensional.

Also, notice that both the "row labels" and "column labels" columns in the table have the dropdown arrow controls on them, indicating that they can be selected for "data filter". In other words, if you wanted to present just a subset of the total pick list, for a particular board size or length or grade, you could click on the dropdown arrow, click on the "select all" item to UNCHECK all of them, and then just check that one (or more) subset value you were looking particularly for.

In other words, you can produce subsets of the table, for specific values in each of the fields... spun and totaled/sub-totaled by just the values you're interested in spinning and printing.

Also, the rows and columns of the presented pivot table are really just an ordinary spreadsheet, like any other spreadsheet. So you can "format" the title heading row, set fonts and colors, set alignment (e.g. "center"), etc. to make it look and print better.

for example, I see that the "#2" grade got left justified in the title, whereas the numeric quantity underneath the column heading got right justified. You might want to CENTER everything in the table, both column headings in the title row as well as contents, just to improve readability.

Any thoughts on how to quickly get dozens of these printed?

File -> Print Preview, to enter the print preview dialog for the table. Then you can get into Page setup, set up headers and footers for printing, center it as you want, etc.

You can then push the PRINT icon button and print as many as you want.

Again, I believe if you SAVE the resulting XLSX workbook then everything you've done using the last data set will be saved, and will be instantly applicable to the next day's set of data... including print setup, etc. Tomorrow, if you first delete the previous set of data rows (under the column heading row), and then select the four columns A-D in the next row (row 2), and then import Data -> from text, then the new data will be imported and placed into rows 2-n, leaving the column widths and separator positions, title row, colorization, etc., just as they were. You're only inserting brand new data, and the generated pivot table on the other sheet will instantly rebuild itself.

Simplicity is the key to making this happen for me on a daily occurrence.

You'll eventually figure out a daily workflow to get each day's data imported, and then print out whatever you want from that day's pivot table.

Excel. It's your friend, for lots more than you might imagine.

 

[McP2143]

Here are some examples of full data files that I need to pull the picklist from.

Note: The original file extension was .stk I changed it to .txt for upload

Sample Picklists :View attachment 110007G.txt

View attachment 110007D.txt

View attachment 110087k.txt

Ps. I have gotten quite a bit out of excel and am now examining BriceH's python script closer. I like it! It has great potential to do all that I want very efficiently.

 

[dsperber]

View attachment 158432 - 110087k.txt

Or, 2-dimensional:

 

[McP2143]

I made a couple adjustments to briceh's program.

Drag & Drop

Still working on it but this works perfectly fine.

View attachment Picklist Counter Drag And Drop File.rar