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

 

[jkpieterse]

Can you perhaps upload one of those ASCII files to somewhere (like OneDrive) and share a link with us so we can see what's inside? I expect Data, From Text/CSV should be able to pull this off.

 

[Gazland]

Hi
Thanks for the response!
here's the link:

https://drive.google.com/file/d/10fntC7IzGs0uiDrrhJ7pja05kHNRYa4_/view?usp=share_link

 

[Gazland]

Can you perhaps upload one of those ASCII files to somewhere (like OneDrive) and share a link with us so we can see what's inside? I expect Data, From Text/CSV should be able to pull this off.

Hi
Thanks for the response!
here's the link:
https://drive.google.com/file/d/10fntC7IzGs0uiDrrhJ7pja05kHNRYa4_/view?usp=share_link

I have tried to use data-text/cv function but not had any luck.

 

[RoryA]

Those are not public links - it requires everyone to request access to help.

 

[Gazland]

Those are not public links - it requires everyone to request access to help.

Sorry about that guys.
The link should be unrestricted now.

Those are not public links - it requires everyone to request access to help.

admin_20240207_14N-1251.txt

drive.google.com

Hi Guys.
Sorry about that. The link should be unrestricted now.

 

[Gazland]

Hi
Thanks for the response!
here's the link:
admin_20240207_14N-1251.txt

I have tried to use data-text/cv function but not had any

 

[Gazland]

admin_20240207_14N-1251.txt

drive.google.com

This link should work.

 

[felixstraube]

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.

 

[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.

Thank you so much for the work you’ve done.
The result is exactly what I need. But I’m going to have to hold my hands up and admit I’m lost as to how you achieved it.
I’ve had a quick go and I think there is fundamental gaps in my knowledge. So in the interest of not wasting your time…..
I’m going to go away and watch some you tube videos and try and bridge my knowledge gap before asking further questions and having you you explain what I imagine will be the equivalent of tying shoe laces.

Again thank you very much and I really want to understand this and be able to implement it myself.
Speak soon!!