Automatically import multiple text files in one worksheet with a separate sheet for every txt file?

[valerio misiti]

Dears,

I do not know anything abot VBA stuff and I have to import multiple text files in one worksheet with a separate sheet for every txt file.
Every txt file is more or less like below (I am interested in the lines after the 9th).
Can you help me? It is one day I am struggling with it but the excel options do not allow me to do that

Ablation Date: 062520
Catheter S/N: 00009
Catheter Lot Number: 000000000215817
Control Mode: TEMPERATURE
Treatment State: RF_ON
Z Magnitude: 125
Z Slope: 0.885
Z Phase: -7.1
CI: 0.545
Gen_Time Set_Time Set_Temp Set_P Abl_Time Imped DC_V DC_I Meas_P Meas_V RFI I_D1 IRet1 Phase TRef TC1 TC2 TC3 TC4 TC5 TC6 TComp SetFlow Fluid Pump1
hh:mm:ss s C W s Ohm V mA W V mA mA mA Deg C C C C C C C C ml/min ml rpm
08:58:24 20 60 50.0 0.00 96 1 313 0.3 5 53 19 53 0.0 29.2 33.8 33.9 33.8 34.7 34.6 34.7 34.7 8 73 31
08:58:25 20 60 50.0 1.00 91 12 4232 49.8 70 723 268 735 7.9 26.5 43.9 46.2 42.6 45.0 45.0 45.4 46.2 8 73 36

 

[sandy666]

you can try Power Query with From Folder feature, but I'd like to see more than one example, eg. 3-4

Gen_Time	Set_Time	Set_Temp	Set_P	Abl_Time	Imped	DC_V	DC_I	Meas_P	Meas_V	RFI	I_D1	IRet1	Phase	TRef	TC1	TC2	TC3	TC4	TC5	TC6	TComp	SetFlow	Fluid	Pump1
hh:mm:ss	s	C	W	s	Ohm	V	mA	W	V	mA	mA	mA	Deg	C	C	C	C	C	C	C	C	ml/min	ml	rpm
08:58:24	20	60	50.0	0.00	96	1	313	0.3	5	53	19	53	0.0	29.2	33.8	33.9	33.8	34.7	34.6	34.7	34.7	8	73	31
08:58:25	20	60	50.0	1.00	91	12	4232	49.8	70	723	268	735	7.9	26.5	43.9	46.2	42.6	45.0	45.0	45.4	46.2	8	73	36

update your profile about Excel version

 

[valerio misiti]

Thanks a lot. I updated the Excel version (365).
I attach here anothe couple of examples as I do not see any option to attach. Thanks a lot (where I remove the initial 9 lines: they are always more or less the same):
Gen_Time Set_Time Set_Temp Set_P Abl_Time Imped DC_V DC_I Meas_P Meas_V RFI I_D1 IRet1 Phase TRef TC1 TC2 TC3 TC4 TC5 TC6 TComp SetFlow Fluid Pump1
hh:mm:ss s C W s Ohm V mA W V mA mA mA Deg C C C C C C C C ml/min ml rpm
08:57:46 20 60 50.0 0.00 99 1 306 0.3 5 52 19 50 0.0 29.0 33.5 33.7 33.6 34.7 34.6 34.7 34.7 8 68 35
08:57:47 20 60 50.0 1.00 96 13 4080 50.2 71 715 256 719 9.0 26.8 42.6 43.3 42.7 47.0 45.6 44.9 47.0 8 68 35
08:57:48 20 60 50.0 2.00 95 13 4177 50.1 70 731 281 729 12.6 31.4 45.9 47.9 45.9 46.3 45.8 46.2 47.9 8 68 35
08:57:49 20 60 50.0 3.00 93 13 4130 49.6 69 703 284 726 0.0 30.0 44.1 50.0 44.5 48.3 48.6 47.5 50.0 8 69 35

Gen_Time Set_Time Set_Temp Set_P Abl_Time Imped DC_V DC_I Meas_P Meas_V RFI I_D1 IRet1 Phase TRef TC1 TC2 TC3 TC4 TC5 TC6 TComp SetFlow Fluid Pump1
hh:mm:ss s C W s Ohm V mA W V mA mA mA Deg C C C C C C C C ml/min ml rpm
08:58:24 20 60 50.0 0.00 96 1 313 0.3 5 53 19 53 0.0 29.2 33.8 33.9 33.8 34.7 34.6 34.7 34.7 8 73 31
08:58:25 20 60 50.0 1.00 91 12 4232 49.8 70 723 268 735 7.9 26.5 43.9 46.2 42.6 45.0 45.0 45.4 46.2 8 73 36
08:58:26 20 60 50.0 2.00 92 12 4216 49.9 69 728 299 727 7.4 26.9 43.2 46.4 42.0 47.2 47.3 46.7 47.4 8 73 35
08:58:27 20 60 50.0 3.00 88 12 4283 49.9 68 746 339 758 11.9 28.6 46.0 52.7 44.7 46.5 45.4 48.2 52.7 8 73 36

I tried to follow your suggestion (before I had tried to use import from folder directly from excel). I have some problems:

• the initial 9 lines makes difficult to get the outcome (I guess they make difficult to the software to understand the pattern)
• even working without those line I am not anle to get the same your outcome
• I have multiple txt files like that and I would like to create an Excel sheet with multiple sheets each reporting the data from one single txt file (basically every single shhe should have what you did)

 

[sandy666]

1. don't change original structure of txt files
2. zip a few (or all) txt files
3. share this .zip file via onedrive, googledrive or any similar then post link to the shared .zip file here
4. why each result must be on the separate sheet?

 

[John_w]

You could also try the legacy Text Import Wizard to import one of the files - it's simpler than Power Query and allows you to specify the start row. Record a macro and the generated VBA can be edited to loop through the folder and import each file into a separate sheet.

I'm not sure if the legacy Text Import Wizard is available in Excel 365. If so, first you must enable it via File tab -> Options -> Data and then run it via Data tab -> Legacy Wizards -> From Text.

 

[valerio misiti]

1. don't change original structure of txt files
2. zip a few (or all) txt files
3. share this .zip file via onedrive, googledrive or any similar then post link to the shared .zip file here
4. why each result must be on the separate sheet?

link to test.zip

I hope I did it properly.
I want to have them in different sheet for doing additional calculations later on.
It should work if they are in the same sheet as well but then I would need maybe something to put them in order/separate the content of one ffile from the other.
Thanks a lot for the help

 

[valerio misiti]

You could also try the legacy Text Import Wizard to import one of the files - it's simpler than Power Query and allows you to specify the start row. Record a macro and the generated VBA can be edited to loop through the folder and import each file into a separate sheet.

I'm not sure if the legacy Text Import Wizard is available in Excel 365. If so, first you must enable it via File tab -> Options -> Data and then run it via Data tab -> Legacy Wizards -> From Text.

thanks a lot for your help.
I did it for one file wityh the Wizard: my problem is now with "Record a macro and the generated VBA can be edited to loop through the folder and import each file into a separate sheet".
I guess you overestimated my Excel skills . Is it something that you can tell me how to do?ù
Thanks a lot again

 

[John_w]

my problem is now with "Record a macro

There are many tutorials and videos, for example:

Record a Macro in Excel - Instructions and Video Lesson

Record a Macro in Excel - Instructions Video: An video, overview, and instructions that show you how to record macros in Excel.

www.teachucomp.com

 

[sandy666]

It is not economical to create multiple queries separately for each sheet. Maybe it would be better to create a pick list for each log and show only the selected one.

Select
0		hh:mm:ss	s	C	W	s_1	Ohm	V	mA	Column1	W_2	_3	V_4	mA_5	mA_6	mA_7	Deg	C_8	C_9	C_10	C_11	C_12	C_13	C_14	C_15	ml/min	ml	rpm
		08:56:57	20	60	50	0	106	1	283	0.3		5	48	17	48	0	29.4	34	34.1	34.2	35	35	35.1	35.1	8	63	35
		08:56:58	20	60	50	1	101	13	4043	50.1	72	726	262	713	16	31.1	45.3	46.6	44.7	47.2	46	46.7	47.2	8	63	35
		08:56:59	20	60	50	2	97	13	4082	49.9	71	704	237	710	0	31.1	48.9	50.3	43.9	45.4	47.5	47.9	50.3	8	63	35
		08:57:00	20	60	50	3	98	13	4027	50	73	691	258	713	8.9	27.5	45.6	48.2	42.3	43.8	45	46.8	48.2	8	63	35
		08:57:01	20	60	50	4	99	13	4050	49.9	73	704	250	706	13.1	30.4	47.3	49.8	42.5	44.3	45.3	48.5	49.8	8	64	35
		08:57:02	20	60	50	5	97	13	4088	50	72	703	234	718	10.1	29.5	49.1	52.7	44.7	47.7	49.1	49.7	52.7	8	64	35
		08:57:03	20	60	50	6	93	13	4184	50.3	71	752	236	735	18.6	30.5	49.3	49.2	43.3	47.7	50.1	49.4	50.1	8	64	35
		08:57:04	20	60	50	7	89	12	4288	50	68	755	307	750	13.6	24.9	52.9	48.5	45.5	46.3	47.7	48.8	52.9	8	64	35
		08:57:05	20	60	50	8	89	12	4280	50.1	69	745	348	743	11.2	27.6	57.4	50.8	48.4	47.4	45.2	46.9	57.4	8	64	35
		08:57:06	20	60	50	9	88	13	4182	50	70	722	254	732	6.3	29.8	49	47.3	45.7	48	47.5	48.6	49.2	8	64	35
		08:57:07	20	60	50	10	91	12	4189	49.5	69	725	288	729	10.5	31.6	42.7	42.4	39.5	43.5	44.6	45.2	45.2	8	64	35
		08:57:08	20	60	50	11	94	13	4099	50	71	704	265	718	5.1	25.9	46.4	47.2	40.8	43.6	44.8	46.9	47.2	8	65	35

Select
15		hh:mm:ss	s	C	W	s_1	Ohm	V	mA	Column1	W_2	_3	V_4	mA_5	mA_6	mA_7	Deg	C_8	C_9	C_10	C_11	C_12	C_13	C_14	C_15	ml/min	ml	rpm
		09:03:11	20	60	50	10	84	12	4306	49.8	68	733	238	749	3.8	28.1	43.4	45	50.8	59.7	52.2	50.7	59.7	8	101	35
		09:03:32	20	60	50	0	101	1	291	0.3		5	49	17	50	0	29.3	34.1	34.3	34.2	35.1	35.1	35.2	35.2	8	103	35
		09:03:32	20	60	50	1	97	13	4109	50.1	71	716	273	715	11.6	28.9	48.6	46.2	45	43.2	44.9	45.5	48.6	8	103	35
		09:03:33	20	60	50	2	93	13	4192	50	70	718	321	732	0	27.2	53	49.6	48.1	43.7	45.6	48.2	53	8	103	35
		09:03:34	20	60	50	3	90	12	4222	50	70	732	332	737	12.4	31.5	55.5	54.8	48.8	45	45.1	48	55.5	8	103	35
		09:03:35	20	60	50	4	90	13	4220	50.1	71	731	331	742	14	30.6	53.1	57.8	47.5	43.2	43.6	44.7	57.8	8	103	35
		09:03:36	20	60	50	5	90	12	4241	49.9	69	738	275	740	10.4	31	46.1	47.9	44.7	47.5	50	50.2	50.5	8	103	35
		09:03:37	20	60	50	6	90	12	4219	50	69	738	301	737	12.4	28.3	46.6	52.7	43.4	42.8	43.6	44.3	52.7	8	103	35
		09:03:38	20	60	50	7	89	12	4235	49.9	68	728	277	740	0	30	50.3	53	46.7	46.8	49.6	50.4	53	8	103	35
		09:03:39	20	60	50	8	89	12	4280	50.1	69	744	258	750	12.7	29.8	47.8	51.7	45.9	47.8	51.3	49.8	51.7	8	103	35
		09:03:40	20	60	50	9	88	13	4210	50.1	70	720	291	742	0	32.9	46	48.8	43.7	45.1	47.1	47.2	48.8	8	104	35
		09:03:41	20	60	50	10	89	12	4253	50	68	755	295	734	14	28.3	46.9	55.7	45	45.2	46.6	46.9	55.7	8	104	35

 

[valerio misiti]

There are many tutorials and videos, for example:

Record a Macro in Excel - Instructions and Video Lesson

Record a Macro in Excel - Instructions Video: An video, overview, and instructions that show you how to record macros in Excel.

www.teachucomp.com

thanks