Auto import data from text files into Excel spreadsheet and merge

[scottryan]

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

	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.