CNC Data Import

A

ajuds06

New Member
Joined
Jul 25, 2017
Messages
2
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.

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

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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 "["

Code: 
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0
Re: CNC Data Import HELP!!!

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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Import new data from a text file based on certain field criteria.
2 3
Replies
23
Views
2K
maudedog
M
B
  • Question
VBA to extract data from multiple folders and add to a master workbook plus identifying tags
Replies
2
Views
870
John_w
J
S
  • Question
VBA query pasting data in with new columns rather than just pasting values into existing columns?
Replies
0
Views
404
SendHalp3
S
G
  • Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
3K
Gassy
G
gravanoc
  • Question
Sync data and row insertions/deletions on multiple tabs
Replies
1
Views
928
gravanoc
gravanoc

Forum statistics

Threads
1,215,219
Messages
6,123,692
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top