asked on

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

Kimputer

Seems like it should be possible. However, I have absolutely now idea what you mean with range A414:473?

robmarr700

ASKER

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

Kimputer

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?

robmarr700

ASKER

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

robmarr700

ASKER

Does this make more sense?

Rob

Kimputer

Ok got it, trying to code now.

robmarr700

ASKER

Hi Kimputor,

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

Rob

Kimputer

Hi Rob,

trying to finish later today (volunteering here, as I have a full time job), sorry!

robmarr700

ASKER

No probs!

Thanks
Rob

Kimputer

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

Kimputer

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

robmarr700

ASKER

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

robmarr700

ASKER

Yes I would only want the 3 supplier letters

Thanks
Rob

Kimputer

Sure here it is
EE-SalesReportingUtility--2-.xlsm

robmarr700

ASKER

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