NotePad to Excel without delimitator??

[Mikeg262]

Hey All,

I have a data set that is about 5k lines (in Notepad), it is in the format (shown below) where each row contains the line of data I want to put into 4 or 5 columns. The data does not have a delimitator (as I understand it anyway; I am not a pro at this). I have tried importing it with different delimitator settings, copying the data into a single column in Excel and using different combos of OFFSET. ROW and COLUMN, even considered using IFs, ANDs and such, but that became complicated quickly. I tried using the Query function in excel, but never used it before and wasn't sure what I was doing, but I thought if I could transpose each line to a column I could write a pretty simple formula to combine the similar columns, but it would not separate the data neatly. of course searched the web and forums for solutions but did not find any good ones. All of this did not solve the issues obviously. My final thought was to type a delimitator like ";" after each line, but I don't know if that'll work and it will take hours. So I would be happy to have a little insight.

Data format in Notepad:
(Line 1) From: XXXXXXXXXX
(Line 2) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)
(Line 3) Source App: Nameof App
(Line 4) Body:
(Line 5) XXXXXXXXXXXXXXXXXXXXXXX
(Line 6) -------------------------------------
(Line 7) From: XXXXXXXXXX
(Line 8) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)
(Line 9) Source App: Nameof App
(Line 10) Body:
(Line 11) XXXXXXXXXXXXXXXXXXXXXXX
(Line 12) -------------------------------------

This then repeats with for the rest of the document. Each entry is separated by --------
The line numbers are not part of the document and the XXXXX and names vary in length, but are all contained to one line.
The body and message are two separate lines, if they end up as separate columns I can easily fix that later.
I don't care about making the dashes into columns, but I can easily delete that later.

I would like to say thank you in advance. I really appreciate all the help!

 

[Candyman8019]

This process will be a shot in the dark, but I think it might work...may need some tweaking.

1) open the file in Excel...all will be in column A.
2) In column B, append each line with a comma using a formula such as =A1 & ","
3) Copy Column B and "paste values" back in column B. This will essentially get rid of the formulas.
4) Delete column A.
5) Do a find/replace in the entire document to replace "----------------------," with a line feed, ie =char(10)
6) Save as a text file.
7) Import into Excel as a comma delimited text file.

 

[Mikeg262]

This process will be a shot in the dark, but I think it might work...may need some tweaking.

1) open the file in Excel...all will be in column A.
2) In column B, append each line with a comma using a formula such as =A1 & ","
3) Copy Column B and "paste values" back in column B. This will essentially get rid of the formulas.
4) Delete column A.
5) Do a find/replace in the entire document to replace "----------------------," with a line feed, ie =char(10)
6) Save as a text file.
7) Import into Excel as a comma delimited text file.

Ok So I did all this and I tried a few different arrangements. Now all the data is the same as above followed by a "," and I saved it as a Text (tab delimin) .txt file (also tried Formated Text (space delim) file and a Unicode text file. When I saved it, it added quotes before and after each line. I tried to import as a comma delaminated file from each of these text files and used a variety of options, but it still gives me one column. I also did a find and replace on the quotes and removed them. What I noticed in the Data Preview window of the import file is that when I select Comma as the delimiter, it creates a second column, but does not move any data into the separate column. The only option that separates the data is the Space delimiter, but this separates every word...

 

[Mikeg262]

Ok So I did all this and I tried a few different arrangements. Now all the data is the same as above followed by a "," and I saved it as a Text (tab delimin) .txt file (also tried Formated Text (space delim) file and a Unicode text file. When I saved it, it added quotes before and after each line. I tried to import as a comma delaminated file from each of these text files and used a variety of options, but it still gives me one column. I also did a find and replace on the quotes and removed them. What I noticed in the Data Preview window of the import file is that when I select Comma as the delimiter, it creates a second column, but does not move any data into the separate column. The only option that separates the data is the Space delimiter, but this separates every word...

I should add that selecting comma as a delimiter adds a second column to the preview window AND removes the commas from the end of the lines of text.

 

[Candyman8019]

Okay, I figured this one out, tried it and it works.
- You have your list of data all in column A
- in column B, number the rows starting at 0 and incrementing by 6. ie, 0,6,12,18,24...etc

Once you have that first row C-H set you can simply copy that row down until it arranges all of your data. After that you can do a copy/pastevalues to replace those formulas with actual data and then delete columns A and B and you should be set.

delimited.csv
	A	B	C	D	E	F	G	H
1	(Line 1) From: XXXXXXXXXX	0	(Line 1) From: XXXXXXXXXX	(Line 2) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	(Line 3) Source App: Nameof App	(Line 4) Body:	(Line 5) XXXXXXXXXXXXXXXXXXXXXXX	(Line 6) -------------------------------------
2	(Line 2) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	6	(Line 7) From: XXXXXXXXXX	(Line 8) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	(Line 9) Source App: Nameof App	(Line 10) Body:	(Line 11) XXXXXXXXXXXXXXXXXXXXXXX	Line 13
3	(Line 3) Source App: Nameof App	12	Line 14	Line 15	Line 16	Line 17	Line 18	Line 19
4	(Line 4) Body:	18	Line 20	Line 21	Line 22	Line 23	Line 24	Line 25
5	(Line 5) XXXXXXXXXXXXXXXXXXXXXXX	24	Line 26	Line 27	Line 28	Line 29	Line 30	Line 31
6	(Line 6) -------------------------------------	30	Line 32	Line 33	Line 34	Line 35	Line 36	Line 37
7	(Line 7) From: XXXXXXXXXX	36	Line 38	Line 39	Line 40	Line 41	Line 42	Line 43
8	(Line 8) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	42	Line 44	Line 45	Line 46	Line 47	Line 48	Line 49
9	(Line 9) Source App: Nameof App	48	Line 50	Line 51	Line 52	Line 53	Line 54	Line 55
10	(Line 10) Body:	54	Line 56	Line 57	Line 58	Line 59	Line 60	Line 61
11	(Line 11) XXXXXXXXXXXXXXXXXXXXXXX	60	Line 62	Line 63	Line 64	Line 65	Line 66	Line 67
12	Line 13	66	Line 68	Line 69	Line 70	Line 71	Line 72	Line 73
13	Line 14	72	Line 74	Line 75	Line 76	Line 77	Line 78	Line 79
14	Line 15	78	Line 80	Line 81	Line 82	Line 83	Line 84	Line 85
15	Line 16
16	Line 17
17	Line 18
18	Line 19
19	Line 20
20	Line 21
21	Line 22
22	Line 23
23	Line 24
24	Line 25
25	Line 26
26	Line 27
delimited

Cell Formulas
Range	Formula
C1:C14	C1	=+OFFSET(A$1,B1,0)
D1:D14	D1	=+OFFSET(A$1,B1+1,0)
E1:E14	E1	=+OFFSET(A$1,B1+2,0)
F1:F14	F1	=+OFFSET(A$1,B1+3,0)
G1:G14	G1	=+OFFSET(A$1,B1+4,0)
H1:H14	H1	=+OFFSET(A$1,B1+5,0)

 

[Mikeg262]

Okay, I figured this one out, tried it and it works.
- You have your list of data all in column A
- in column B, number the rows starting at 0 and incrementing by 6. ie, 0,6,12,18,24...etc

Once you have that first row C-H set you can simply copy that row down until it arranges all of your data. After that you can do a copy/pastevalues to replace those formulas with actual data and then delete columns A and B and you should be set.

delimited.csv
	A	B	C	D	E	F	G	H
1	(Line 1) From: XXXXXXXXXX	0	(Line 1) From: XXXXXXXXXX	(Line 2) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	(Line 3) Source App: Nameof App	(Line 4) Body:	(Line 5) XXXXXXXXXXXXXXXXXXXXXXX	(Line 6) -------------------------------------
2	(Line 2) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	6	(Line 7) From: XXXXXXXXXX	(Line 8) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	(Line 9) Source App: Nameof App	(Line 10) Body:	(Line 11) XXXXXXXXXXXXXXXXXXXXXXX	Line 13
3	(Line 3) Source App: Nameof App	12	Line 14	Line 15	Line 16	Line 17	Line 18	Line 19
4	(Line 4) Body:	18	Line 20	Line 21	Line 22	Line 23	Line 24	Line 25
5	(Line 5) XXXXXXXXXXXXXXXXXXXXXXX	24	Line 26	Line 27	Line 28	Line 29	Line 30	Line 31
6	(Line 6) -------------------------------------	30	Line 32	Line 33	Line 34	Line 35	Line 36	Line 37
7	(Line 7) From: XXXXXXXXXX	36	Line 38	Line 39	Line 40	Line 41	Line 42	Line 43
8	(Line 8) Timestamp: mm/dd/yyyy hr:mm:sc PM(UTC+0)	42	Line 44	Line 45	Line 46	Line 47	Line 48	Line 49
9	(Line 9) Source App: Nameof App	48	Line 50	Line 51	Line 52	Line 53	Line 54	Line 55
10	(Line 10) Body:	54	Line 56	Line 57	Line 58	Line 59	Line 60	Line 61
11	(Line 11) XXXXXXXXXXXXXXXXXXXXXXX	60	Line 62	Line 63	Line 64	Line 65	Line 66	Line 67
12	Line 13	66	Line 68	Line 69	Line 70	Line 71	Line 72	Line 73
13	Line 14	72	Line 74	Line 75	Line 76	Line 77	Line 78	Line 79
14	Line 15	78	Line 80	Line 81	Line 82	Line 83	Line 84	Line 85
15	Line 16
16	Line 17
17	Line 18
18	Line 19
19	Line 20
20	Line 21
21	Line 22
22	Line 23
23	Line 24
24	Line 25
25	Line 26
26	Line 27
delimited

Cell Formulas
Range	Formula
C1:C14	C1	=+OFFSET(A$1,B1,0)
D1:D14	D1	=+OFFSET(A$1,B1+1,0)
E1:E14	E1	=+OFFSET(A$1,B1+2,0)
F1:F14	F1	=+OFFSET(A$1,B1+3,0)
G1:G14	G1	=+OFFSET(A$1,B1+4,0)
H1:H14	H1	=+OFFSET(A$1,B1+5,0)

Awesome! This worked, there were some errors in the data that took me awhile to correct, mostly erroneous blank rows. But once that was all cleared up this method worked perfectly. Thank you so much!