Hey Guys,
Been a while since I've been asked to write a batch, but needless to say, it is being requested of me to make everyone's life a little easier. Let me explain a little of what is needed. I work in a motor manufacturer. We have testing equipment
at my job that outputs files in tab delimited txt files. We essentially need it to ouptut into an excel template, and so far this has been done manually by engineers.
I have never had to setup a batch script as intricate as this so I need decent amount of help (if it can be done with SQL commands, may need to use vbs, not sure).
Basically the TD file the testing software puts out is cumulative. It keeps adding onto the same file with the results. So if the script could take the last Entry (not sure if it can recognize the serial number breaks in the TD file), copy paste
the results into the excel file (or import them, whatever is easiest) and then save the file as and either prompt user for file name or just save it with date/serial number of unit in title.
Here is an example of the TD file layout:
09/29/17 12:49:31
NBP 00022 10.013
5955.000 7.198
0.309 24.017
60.658 CW
20.057
0.000 0.091
0.000 0.000
0.000 CW
31.050
5954.000 7.094
0.302 24.016
61.432 CCW
41.083
0.000 0.547
0.000 0.000
0.000 CCW
47.081
0.000 78.460
2.104 4.515
0.000 CW
52.099
0.000 82.710
2.156 4.516
0.000 CCW
57.234
0.000 103.000
2.858 6.217
0.000 CW
62.247
0.000 111.000
2.887 6.216
0.000 CCW
Each test is going to be about 8 lines of text, tab delimited.
Here is a copy and past of the template we are using:
DATE |
TIME |
SERIAL |
ELAPSED |
RPM |
TORQUE |
AMP |
VOLT |
EFF % |
DOR |
Kt 2A |
CW |
#DIV/0! |
Kt 2A |
CCW |
#DIV/0! |
Kt 3A |
CW |
#DIV/0! |
Kt 3A |
CCW |
#DIV/0! |
ATP |
Speed |
#DIV/0! |
Unit type |
NBP |
Direction: |
FAIL |
Serial Number |
00000 |
Motor Kt |
#DIV/0! |
Comments |
|
|
|
|
|
The import or copy paste should start on Column A row 3.
Here is an example of one filled out by manual copy paste:
DATE |
TIME |
SERIAL |
ELAPSED |
RPM |
TORQUE |
AMP |
VOLT |
EFF % |
DOR |
9/29/2017 |
12:49:31 |
NBP 00022 |
10.013 |
5955 |
7.198 |
0.309 |
24.017 |
60.658 |
CW |
20.057 |
0 |
0.091 |
0 |
0 |
0 |
CW |
31.05 |
5954 |
7.094 |
0.302 |
24.016 |
61.432 |
CCW |
41.083 |
0 |
0.547 |
0 |
0 |
0 |
CCW |
47.081 |
0 |
78.46 |
2.104 |
4.515 |
0 |
CW |
52.099 |
0 |
82.71 |
2.156 |
4.516 |
0 |
CCW |
57.234 |
0 |
103 |
2.858 |
6.217 |
0 |
CW |
62.247 |
0 |
111 |
2.887 |
6.216 |
0 |
CCW |
Kt 2A |
CW |
37.29087 |
Kt 2A |
CCW |
38.36271 |
Kt 3A |
CW |
36.03919 |
Kt 3A |
CCW |
38.44822 |
ATP |
Speed |
5955 |
Unit type |
NBP |
Direction: |
PASS |
Serial Number |
00022 |
Motor Kt |
37.5 |
Comments |
|
|
|
|
|
Sorry for the long post, and any help or direction would be appreciated.