• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

Macro to full parse text file into use-able data set?

The attached file name stockuseage.txt is the raw data for a stock usage file for all of our current supplier stock.

They display stock usage for products during a four month period.

I basically need a macro to split and modify the data from the original text for into relevant columns so it can be worked with in excel.

This is a macro that will be run repeatedly for different supplier stock files.

The attached sheet (manual example) shows the data which needs to be kept between the range A414:473. The macro would have to cover the whole data range.

note the product descriptions often push onto two lines. These need to be consolidate onto the one line whilst keeping the relevant data aligned.

Also note each page is assigned to a different product group. These groups need to be pulled from above the data and aligned next to the relevant product code (see manual example).

Any questions just ask

Rob
stockuseage.txt
stock-useage-macro--manual-example-.xlsx
0
robmarr700
Asked:
robmarr700
  • 28
  • 23
  • 7
1 Solution
 
KimputerCommented:
Seems like it should be possible. However, I have absolutely now idea what you mean with range A414:473?
0
 
robmarr700Author Commented:
Sorry if I wasn't clear this is the range I have manually modified after opening the raw data file in excel. See attached.

The range is highlighted in yellow.

Rob
stockuseage.csv
0
 
KimputerCommented:
CSV files cannot be highlighted. But then still I don't see how this range is of any importance? Will you always modify it? Never? Why did you change it in the first place?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
robmarr700Author Commented:
I just selected this range to demonstrate what I was trying to achieve on a small section of the data.

I would want a macro to modify the entire range of data automatically.

Rob
0
 
robmarr700Author Commented:
Does this make more sense?

Rob
0
 
KimputerCommented:
Ok got it, trying to code now.
0
 
robmarr700Author Commented:
Hi Kimputor,

How's it coming along? Have you come across any issues I may be able to help with?

Rob
0
 
KimputerCommented:
Hi Rob,

trying to finish later today (volunteering here, as I have a full time job), sorry!
0
 
robmarr700Author Commented:
No probs!

Thanks
Rob
0
 
KimputerCommented:
This is the current code I have, it will open the text file (please adjust in code to get the correct file at the correct location), add a new sheet, then start copying the rows from the imported text file.
Please let it run a few seconds (based on your sample text), or allow even half a minute for slower computers

Sub importtxt()
    
  
    Application.ScreenUpdating = False
    
    Dim wb As Workbooks
    Dim wb2 As Workbook
    Dim or_sheet, import_sheet As Worksheet
   
    Set wb = Workbooks
    
    wb.OpenText Filename:="C:\Users\Kimputer\Downloads\stockuseage.txt", _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 2), Array(25, 2), Array(57, 2), Array(65, 1), Array(77, 1), Array(90, 1), Array(103, 1), Array( _
        116, 1), Array(129, 1)), TrailingMinusNumbers:=True  'change text file location!
    
    Set wb2 = ActiveWorkbook
    
    wb2.Worksheets.Add().Name = "import"
    Set or_sheet = wb2.Worksheets(2)
    Set import_sheet = wb2.Worksheets(1)


   
    'header import
    
    import_sheet.Cells(1, 5).Value = "Qty Used"
    import_sheet.Cells(1, 6).Value = "Qty Used"
    import_sheet.Cells(1, 7).Value = "Qty Used"
    import_sheet.Cells(1, 8).Value = "Qty Used"
    import_sheet.Cells(1, 9).Value = "Free"
    import_sheet.Cells(2, 1).Value = "Supplier"
    import_sheet.Cells(2, 2).Value = "Stock Code"
    import_sheet.Cells(2, 3).Value = "Description"
    import_sheet.Cells(2, 4).Value = "Cat."
    import_sheet.Cells(2, 5).Value = or_sheet.Cells(9, 4).Value
    import_sheet.Cells(2, 6).Value = or_sheet.Cells(9, 5).Value
    import_sheet.Cells(2, 7).Value = or_sheet.Cells(9, 6).Value
    import_sheet.Cells(2, 8).Value = or_sheet.Cells(9, 7).Value
    import_sheet.Cells(2, 9).Value = or_sheet.Cells(9, 8).Value
    
    import_row = 3
    

    For i = 1 To or_sheet.UsedRange.Rows.Count Step 1
        
        
        If Left(or_sheet.Cells(i, 1).Value, 7) = "PRODUCT" Then
            ProductCode = Mid(or_sheet.Cells(i, 1).Value, 14, Len(or_sheet.Cells(i, 1).Value) - 13) + or_sheet.Cells(i, 2).Value
            
            i = i + 6
            
            Do While (Replace(Replace(Trim(or_sheet.Cells(i, 1).Value2), " ", ""), Chr(9), "") <> "") Or (Replace(Replace(Trim(or_sheet.Cells(i, 2).Value2), " ", ""), Chr(9), "") <> "")
                If or_sheet.Cells(i, 1).Value <> "" Then
                    'copy to import
                    import_sheet.Cells(import_row, 1).Value = ProductCode
                    import_sheet.Cells(import_row, 2).Value = or_sheet.Cells(i, 1).Value
                    import_sheet.Cells(import_row, 3).Value = or_sheet.Cells(i, 2).Value
                    import_sheet.Cells(import_row, 4).Value = or_sheet.Cells(i, 3).Value
                    import_sheet.Cells(import_row, 5).Value = or_sheet.Cells(i, 4).Value
                    import_sheet.Cells(import_row, 6).Value = or_sheet.Cells(i, 5).Value
                    import_sheet.Cells(import_row, 7).Value = or_sheet.Cells(i, 6).Value
                    import_sheet.Cells(import_row, 8).Value = or_sheet.Cells(i, 7).Value
                    import_sheet.Cells(import_row, 9).Value = or_sheet.Cells(i, 8).Value
                    import_row = import_row + 1
                Else
                   import_sheet.Cells(import_row - 1, 3).Value = import_sheet.Cells(import_row - 1, 3).Value + or_sheet.Cells(i, 2).Value
                End If
                i = i + 1
            Loop
            
        End If
    Next
        
    Application.ScreenUpdating = True
    

End Sub

Open in new window

0
 
KimputerCommented:
Just in case you only want the 3 letters supplier code, find replace:

ProductCode = Split(Mid(or_sheet.Cells(i, 1).Value, 14, Len(or_sheet.Cells(i, 1).Value) - 13) + or_sheet.Cells(i, 2).Value, " - ")(0)

Open in new window

0
 
robmarr700Author Commented:
Sorry I don't exactly know what you mean.

Most of my reports (source data txt. ) just need to be ran through the attached utility. This is one an expert created for me.

Would it be possible for you to add a button to the utility whereby all I have to do is click a button called 'import and parse full stock sheet (.txt file)' then  select the relevant txt. file and the macro will run automatically from there into a new excel worksheet?

Rob
EE-SalesReportingUtility--2-.xlsm
0
 
robmarr700Author Commented:
Yes I would only want the 3 supplier letters

Thanks
Rob
0
 
KimputerCommented:
0
 
robmarr700Author Commented:
Thanks,

Just a couple of initial observations.

1. Alot of the qty used and free stock data seems to have gone missing.

for example A0021.0

See source data for difference's. This seems to have happened on a number of stock codes.

2. You may want to check that "" aren't causing phase shifts. I have had this problem before with my customer orders reports. These reports are very similar in structure and layout to the stock reports I'm asking you to modify. I've attached one for you to look at. If you run it through the 'import and parse customer orders report (.txt file)' button on the utility you may be able to see how some of the issues I've mentioned have been handled previously.
July-Orders.txt
0
 
aikimarkCommented:
how many items should I expect to parse?

Thanks for the heads-up on the missing numbers.
0
 
aikimarkCommented:
The file you just posted is different than the other files you posted.  These are completed orders, not stock usage.
0
 
robmarr700Author Commented:
yes but the macro (button: import and parse customer orders report) for parsing this report may contain similar coding that will also work (or help to highlight) and then rectify the errors picked up in kimputors current macro solution.

Rob
0
 
aikimarkCommented:
I'm using a different parsing technique from the one kimputors is using.  Adjusting what I'm doing shouldn't be too much problem, but you are making some big assumptions in the information you are conveying.

Please answer my question about the expected number of items to be found in the bosch-example.txt file that I am using for my test case data.
0
 
aikimarkCommented:
by my calculations, I should produce 519-520 items with stock codes
0
 
aikimarkCommented:
I'm not sure whether 2608831001 should be included.
0
 
aikimarkCommented:
A0021.0
I do not see that stock code in the bosch-example data
0
 
aikimarkCommented:
Here is a version of the solution from the other (related) question that seems to work with this input file.
Q-28502636.xls
0
 
aikimarkCommented:
Is this stock usage file the exact one you are receiving or have you edited it?
0
 
aikimarkCommented:
I've tweaked the patterns and have improved the capture from 1700 to 5500 out of 6067 items.

Can you tell us something about the quoted strings?

Note: I'm having to clean up the file in order to parse it.
Q-28502636.xls
0
 
aikimarkCommented:
At 5874 parsed items, I'm now within 200 of the 6067 possible items.  In this output, I've forced the stock code column data to be text so that it sorts better.  Also, both prod code and stock code columns are now being sorted.
Q-28502636.xls
0
 
aikimarkCommented:
Another problem I see with is stock codes with spaces.  Some of these look like descriptions.  These 129 items account for more than half of the unparsed items.
110V ELEC MINIMIXER
12611 SMALL
18601 DIRECT
230V ELEC MINIMIXER
300302-P3 PC
300303-P3 PC
35246 3XL
35247 4XL
44116 FREE
44176 FREE
44177 FREE
44179 FREE
44180 FREE
44181 FREE
44182 FREE
44183 FREE
44445 FREE
44513 FREE
44736 FREE
500-172-20 DD
700 GAMMA SB
75661 SMALL
800 ALPHA SB
AD SPACE IN TRADE CNTR
ALUTITAN 770 30 SB
ALUTITAN 770 40 SB
ALUTITAN 770 50 SB
ALUTITAN 770 60 SB
ALUTITAN 770 HB3045SB
ALUTITAN 770 HB4065SB
BLACK & DECKER REBATE
BLUE/YELLOW HANDLE
BOCCIA 450 40 SB
BOCCIA 450 50 SB
BOCCIA 450 60 SB
BOSCH REBATE
CYNO 1200
CYNO 2000
CYNO 40
CYNO GEL
D66 GEL 50
D77GEL 50
DESK DIARIES
DRAPER REBATE
EXHIB DINNER 03
EXHIB LUNCH 03
EXHIBITION HEADER
EXIB ACCOMMODATION 03
GREEN HANDLE
HDS 821 030 GS DN 360+
HDS 821 040 GS DN 360+
HDS 821 050 GS DN 360+
HDS 821 060 GS DN 360+
HDS 821 080 GS DN 360+
HDS 821 100 GS DN 360+
HDS 821 120 GS DN 360+
HDS 821 140 GS DN 360+
HDS 821 160 GS DN 360+
HDS 821 180 GS DN 360+
HDS 821 200 GS DN 360+
HDS 832 030 GS DN 630+
HDS 832 040 GS DN 630+
HDS 832 050 GS DN 630+
HDS 832 060 GS DN 630+
HDS 832 080 GS DN 630+
HDS 832 100 GS DN 630+
HDS 832 120 GS DN 630+
HDS 832 160 GS DN 630+
HDS 832 200 GS DN 630+
HDS 832 250 GS DN 630+
HDS 842 030 GS DN 630+
HDS 842 040 GS DN 630+
HDS 842 050 GS DN 630+
HDS 842 060 GS DN 630+
HDS 842 060 HR DN 630+
HDS 842 060 NR DN 630+
HDS 842 080 GS DN 630+
HDS 842 080 HR DN 630+
HDS 842 080 NR DN 630+
HDS 842 100 GS DN 630+
HDS 842 100 HR DN 630+
HDS 842 100 NR DN 630+
HDS 842 120 GS DN 630+
HDS 842 120 HR DN 630+
HDS 842 120 NR DN 630+
HDS 842 160 GS DN 630+
HDS 842 160 HR DN 630+
HDS 842 160 NR DN 630+
HDS 842 200 GS DN 630+
HDS 842 200 HR DN 630+
HDS 842 200 NR DN 630+
HDS 852 250 GS DN 630+
HDS 852 250 HR DN 630+
HDS 852 250 NR DN 630+
HDS 852 320 GS DN 630+
HDS 852 320 HR DN 630+
HONDA PETROL MINIMIXER
HP14 PLUS
HP7 PLUS
HR2020/1 110V
HR2020/2 240V
HW101 100
MAKITA REBATE
MEM PPE
MIT 500-196-20
MN1300 (D)
MN1400 (C)
MN1604 (9V)
POCKET DIARIES
POWER TOOLSTOP MAY-JUL
PRED 6
PRED 6D
Prod. Type - ABR
Prod. Type - HDT
Prod. Type - MNT
Prod. Type - MNT
Prod. Type - MSR
Prod. Type - PTA
SPONSORSHIP OF THS CON
SUPP PPE
THS REBATE
TOOLSTOP PUBLICATION
TUFF BLCK TRSR 32R32L
TUFF BLCK TRSR 34R32L
TUFF BLCK TRSR 36R32L
TUFF BLCK TRSR 38R32L
TUFF BLCK TRSR 40R32L
TUFF BLCK TRSR 42R32L
WELDING HELMET-JPARKER

Open in new window

0
 
robmarr700Author Commented:
Hi aikimark,

Thank-you for the work you have done so far.

1. Bosch file seems to be fine. The totals match those in the source data so I can't think why there would be any issues.

2. The file is a straight export and has not been edited in anyway.

3. All of the codes bar

Prod. Type - ABR
Prod. Type - HDT
Prod. Type - MNT
Prod. Type - MNT
Prod. Type - MSR
Prod. Type - PTA

Are stock codes and need to be treated in the same way as the rest.

4. Please note I am ultimately looking for this Macro to work on new data sheets where the data will change daily. Do you think this is possible? Attached is the data for today. You may want to try running your macro on this to see if there are any problems.

Rob
stockuseadenew.txt
0
 
aikimarkCommented:
@Rob

What about the other 100+ codes that include space characters?

I had asked about the file because it is so different than the bosch file: different line termination sequences, tab characters, quote characters, and some stock codes that contain spaces.

Now that we're getting close to the end of August, I should ask what you're going to do about the Qty Used columns.  I expect next month's data to cause a misalignment of these columns.
0
 
aikimarkCommented:
This is about as close as I can get without spending a lot of (unbillable) time.  Please inspect the results.
Q-28502636.xls
0
 
robmarr700Author Commented:
Yes those are all stock codes

yes it is a different export. On our system I have the option to bring up a stock useage report based on a specific supplier/product group e.g. Bosch or I can bring up a report to display stock useage for all of our current supplier e.g. this sheet.

I wouldn't have expected the new month to cause misalignment issues as the number of characters used will always be the same regardless of the month we are in.

Thank-you for your help up until now. I will see if another expert can take over the fine tuning from here.

Rob
0
 
aikimarkCommented:
Next month, the left-most Qty column will be for September data.

If you stack the data from these two reports, the August column and September column will occupy the same column.  That is what I mean by misalign.
0
 
robmarr700Author Commented:
I do not want to stack the data. The data on future sheets will be completely independent from previous data.
0
 
aikimarkCommented:
will there be three month's worth of data?
0
 
robmarr700Author Commented:
No the raw data will always display 4 columns 9 (months) worth of data not 3
0
 
aikimarkCommented:
My three figure was only for the month-specific data. I didn't reference the right-most column, which is not month-specific.

Do you understand how the column alignment problem I described?
0
 
robmarr700Author Commented:
Sorry I'm a little confused. There are 4 columns for the months and one (far right) for free stock. This will always be the same.
0
 
aikimarkCommented:
You are right.  There are four month-specific columns and the free stock column.  
Qty Used - AUG	Qty Used - JUL 	Qty Used - JUN	Qty Used - MAY

Open in new window


Next month, I would extect to see
Qty Used - SEP	Qty Used - AUG	Qty Used - JUL 	Qty Used - JUN

Open in new window

0
 
robmarr700Author Commented:
That is correct
0
 
aikimarkCommented:
So, if you have two (or more) months' worth of extracts, you can't just stack then on top of one another or simply concatenate the two files.  Half way down the concatenated file or parsed results, the month-specific columns have shifted to the right.

If you need to use this like a database, it must be normalized - one table for the key data/static data and another table for the monthly and free stock data.

Have you compared the latest parsed results against your expected results?
0
 
aikimarkCommented:
I had a few minutes and looked at the raw and parsed data.  I thought your tab characters were bad until I saw the description text located in the stock code column!
0
 
aikimarkCommented:
This is an improvement, but the actual replacement of the tab characters does produce some strange text.  Sometimes, they should be replaced with a space, sometimes with a comma, and sometimes an empty string.
Q-28502636.xls
0
 
robmarr700Author Commented:
Looks like your'e getting there. I have noticed by summing the monthly and free stock total's that they don't match those on the source data meaning there must be some discrepancies.
0
 
robmarr700Author Commented:
Please refer to stock totals at bottom of the attached sheet
stock--expert-.xls
0
 
aikimarkCommented:
@Rob

Please identify some lines where the data is not parsing the numbers correctly.  I do not have the time to dive into the number discrepancy, although I might have the time to tweak the parsing if you identify where things are going wrong.
0
 
robmarr700Author Commented:
Don't worry I feel you've overworked on this question alone anyway. I'm going to close the question and re-open. Great work on this one. If I could award more points I would.

Rob
0
 
aikimarkCommented:
If you would do the grunt work on this, I will likely be able to tweak the code, providing you with the complete solution.  You just need to compare the monthly sums in the spreadsheet against the product group total values for each group.  You should probably use filtering to make this easier.

Alternatively, you can use the sum/total feature to add sums.
0
 
aikimarkCommented:
Fortunately, the problem existing in the sixth product group using the subtotals feature.  There are dual line entries where the description for the second line begins with a space character.
Example:
MN1500(AA)               Duracell Procell AA Battery   								
                          x 10 (AAPRST)                                 175          163          116           82          255  								
MN1604 (9V)              Duracell Procell 9V Battery   								
                          x 10 (9VPRST)                                  26           12           17            6           12  								

Open in new window

This required one more clean-up operation before beginning the parsing.
Q-28502636.xls
0
 
robmarr700Author Commented:
Apologies I don't know why I accepted this one. Well spotted, the monthly totals match up now. I'm not sure whether the free stock matches the source data? I wouldn't know how to figure this out since there isn't a complete total on the source data. Its imperative that the Free data does match the original data.

As I have mentioned previously this macro will need to be used repeatedly (twice daily) with new data. Occasionally new products will be added. The monthly figures and free stock will change daily.

I have attached a copy of today's stock data (original file). Could you test the macro on this data. If it works successfully could you add a button to the attached utility where all I have to do is click>>>import source text. file and the macro will run from there.
EE-SalesReportingUtility--2-.xlsm
0
 
robmarr700Author Commented:
New stock data attached
stock-29.08.14.txt
0
 
aikimarkCommented:
did you run the new stock file against the workbook I just posted?
0
 
robmarr700Author Commented:
Sorry how would I go about doing that?

Rob
0
 
aikimarkCommented:
There is a constant in the routine with the path.  Change the path.  

How have you been testing the workbooks that I have posted?!?
0
 
aikimarkCommented:
I do not know where the file is, absolute path or relative to the workbook.
I do not know the file is named (or will be named each import).
0
 
robmarr700Author Commented:
I haven't been testing with new data. I have just been looking at the results on the sheets you have been posting.

My last attachment is the file.

Rob
0
 
robmarr700Author Commented:
Do you understand what I mean about adding a button to my master utility?
0
 
aikimarkCommented:
I haven't been testing
It is very dangerous to not test.

Since the latest version of the code seems to be the solution to your question, please accept my comment http:#a40292411 as the solution.  If you need to package the code so that the correct text file is read, open a new question.  Be sure to include a link to this question in your new question.
0
 
aikimarkCommented:
Do you understand what I mean...
Yes.  That is a separate question.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 28
  • 23
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now