import txt or csv delimited parts into different excel sheets

[tuspilica]

Hello everyone,:)

I'm using excel 2013 and i have a big challenge, i have a big txt file, with more than 400000 lines. Some parts of this file i need to extract and to add them into excel, under different sheets.
These paragraphs are delimited by % as the beginning and %%% as the end of paragraph.
I'm doing this manually, copying the selected text, put it in a new excel sheet, rename the sheet with the column title, i'm doing it but it takes me a lot of time. Is there any way to do this automatically?


Below is a sample of the text file in this subject.
Thank you in advance.


Text file:

%
Name
First name|Last name| Age
John|Smith|55
Mary|Anne|24
Nicole|Manda|34
%%%
%
Occupation
Title|Function|Seniority
Mr|Director|28
Mrs|Secretary|3
Mrs|Accountant|4
%%%
%
Salary
Crt|Salary|Month
1|12000|June
2|2340|June
3|2800|May
%%%

[mancubus]

welcome to vbax.

.create a sample txt file with more data. (you can alter company specific / confidential bits)
.manually import this txt file to an excel file.
.upload these two files

i'm sure one of our members can provide a solution for you.

UPLOADING FILE(S)
Go Advanced
(scroll down) Manage Attachments
Add Files
Select Files (click on txt file name and while holding Ctrl key click on xl file name)
Open
Upload Files

[tuspilica]

Thank you Mancubus,

i've attached a sample of the txt file and the Excel result.
The usual size of the txt file is about 18Mb.

The file.txt contains about 50 sections (data parts), but i'm more particulary interested in about 25 of them.
At least 2 sections contains exactly 5002 rows. These data parts contains different kind of data, as number of rows, columns, ...
I'm not really interested to extract these sections only into one single workbook, with many sheets. It could be 2 or more with more sheets. But i prefer only one excel file, if possible.

In between %...%%% sections, it could be any other data rows unframmed by %...%%%, which i'm not interested in.


Many thanks

Added by Moderator: The *.xlsx file is the sample result. The *.zip file is the sample text file.

[SamT]

The text/csv layout appears to be:
File Header
Line n: %
line n+1: Table Name (includes String "table")
line n+2: "|" Delineated Table Headers
lines n+n: "|" Delineated Columns
%%%
unwanted data may occur here
Line n: %
line n+1: Table Name (includes String "table")
line n+2: "|" Delineated Table Headers
lines n+n: "|" Delineated Columns
%%%
unwanted data may occur here
Line n: %
line n+1: Table Name (includes String "table")
line n+2: "|" Delineated Table Headers
lines n+n: "|" Delineated Columns
%%%
unwanted data may occur here

The desired Output is:

Row 1 = Table Name
Row 2 = TableHeaders
Rows >=3 = Data

Do you have a list of the specific 25 Table Names you are interest in?

It will be easiest to code the Sheet Tab Names as the Table Name. The second easiest is the first "String-String," or "String-String-String" Instance in the Table Name.
IOW, "if-manager-status-counters-table" would result in a Tab Name of "if-manager" or "if-manager-status"

[tuspilica]

Hello SamT,

It's exactly what i need, to have the Sheet_Name as the Table_Name.
As you said, "if-manager-status-counters-table" can result in a Tab Name of "if-manager" or "if-manager-status", no problem with the name. The output is like that:
Row 1 = Table Name
Row 2 = TableHeaders
Rows >=3 = Data

Please find attached the list with 25 table names which interests me the most.
Thanks
:yes

[p45cal]

In the attached there's a button on the Table names sheet which when clicked and when the initial config.txt file you supplied is chosen, gives 22 tables (not 25) (I took tables to include only those parts delimited with "|").
Close to what's needed?

For those interested here's the macro:
Sub blah()
Dim zz()
x = Application.GetOpenFilename("TXT Files (*.txt), *.txt")
xx = CreateObject("scripting.filesystemobject").opentextfile(x).readall
yy = Split(xx, "%%%")
ReDim zz(0 To UBound(yy))
For i = 0 To UBound(yy)
zz(i) = Split(yy(i), "%")
For j = 0 To UBound(zz(i))
For Each cll In Sheets("Table names").Range("A1:A25")
wherex = InStr(zz(i)(j), cll.Value)
If wherex > 0 And InStr(zz(i)(j), "|") > 0 Then
Set NewSht = Sheets.Add(After:=Sheets(Sheets.Count))
NewSht.Name = Right(Replace(Replace(cll.Value, "-table", ""), "/", ""), 31)
dd = Split(zz(i)(j), vbCrLf)
NewSht.Cells(1).Resize(UBound(dd)) = Application.Transpose(dd)
NewSht.Columns("A:A").TextToColumns Other:=True, OtherChar:="|"
NewSht.Cells.EntireColumn.AutoFit
End If
Next cll
Next j
Next i
End Sub

[tuspilica]

i'm really greatful for your help. It was almost exactly to what i need. My ideea was not stated so clear.

Many thanks