Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Need to create a .BAT to import Tab Delimited file into excel template and have it save as in said template.. RRS feed

 > 

  • General discussion

  • Discussion
    Sign in to vote
    0
    Sign in to vote

    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.  

    • Changed type Bill_Stewart Tuesday, December 5, 2017 3:14 PM
    • Moved by Bill_Stewart Tuesday, December 5, 2017 3:15 PM This is not "scripts on demand"
    Thursday, October 26, 2017 2:53 PM

All replies