I'm trying to automate my data import method to save time. Right now 2 text files are created and saved in a folder before being opened in Excel, the required data highlighted and then copied and pasted into the relevant cells in the ROPdata sheet shown below.
The first file is called ROP-Gas.txt and contains 3 columns delimited by spaces. The first column is Depth, second is ROP and the third is Gas. After going through Text Import Wizard, it looks like this:
ROP-Gas.txt
<tbody>
</tbody>
So, the file is opened and the ROP and Gas columns highlighted and copied to the clipboard before going to the ROPdata sheet and the matching Depth value and pasted in.
The second file is called gamma.las that contains a header and columns of data. The header isn't relevant but the columns are - the first is Depth and the second is Gamma. It's also space delimited and looks like this after going through the Import Text Wizard:
gamma.las
<tbody>
</tbody>
Same thing with the gamma.las file - the gamma values are highlighted, copied to the clipboard and then pasted into the ROPdata sheet based on the matching depth values.
Excel tables to the web >> Excel Jeanie HTML 4
ROPdata
<tbody>
</tbody>
I'd like to automate this process as much as possible. This data comes from drilling oil wells and is depth based so the depth keeps increasing and new ROP, Gas and Gamma data is generated every 0.2 meters. The ROP-Gas.txt and gamma.las files are periodically overwritten in their folder keeping the file name the same but containing new depth based data. When that happens I'd like to automatically import these new values into the ROPdata sheet and have the ROP, Gas and Gamma data put into the cells with the matching Depth values.
I hope that I've explained what I'd like to do clearly - any help would be greatly appreciated.
The first file is called ROP-Gas.txt and contains 3 columns delimited by spaces. The first column is Depth, second is ROP and the third is Gas. After going through Text Import Wizard, it looks like this:
ROP-Gas.txt
A | B | C | |
1 | 500 | 0.6 | 2.18 |
2 | 500.2 | 0.6 | 2.18 |
3 | 500.4 | 0.6 | 2.18 |
4 | 500.6 | 0.6 | 2.18 |
5 | 500.8 | 0.6 | 2.19 |
6 | 501 | 0.6 | 2.19 |
7 | 501.2 | 0.6 | 2.2 |
<tbody>
</tbody>
So, the file is opened and the ROP and Gas columns highlighted and copied to the clipboard before going to the ROPdata sheet and the matching Depth value and pasted in.
The second file is called gamma.las that contains a header and columns of data. The header isn't relevant but the columns are - the first is Depth and the second is Gamma. It's also space delimited and looks like this after going through the Import Text Wizard:
gamma.las
A | B | C | D | E | F | |
1 | ~VERSION | INFORMATION | ||||
2 | VERS. | 2 | : | CWLS | LOG | ASCII |
3 | WRAP. | NO | : | ONE | LINE | PER |
4 | PHXV. | 1.4.1.14 | : | PhoenixLAS | Generator | Version |
5 | VDAT. | ######## | 8:58:00 | : | PhoenixLAS | Build |
6 | ~WELL | INFORMATION | ||||
7 | #MNEM.UNIT | DATA | DESCRIPTION | |||
8 | #----- | ----- | ---------- | ----------------------- | ||
9 | STRT | .M | 139 | : | START | DEPTH |
10 | STOP | .M | 2175 | : | STOP | DEPTH |
11 | STEP | .M | 0.2 | : | STEP | |
12 | NULL | . | -999.25 | : | NULL | VALUE |
13 | WELL | . | : | Well | Name | |
14 | RIG | . | : | Rig | Name | |
15 | COMP | . | : | Client | Name | |
16 | CTRY | . | CA | : | Country | |
17 | PROV | . | MB | : | Province | |
18 | LOC | . | : | Surface | Location | |
19 | UWI | . | : | Unique | Well | ID |
20 | PDAT | . | Ground | Level | : | Permanent |
21 | DMF | . | Kelly | Bushing | : | Drilling |
22 | KB | .M | 535.53 | : | Kelly | Bushing |
23 | GL | .M | 531.33 | : | Ground | Level |
24 | DATE | . | 2018 | 6 | 30 | : |
25 | CREATED | . | 2018 | 7 | 3 | : |
26 | LIC | . | : | Well | License | Number |
27 | LATI | .DEG | : | Latitude | ||
28 | LONG | .DEG | : | Longitude | ||
29 | SRVC | . | : | Service | Company | |
30 | FLD | . | : | Field | ||
31 | ~CURVE | INFORMATION | ||||
32 | #MNEM.UNIT | API | CODES | CURVE | DESCRIPTION | |
33 | #----- | ----- | ---------- | ----------------------- | ||
34 | DEPT | .M | 1 | : | DEPTH | |
35 | MG1C | .API | : | Gamma | Ray | (Corrected) |
36 | DVER | .M | 960 | : | Hole | TVD |
37 | ROPA | .M/HR | : | Rate | of | Penetration |
38 | ~A | DEPT | MG1C | DVER | ROPA | |
39 | 500 | 34.94 | 139 | -999.25 | ||
40 | 500.2 | 36.39 | 139.2 | 24.87 | ||
41 | 500.4 | 37.85 | 139.4 | 50.25 | ||
42 | 500.6 | 39.3 | 139.6 | 52.44 | ||
43 | 500.6 | 39.85 | 139.8 | 58.81 | ||
44 | 500.8 | 40.39 | 140 | 69.81 | ||
45 | 501 | 40.94 | 140.2 | 76.01 | ||
46 | 501.2 | 41.49 | 140.4 | 74.11 |
<tbody>
</tbody>
Same thing with the gamma.las file - the gamma values are highlighted, copied to the clipboard and then pasted into the ROPdata sheet based on the matching depth values.
Excel tables to the web >> Excel Jeanie HTML 4
ROPdata
A | B | C | D | E | F | G | H | I | |
1 | Slide | Depth | ROP (min/m) | Gas (%) | Gamma (cps) | TVD | Subsea Elevation | Plotted Gas | Plotted Gamma |
2 | 500.0 | 496.62 | -496.62 | 0.0 | 0.0 | ||||
3 | 500.2 | 496.82 | -496.82 | 0.0 | 0.0 | ||||
4 | 500.4 | 497.02 | -497.02 | 0.0 | 0.0 | ||||
5 | 500.6 | 497.22 | -497.22 | 0.0 | 0.0 | ||||
6 | 500.8 | 497.42 | -497.42 | 0.0 | 0.0 | ||||
7 | 501.0 | 497.61 | -497.61 | 0.0 | 0.0 | ||||
8 | 501.2 | 497.81 | -497.81 | 0.0 | 0.0 | ||||
9 | 501.4 | 498.01 | -498.01 | 0.0 | 0.0 | ||||
10 | 501.6 | 498.21 | -498.21 | 0.0 | 0.0 | ||||
11 | 501.8 | 498.41 | -498.41 | 0.0 | 0.0 | ||||
12 | 502.0 | 498.61 | -498.61 | 0.0 | 0.0 | ||||
13 | 502.2 | 498.80 | -498.80 | 0.0 | 0.0 | ||||
14 | 502.4 | 499.00 | -499.00 | 0.0 | 0.0 | ||||
15 | 502.6 | 499.20 | -499.20 | 0.0 | 0.0 | ||||
16 | 502.8 | 499.40 | -499.40 | 0.0 | 0.0 | ||||
17 | 503.0 | 499.60 | -499.60 | 0.0 | 0.0 |
<tbody>
</tbody>
I'd like to automate this process as much as possible. This data comes from drilling oil wells and is depth based so the depth keeps increasing and new ROP, Gas and Gamma data is generated every 0.2 meters. The ROP-Gas.txt and gamma.las files are periodically overwritten in their folder keeping the file name the same but containing new depth based data. When that happens I'd like to automatically import these new values into the ROPdata sheet and have the ROP, Gas and Gamma data put into the cells with the matching Depth values.
I hope that I've explained what I'd like to do clearly - any help would be greatly appreciated.