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
%%%

 

[michaelsmith559]

I have made a couple of assumptions. You need four sheets. Name the sheets: Import Data, Name, Occupation, and Salary. Following are the screenshots and macro:

Import Data Sheet:

Excel 2012
	A
1	%
2	Name
3	First name|Last name| Age
4	John|Smith|55
5	Mary|Anne|24
6	Nicole|Manda|34
7	%%%
8	%
9	Occupation
10	Title|Function|Seniority
11	Mr|Director|28
12	Mrs|Secretary|3
13	Mrs|Accountant|4
14	%%%
15	%
16	Salary
17	Crt|Salary|Month
18	1|12000|June
19	2|2340|June
20	3|2800|May
21	%%%
Sheet1

<p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Import Data</p><br /><br />

Here is the macro:

Sub Move_Data_To_Different_Sheets()
Dim rng As Range, r As Long
lastRow = Range("A1").End(xlDown).Row
If lastRow = Rows.Count Then Exit Sub

    For r = 1 To lastRow
        Set rng = Worksheets("Import Data").Range("A1:A7")
            If rng.Range("A2").Value = "Name" Then
                Worksheets("Name").Activate
                If IsEmpty(Sheets("Name").Range("A1048576").End(xlUp).Value) = True Then
                    Worksheets("Name").Range("A1048576").End(xlUp).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                Else
                    Worksheets("Name").Range("A1048576").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                End If
            ElseIf rng.Range("A2").Value = "Occupation" Then
                Worksheets("Occupation").Activate
                If IsEmpty(Sheets("Occupation").Range("A1048576").End(xlUp).Value) = True Then
                    Worksheets("Occupation").Range("A1048576").End(xlUp).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                Else
                    Worksheets("Occupation").Range("A1048576").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                End If
             Else
                If rng.Range("A2").Value = "Salary" Then
                Worksheets("Salary").Activate
                If IsEmpty(Sheets("Salary").Range("A1048576").End(xlUp).Value) = True Then
                    Worksheets("Salary").Range("A1048576").End(xlUp).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                Else
                    Worksheets("Salary").Range("A1048576").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, _
                        rng.Columns.Count).Cells.Value = rng.Cells.Value
                End If
            End If
            End If
            
        Worksheets("Import Data").Activate
        Worksheets("Import Data").Range("A1:A7").Select
        Selection.ClearContents
        Range("A1:A7").Delete Shift:=xlUp
         
    Next r
                
End Sub

after macro runs import data sheet is blank.

Name sheet:

Excel 2012
	A
1	%
2	Name
3	First name|Last name| Age
4	John|Smith|55
5	Mary|Anne|24
6	Nicole|Manda|34
7	%%%
Sheet1

<p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Name</p><br /><br />

Occupation Sheet:

Excel 2012
	A
1	%
2	Occupation
3	Title|Function|Seniority
4	Mr|Director|28
5	Mrs|Secretary|3
6	Mrs|Accountant|4
7	%%%
Sheet1

<p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Occupation</p><br /><br />

Salary Sheet:

Excel 2012
	A
1	%
2	Salary
3	Crt|Salary|Month
4	1|12000|June
5	2|2340|June
6	3|2800|May
7	%%%
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Salary</p><br /><br />

 

[Joe4]

Cross-posted:
import txt or csv delimited parts into different excel sheets
import txt or csv delimited parts into different excel sheets | Chandoo.org Excel Forums - Become Awesome in Excel
import txt or csv delimited parts into different excel sheets

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).