Converting a ASCII text file with strange delimiters.

[Gazland]

Hi,
Firstly.. Thanks to anyone that reads this and gives me help. I'm a total beginner when it comes to this and I'm not even too sure on how to ask this question properly.

OK....
I'm trying to create spreadsheets using data downloaded from an electrical testing device, Specifically a PAT Tester. The model I use for work does connect with software that you have to pay an extortionate amount of money for and I'm sure that software will present the data in a lovely way. However there is an option to download the data from it as an ACSII file. Which looks like this:

TEST NUMBER 0001
DATE 02-FEB-2024
TIME 12:07:35
TESTER 14N-1251
APP NO 231131
TEST MODE TEST
INS >19.99 Mohm P
USER admin
SITE Ravns Triangle
TEXT Heater
TEXT
TEXT
TEXT

TEST NUMBER 0002
DATE 02-FEB-2024
TIME 12:14:20
TESTER 14N-1251
APP NO 231132
TEST MODE TEST
INS >19.99 Mohm P
USER admin
SITE Ravns Triangle
TEXT Dehumidifier
TEXT
TEXT
TEXT

TEST NUMBER 0003
DATE 02-FEB-2024
TIME 12:16:16
TESTER 14N-1251
APP NO 231133
TEST MODE TEST
INS >19.99 Mohm P
USER admin
SITE Ravns Triangle
TEXT Radio Charger
TEXT
TEXT
TEXT

Ive tried to use carriage return as a delimiter to sort the information but that doesn't work I believe because its imported text and not create using alt-Return in excel.
Ive tried using notepad++ to replace all the carriage returns for commas and load that in but that just creates 1 row and a million columns.
The best Ive managed to do is make a file that's created from an unofficial device specific online converter, which looks great but some of the 'columns' and their data are missing. particularly the 'SITE' and 'USER' information. I tried to take a screen shot of what that site produced but the file size was too large to upload here. So Ive created a table below to show roughly what it should look like.

Test number	date	tester	app no	INS	USER	SITE	Description
1	02 feb	14N	67523675	<99.9	me	Ravensthorpe	Radio Charger
2	02 feb	14N	1543763	<99.9	me	Ravenshorpe	Kettle

I know from my experiments that there is more data in the file and the lines that say 'TEXT' are more than just that.

So lovely excel people how do I get these ASCII files off my machine and quickly make an excel spreadsheet from them?
I'm more than happy to upload one of the files for examination but I'm not sure you can on this site??

All Help much appreciated!!!
I'm an electrician and a live sound expert so if any one needs any help with those things let me Know!!
Gareth

 

[Gazland]

Hi, one first approach:
Here the working file to download.
ExtractFromTxt.zip

You will have to paste in column A the content of your file

ExtractFromTxt.xlsx
	A	B	C
1	Text	Exist in list	Test index
2	TEST NUMBER 0001	TRUE	1
3	DATE 02-FEB-2024	TRUE	1
4	TIME 12:07:35	TRUE	1
5	TESTER 14N-1251	TRUE	1
6	APP NO 231131	TRUE	1
7	TEST MODE TEST	TRUE	1
8	INS >19.99 Mohm P	TRUE	1
9	USER admin	TRUE	1
10	SITE Ravns Triangle	TRUE	1
11	TEXT Heater	TRUE	1
12	TEXT	TRUE	1
13	TEXT	TRUE	1
14	TEXT	TRUE	1
15		FALSE	2
16	TEST NUMBER 0002	TRUE	2
17	DATE 02-FEB-2024	TRUE	2
18	TIME 12:14:20	TRUE	2
19	TESTER 14N-1251	TRUE	2
20	APP NO 231132	TRUE	2
21	TEST MODE TEST	TRUE	2
22	INS >19.99 Mohm P	TRUE	2
23	USER admin	TRUE	2
24	SITE Ravns Triangle	TRUE	2
25	TEXT Dehumidifier	TRUE	2
26	TEXT	TRUE	2
27	TEXT	TRUE	2
28	TEXT	TRUE	2
29		FALSE	3
30	TEST NUMBER 0003	TRUE	3
31	DATE 02-FEB-2024	TRUE	3
32	TIME 12:16:16	TRUE	3
33	TESTER 14N-1251	TRUE	3
34	APP NO 231133	TRUE	3
35	TEST MODE TEST	TRUE	3
36	INS >19.99 Mohm P	TRUE	3
37	USER admin	TRUE	3
38	SITE Ravns Triangle	TRUE	3
39	TEXT Radio Charger	TRUE	3
40	TEXT	TRUE	3
41	TEXT	TRUE	3
42	TEXT	TRUE	3
43		FALSE	4
44	TEST NUMBER 0004	TRUE	4
45	DATE 02-FEB-2024	TRUE	4
46	TIME 12:17:00	TRUE	4
47	TESTER 14N-1251	TRUE	4
48	APP NO 231134	TRUE	4
49	TEST MODE TEST	TRUE	4
50	INS >19.99 Mohm P	TRUE	4
51	USER admin	TRUE	4
52	SITE Ravns Triangle	TRUE	4
53	TEXT Radio Charger	TRUE	4
54	TEXT	TRUE	4
55	TEXT	TRUE	4
56	TEXT	TRUE	4
57		FALSE	5
Sheet1

Cell Formulas
Range	Formula
B2:B57	B2	=SUM(IFERROR(SEARCH(Table2[Data types],A2),0))>0
C2:C57	C2	=IF(A2="",N(C1)+1,IF(N(C1)=0,1,C1))

Which is formated as a table.
Then you have the table where you define each data and formula in H2 shows you if new (not in the list) datatypes are present in the content of the text you pasted in column A. You will need to add them manually. For it to work correctly some strings in Table2 (datatypes), will have to have 2 spaces after the text. For example to differentiate "EARTH" from "EARTH CURRENT", the first actually has the text "EARTH " (with two spaces at the end). Thankfully the text in your sample file has a lot of spaces between the field name and the actual value of the field.

ExtractFromTxt.xlsx
	E	F	G	H
1	Order	Data types		New data types
2	1	TEST NUMBER		no new datatypes
3	2	DATE
4	3	TIME
5	4	TESTER
6	5	APP NO
7	6	TEST MODE
8	7	INS
9	8	USER
10	9	SITE
11	11	IEC
12	12	LEAD CONTINUITY
13	13	EARTH CURRENT
14	14	EARTH
15	15	TEXT
Sheet1

Cell Formulas
Range	Formula
H2	H2	=IFERROR(FILTER(A2:A1000,(A2:A1000<>"")*(B2:B1000=FALSE)), "no new datatypes")

And the last formulas that filter the values and format them as table.

ExtractFromTxt.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		TEST NUMBER	DATE	TIME	TESTER	APP NO	TEST MODE	INS	USER	SITE	IEC	LEAD CONTINUITY	EARTH CURRENT	EARTH	TEXT
2	1	0001	02-FEB-2024	12:07:35	14N-1251	231131	TEST	>19.99 Mohm P	admin	Ravns Triangle					Heater
3	2	0002	02-FEB-2024	12:14:20	14N-1251	231132	TEST	>19.99 Mohm P	admin	Ravns Triangle					Dehumidifier
4	3	0003	02-FEB-2024	12:16:16	14N-1251	231133	TEST	>19.99 Mohm P	admin	Ravns Triangle					Radio Charger
5	4	0004	02-FEB-2024	12:17:00	14N-1251	231134	TEST	>19.99 Mohm P	admin	Ravns Triangle					Radio Charger
6	5	0005	02-FEB-2024	12:19:42	14N-1251	231135	TEST	>19.99 Mohm P	admin	Ravns Triangle	0.05 ohm P	P			13A 4way
7	6	0006	02-FEB-2024	12:23:14	14N-1251	231136	TEST	>19.99 Mohm P	admin	Ravns Triangle					Heater
8	7	0007	02-FEB-2024	12:25:53	14N-1251	231137	TEST	>19.99 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Heater
Sheet1

Cell Formulas
Range	Formula
K1:X1	K1	=TRANSPOSE(Table2[Data types])
J2:J21	J2	=LET(r, UNIQUE(Table3[Test index]), r)
K2:X8	K2	=IFERROR(TEXTJOIN(";",,TRIM(SUBSTITUTE(FILTER(Table3[[Text]:[Text]],(Table3[[Test index]:[Test index]]=$J2)*(ISNUMBER(SEARCH(K$1,Table3[[Text]:[Text]])))),K$1,""))),"")
Dynamic array formulas.

Here you will have to see if new columna appear (if new datatypes are added) and the number of rows may extend beyond 20, so you will have to copy down and right if necessary.
Hope this makes sense.
For sure this can be optimized and cleaned up, but it is what i could come up with for now.

Hi. Thanks again for you help.
This is all a bit beyond my understanding of excel. So apologies if my questions are fundamental.
I’ve tried to copy another similar file but with a larger number of tests in.
It loads in the extra tests in to the ‘new data types’ part (column J) and fills the final table up to 20. In column J it puts in the rest of the numbers (so in the case of this new file up to 81) but doesn’t fill in the rest of that table. I know you said I would need to copy down and right. What exactly do I copy and where do I copy it to to fill the rest of the table?

Cheers.

 

[felixstraube]

If you have more than 20 entries, you select the last row for example K21:X21 and copy it down till you have covered to the number of entries you have in your case 81. you can copy down further if you like, for example to row 200, if there is no data you just will get an empty string. So there will be no problem.
If you get new datatypes you will have to copy the last column lets say X2:X21 (or X2:X82) to the right.
The new datatypes that appear you should copy to the table in columns E and F. With the order column you can manually give it the sort the table as you like. Change the numbers and sort the from smallest to largest for example.
If something is unclear, let me know, or if you like you can share you txt file and I'll have a look.

 

[Gazland]

Hi Felix.
Thank you very much! That all makes sense now.
The file Works really well. You’re a legend.

 

[felixstraube]

You are welcome. Glad it is of use to you. Thanks for the feedback.

 

[felixstraube]

Hi again.
Here is a cleaner formula (dynamic array formula). That does the same. It adjusts the number of rows and columns depending on the input.
Here is the working sheet: ExtractFromTxt2.zip

here the XL2BB:

ExtractFromTxt2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Text		Order	Data types		New data types
2	TEST NUMBER 0001		1	TEST NUMBER		no new datatypes			TEST NUMBER	DATE	TIME	TESTER	APP NO	TEST MODE	INS	USER	SITE	IEC	LEAD CONTINUITY	EARTH CURRENT	EARTH	TEXT
3	DATE 02-FEB-2024		2	DATE				1	0001	02-FEB-2024	12:07:35	14N-1251	231131	TEST	>19.99 Mohm P	admin	Ravns Triangle					Heater
4	TIME 12:07:35		3	TIME				2	0002	02-FEB-2024	12:14:20	14N-1251	231132	TEST	>19.99 Mohm P	admin	Ravns Triangle					Dehumidifier
5	TESTER 14N-1251		4	TESTER				3	0003	02-FEB-2024	12:16:16	14N-1251	231133	TEST	>19.99 Mohm P	admin	Ravns Triangle					Radio Charger
6	APP NO 231131		5	APP NO				4	0004	02-FEB-2024	12:17:00	14N-1251	231134	TEST	>19.99 Mohm P	admin	Ravns Triangle					Radio Charger
7	TEST MODE TEST		6	TEST MODE				5	0005	02-FEB-2024	12:19:42	14N-1251	231135	TEST	>19.99 Mohm P	admin	Ravns Triangle	0.05 ohm P	P			13A 4way
8	INS >19.99 Mohm P		7	INS				6	0006	02-FEB-2024	12:23:14	14N-1251	231136	TEST	>19.99 Mohm P	admin	Ravns Triangle					Heater
9	USER admin		8	USER				7	0007	02-FEB-2024	12:25:53	14N-1251	231137	TEST	>19.99 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Heater
10	SITE Ravns Triangle		9	SITE				8	0008	02-FEB-2024	12:29:22	14N-1251	231138	TEST	>19.99 Mohm P	admin	Ravns Triangle	0.06 ohm P	P			IEC
11	TEXT Heater		11	IEC				9	0009	02-FEB-2024	12:31:06	14N-1251	231139	TEST	>19.99 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Monitor
12	TEXT		12	LEAD CONTINUITY				10	0010	02-FEB-2024	12:33:36	14N-1251	231140	TEST	>19.99 Mohm P	admin	Ravns Triangle					Fan heater
13	TEXT		13	EARTH CURRENT				11	0011	02-FEB-2024	12:35:06	14N-1251	231141	TEST	>19.99 Mohm P	admin	Ravns Triangle					Charger
14	TEXT		14	EARTH				12	0012	02-FEB-2024	12:39:58	14N-1251	231142	TEST	>19.99 Mohm P	admin	Ravns Triangle	0.05 ohm P	P			Vertical Powe
15			15	TEXT				13	0013	02-FEB-2024	12:52:30	14N-1251	231143	TEST	>19.99 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Heater
16	TEST NUMBER 0002							14	0014	02-FEB-2024	12:56:54	14N-1251	231144	TEST	16.62 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Heater
17	DATE 02-FEB-2024							15	0015	02-FEB-2024	12:59:58	14N-1251	231145	TEST	>19.99 Mohm P	admin	Ravns Triangle					Charger
18	TIME 12:14:20							16	0016	02-FEB-2024	13:02:53	14N-1251	231146	TEST	>19.99 Mohm P	admin	Ravns Triangle			.2A	0.1 ohm P	Monitor
19	TESTER 14N-1251							17	0017	02-FEB-2024	13:05:31	14N-1251	231147	TEST	>19.99 Mohm P	admin	Ravns Triangle					Dehumidifier
20	APP NO 231132							18	0018	02-FEB-2024	13:07:20	14N-1251	231148	TEST	>19.99 Mohm P	admin	Ravns Triangle					Fan heater
21	TEST MODE TEST							19	0019	02-FEB-2024	13:09:31	14N-1251	231149	TEST	>19.99 Mohm P	admin	Ravns Triangle					tv
22	INS >19.99 Mohm P							20	0020	02-FEB-2024	13:11:36	14N-1251	231150	TEST	>19.99 Mohm P	admin	Ravns Triangle	0.09 ohm P	P			4 Way
Sheet1 (2)

Cell Formulas
Range	Formula
F2	F2	=IFERROR(FILTER(A2:A1000,(A2:A1000<>"")*(#REF!=FALSE)), "no new datatypes")
H2:V22	H2	=LET(t, A2:A10000, maxrow, MAX(ROW(t)*(t<>"")) - ROW(InputText[[#Headers],[Text]]), d, TAKE(t, maxrow), i, DROP(REDUCE("", d, LAMBDA(ini,x, VSTACK(ini,N(TAKE(ini,-1))+(x="") ))),1)+1, h, TRANSPOSE(DataTypes[Data types]), r, UNIQUE(i), r_m, DROP(REDUCE("", h, LAMBDA(ini,x, HSTACK(ini, r))),,1), h_m, DROP(REDUCE("", r, LAMBDA(ini,x, VSTACK(ini, h))),1), res, MAP(r_m, h_m, LAMBDA(x,y, IFERROR( TRIM(SUBSTITUTE(CONCAT(FILTER(d, (i=x)*(ISNUMBER(SEARCH(y, d))))), y,"")), "") )), fres, HSTACK(VSTACK("", r), VSTACK(h, res)), fres )
Dynamic array formulas.