CNC Data Import

[ajuds06]

I am in need of help to write a macro for importing data from a txt file automatically, and to separate the data into multiple columns by a specific cell input. The only problem is that my txt file imports as a single column and the data isn't exact same amount of rows. So I would like the VBA or Macro to loop to find a specific text and take all the information below it, until it reaches the next cell with a similar name in it. Here's what the data looks like (mind you it's 21000 rows) at a glance. The specific cell criteria is the first cell and 125 lines down is the next similar one.

[TABLE="width: 219"]
<colgroup><col></colgroup><tbody>[TR]
[TD][Magazine0_Pot001][/TD]
[/TR]
[TR]
[TD]Pot_Kind=0[/TD]
[/TR]
[TR]
[TD]PTN=0[/TD]
[/TR]
[TR]
[TD]FTN=0[/TD]
[/TR]
[TR]
[TD]ITN=0[/TD]
[/TR]
[TR]
[TD]Order=0[/TD]
[/TR]
[TR]
[TD]Note=[/TD]
[/TR]
[TR]
[TD]Through_Spindle_Enable=0[/TD]
[/TR]
[TR]
[TD]Through_Spindle_Time=0[/TD]
[/TR]
[TR]
[TD]ATC_Speed=0[/TD]
[/TR]
[TR]
[TD]M60_Disable=0[/TD]
[/TR]
[TR]
[TD]Prohibit=0[/TD]
[/TR]
[TR]
[TD]TL_Alarm_Effective=0[/TD]
[/TR]
[TR]
[TD]Total_Cutter=1[/TD]
[/TR]
[TR]
[TD]Pot_Size=0[/TD]
[/TR]
[TR]
[TD]Prohibit_Rotation=0[/TD]
[/TR]
[TR]
[TD]Empty_Pot=0[/TD]
[/TR]
[TR]
[TD]Irregular_Shape=0[/TD]
[/TR]
[TR]
[TD]Commanded_T_Code=0[/TD]
[/TR]
[TR]
[TD]TLS_Search=0[/TD]
[/TR]
[TR]
[TD]B_Axis_Rot_Prohibit=0[/TD]
[/TR]
[TR]
[TD]One_Touch_Prohibit=0[/TD]
[/TR]
[TR]
[TD]RakuRakuL_Measuring=0[/TD]
[/TR]
[TR]
[TD]Tool_Size=0[/TD]
[/TR]
[TR]
[TD]TSC_Removal_Type=0[/TD]
[/TR]
[TR]
[TD]ExistOnMgz=0[/TD]
[/TR]
[TR]
[TD]CheckH=0[/TD]
[/TR]
[TR]
[TD]CheckD=0[/TD]
[/TR]
[TR]
[TD]InspectedFlag=0[/TD]
[/TR]
[TR]
[TD]ONumber1=0[/TD]
[/TR]
[TR]
[TD]ONumber2=0[/TD]
[/TR]
[TR]
[TD]ONumber3=0[/TD]
[/TR]
[TR]
[TD]TSC_Frequency=0[/TD]
[/TR]
[TR]
[TD]TSC_FLOW_CHECK=1[/TD]
[/TR]
[TR]
[TD]TSC_Frequency_Setting=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_X=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_Y=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_Z=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_X=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_Y=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_Z=0[/TD]
[/TR]
[TR]
[TD]PWRFLR_Retract_Disable=0[/TD]
[/TR]
[TR]
[TD]SisterToolStatus=0[/TD]
[/TR]
[TR]
[TD]TLMgzVibCtrlMode=0[/TD]
[/TR]
[TR]
[TD]UseTlsNo=0[/TD]
[/TR]
[TR]
[TD]MultiPurpose=0[/TD]
[/TR]
[TR]
[TD]AirSpinDataNO=0[/TD]
[/TR]
[TR]
[TD]ImagingAtTCode=0[/TD]
[/TR]
[TR]
[TD]FixedPot=0[/TD]
[/TR]
[TR]
[TD]GroupNo=0[/TD]
[/TR]
[TR]
[TD]ImagingAllTogether=0[/TD]
[/TR]
[TR]
[TD]ImagExist=0[/TD]
[/TR]
[TR]
[TD]SchankCount=0[/TD]
[/TR]
[TR]
[TD]HighAccuracySeatChk=0[/TD]
[/TR]
[TR]
[TD]SpinVibRapidW=0[/TD]
[/TR]
[TR]
[TD]SpinVibRapidA=0[/TD]
[/TR]
[TR]
[TD]SpinVibCutW=0[/TD]
[/TR]
[TR]
[TD]SpinVibCutA=0[/TD]
[/TR]
[TR]
[TD]TSC_Pressure=0[/TD]
[/TR]
[TR]
[TD]AlarmStopType=0[/TD]
[/TR]
[TR]
[TD]Helix=0[/TD]
[/TR]
[TR]
[TD]C1_Kind=0[/TD]
[/TR]
[TR]
[TD]C1_H_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_H_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_D_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_D_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time Actual=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Actual=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Actual=0[/TD]
[/TR]
[TR]
[TD]C1_SL_Upper=0[/TD]
[/TR]
[TR]
[TD]C1_SL_Lower=0[/TD]
[/TR]
[TR]
[TD]C1_AC=0[/TD]
[/TR]
[TR]
[TD]C1_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_Warning=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Action_Type=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Before=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_After=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Length=0[/TD]
[/TR]
[TR]
[TD]C1_Operator_Call=0[/TD]
[/TR]
[TR]
[TD]C1_First_Use=0[/TD]
[/TR]
[TR]
[TD]C1_Spindle_Speed_M6=0[/TD]
[/TR]
[TR]
[TD]C1_Coolant_Kind_M6=0[/TD]
[/TR]
[TR]
[TD]C1_Spindle_Speed_Limit=0[/TD]
[/TR]
[TR]
[TD]C1_Surface_Speed_Limit=0[/TD]
[/TR]
[TR]
[TD]C1_SuspendAlarm=0[/TD]
[/TR]
[TR]
[TD]C1_SuspendWarning=0[/TD]
[/TR]
[TR]
[TD]C1_H_Standard=0[/TD]
[/TR]
[TR]
[TD]C1_Number_Blade=1[/TD]
[/TR]
[TR]
[TD]C1_R_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_R_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_Radial_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Axial_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4=0[/TD]
[/TR]
[TR]
[TD]C1_ImagingDataBefore=0[/TD]
[/TR]
[TR]
[TD]C1_ImagingDataAfter=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4_2=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4_2=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4_3=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4_3=0[/TD]
[/TR]
[TR]
[TD]C1_CutType=0[/TD]
[/TR]
[TR]
[TD]C1_Turning_SL_Upper=0[/TD]
[/TR]
[TR]
[TD]C1_Turning_SL_Lower=0[/TD]
[/TR]
[TR]
[TD][Magazine0_Pot002][/TD]
[/TR]
[TR]
[TD]Pot_Kind=0[/TD]
[/TR]
[TR]
[TD]PTN=0[/TD]
[/TR]
[TR]
[TD]FTN=0[/TD]
[/TR]
[TR]
[TD]ITN=0[/TD]
[/TR]
[TR]
[TD]Order=0[/TD]
[/TR]
[TR]
[TD]Note=[/TD]
[/TR]
[TR]
[TD]Through_Spindle_Enable=0[/TD]
[/TR]
[TR]
[TD]Through_Spindle_Time=0[/TD]
[/TR]
[TR]
[TD]ATC_Speed=0[/TD]
[/TR]
[TR]
[TD]M60_Disable=0[/TD]
[/TR]
[TR]
[TD]Prohibit=0[/TD]
[/TR]
[TR]
[TD]TL_Alarm_Effective=0[/TD]
[/TR]
[TR]
[TD]Total_Cutter=1[/TD]
[/TR]
[TR]
[TD]Pot_Size=0[/TD]
[/TR]
[TR]
[TD]Prohibit_Rotation=0[/TD]
[/TR]
[TR]
[TD]Empty_Pot=0[/TD]
[/TR]
[TR]
[TD]Irregular_Shape=0[/TD]
[/TR]
[TR]
[TD]Commanded_T_Code=0[/TD]
[/TR]
[TR]
[TD]TLS_Search=0[/TD]
[/TR]
[TR]
[TD]B_Axis_Rot_Prohibit=0[/TD]
[/TR]
[TR]
[TD]One_Touch_Prohibit=0[/TD]
[/TR]
[TR]
[TD]RakuRakuL_Measuring=0[/TD]
[/TR]
[TR]
[TD]Tool_Size=0[/TD]
[/TR]
[TR]
[TD]TSC_Removal_Type=0[/TD]
[/TR]
[TR]
[TD]ExistOnMgz=0[/TD]
[/TR]
[TR]
[TD]CheckH=0[/TD]
[/TR]
[TR]
[TD]CheckD=0[/TD]
[/TR]
[TR]
[TD]InspectedFlag=0[/TD]
[/TR]
[TR]
[TD]ONumber1=0[/TD]
[/TR]
[TR]
[TD]ONumber2=0[/TD]
[/TR]
[TR]
[TD]ONumber3=0[/TD]
[/TR]
[TR]
[TD]TSC_Frequency=0[/TD]
[/TR]
[TR]
[TD]TSC_FLOW_CHECK=1[/TD]
[/TR]
[TR]
[TD]TSC_Frequency_Setting=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_X=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_Y=0[/TD]
[/TR]
[TR]
[TD]AngleHeadDistance_Z=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_X=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_Y=0[/TD]
[/TR]
[TR]
[TD]AngleHeadVector_Z=0[/TD]
[/TR]
[TR]
[TD]PWRFLR_Retract_Disable=0[/TD]
[/TR]
[TR]
[TD]SisterToolStatus=0[/TD]
[/TR]
[TR]
[TD]TLMgzVibCtrlMode=0[/TD]
[/TR]
[TR]
[TD]UseTlsNo=0[/TD]
[/TR]
[TR]
[TD]MultiPurpose=0[/TD]
[/TR]
[TR]
[TD]AirSpinDataNO=0[/TD]
[/TR]
[TR]
[TD]ImagingAtTCode=0[/TD]
[/TR]
[TR]
[TD]FixedPot=0[/TD]
[/TR]
[TR]
[TD]GroupNo=0[/TD]
[/TR]
[TR]
[TD]ImagingAllTogether=0[/TD]
[/TR]
[TR]
[TD]ImagExist=0[/TD]
[/TR]
[TR]
[TD]SchankCount=0[/TD]
[/TR]
[TR]
[TD]HighAccuracySeatChk=0[/TD]
[/TR]
[TR]
[TD]SpinVibRapidW=0[/TD]
[/TR]
[TR]
[TD]SpinVibRapidA=0[/TD]
[/TR]
[TR]
[TD]SpinVibCutW=0[/TD]
[/TR]
[TR]
[TD]SpinVibCutA=0[/TD]
[/TR]
[TR]
[TD]TSC_Pressure=0[/TD]
[/TR]
[TR]
[TD]AlarmStopType=0[/TD]
[/TR]
[TR]
[TD]Helix=0[/TD]
[/TR]
[TR]
[TD]C1_Kind=0[/TD]
[/TR]
[TR]
[TD]C1_H_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_H_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_D_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_D_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Time Actual=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Distance_Actual=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Warn=0[/TD]
[/TR]
[TR]
[TD]C1_TL_Count_Actual=0[/TD]
[/TR]
[TR]
[TD]C1_SL_Upper=0[/TD]
[/TR]
[TR]
[TD]C1_SL_Lower=0[/TD]
[/TR]
[TR]
[TD]C1_AC=0[/TD]
[/TR]
[TR]
[TD]C1_Alarm=0[/TD]
[/TR]
[TR]
[TD]C1_Warning=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Enable=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Action_Type=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Before=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_After=0[/TD]
[/TR]
[TR]
[TD]C1_BTS_Length=0[/TD]
[/TR]
[TR]
[TD]C1_Operator_Call=0[/TD]
[/TR]
[TR]
[TD]C1_First_Use=0[/TD]
[/TR]
[TR]
[TD]C1_Spindle_Speed_M6=0[/TD]
[/TR]
[TR]
[TD]C1_Coolant_Kind_M6=0[/TD]
[/TR]
[TR]
[TD]C1_Spindle_Speed_Limit=0[/TD]
[/TR]
[TR]
[TD]C1_Surface_Speed_Limit=0[/TD]
[/TR]
[TR]
[TD]C1_SuspendAlarm=0[/TD]
[/TR]
[TR]
[TD]C1_SuspendWarning=0[/TD]
[/TR]
[TR]
[TD]C1_H_Standard=0[/TD]
[/TR]
[TR]
[TD]C1_Number_Blade=1[/TD]
[/TR]
[TR]
[TD]C1_R_Geometry=0[/TD]
[/TR]
[TR]
[TD]C1_R_Wear=0[/TD]
[/TR]
[TR]
[TD]C1_Radial_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Axial_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4=0[/TD]
[/TR]
[TR]
[TD]C1_ImagingDataBefore=0[/TD]
[/TR]
[TR]
[TD]C1_ImagingDataAfter=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Load=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration1=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration2=0[/TD]
[/TR]
[TR]
[TD]C1_Max_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Ave_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Min_Vibration3=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4_2=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4_2=0[/TD]
[/TR]
[TR]
[TD]C1_Inst_Spd_AST4_3=0[/TD]
[/TR]
[TR]
[TD]C1_Shift_Spd_AST4_3=0[/TD]
[/TR]
[TR]
[TD]C1_CutType=0[/TD]
[/TR]
[TR]
[TD]C1_Turning_SL_Upper=0[/TD]
[/TR]
[TR]
[TD]C1_Turning_SL_Lower=0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Re: CNC Data Import HELP!!!

Welcome to the MrExcel board!

I'm not at all clear exactly what you are trying to achieve.

1. Are we trying to find the second cell that starts with "["? If not how do we tell what a "similar" cell looks like?

2. Do you want the data from A1 down to that similar cell, or do you want the data below that second similar cell?

3. Where do you want the results?

Without answers to those questions, you could try this on a copy of your data. It puts in column B the data from cell A1 down to the next cell that starts with a "["

Sub FindBlock()
  Dim a As Variant
  Dim i As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  i = 1
  Do
    i = i + 1
  Loop Until Left(a(i, 1), 1) = "["
  Range("B1").Resize(i).Value = a
End Sub

BTW, if you post more sample data to clarify what you want, please make it small - say 10-15 rows only.

 

[ajuds06]

Re: CNC Data Import HELP!!!

Peter,

Thank you for your prompt response and I am new to posting on the forum so I will definitely move forward with smaller samples, I just put it in there exactly the way I see it so it would be more clear. As for your questions:
1. Yes finding the second, third, ....n cells that contain "[" is what I am looking for, considering it's labor intensive for 21000 rows of data.
2. I want all subsequent data underneath the cell with the character "[" to be moved with the cell into another column.
3. I want the results into the same sheet, just multiple columns, so it would look something like;
A B C ...N
[Magazine0_Pot001] [Magazine0_Pot002] [Magazine1_Pot001] [MagazineX_PotXXX]

 

[Peter_SSs]

Re: CNC Data Import HELP!!!

OK, try this. I have assumed that cell A1 is always a "[......" cell

Sub Move_Blocks()
  Dim a As Variant, b As Variant
  Dim i As Long, rw As Long, col As Long
  
  Application.ScreenUpdating = False
  a = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  a(UBound(a), 1) = "["
  col = 2
  i = 1
  b(1, 1) = a(1, 1)
  For rw = 2 To UBound(a)
    If Left(a(rw, 1), 1) = "[" Then
      Cells(1, col).Resize(i).Value = b
      col = col + 1
      i = 1
      b(1, 1) = a(rw, 1)
    Else
      i = i + 1
      b(i, 1) = a(rw, 1)
    End If
  Next rw
  ActiveSheet.UsedRange.Columns.AutoFit
  Application.ScreenUpdating = True
End Sub