• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

import txt or csv delimited parts into different excel sheets

tuspilica

New Member
Hello everyone,
icon_smile.gif


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
%%%
__________________________________________________________________
Mod edit : post moved to appropriate forum
 

Attachments

  • config.txt
    3.8 KB · Views: 5
Hi !

Welcome to Chandoo !

It may be difficult by Excel formulas to do this automatically !
For this kind of stuff, you must post to this VBA Macros forum …​

rename the sheet with the column title
column title ? Which, where ?‼

Better is to upload an expected output layout workbook
according to your attached text file …
 
1) Length of sheet name is up to 31 characters.
2) Following characters can not be used for sheet name
: / \ [ ] ? *

How do you want it when it happens?
 
Dear Marc,
i've attached an Excel file that contains data extracted from config.txt file.
It should look something like that ...
 

Attachments

  • config debug excel.xlsx
    20.1 KB · Views: 3
See if this works
Code:
Sub test()
    Dim fn As String, txt As String, i As Long, x, m As Object, temp
    fn = Application.GetOpenFilename("TextFiles,*.txt")
    If fn = "False" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "%\r\n([^%\r\n]+)\r\n([^%]*\r\n)+(?=%{3})"
        If .test(txt) Then
            Set m = .Execute(txt)
            For i = 0 To m.Count - 1
                x = Split(m(i).submatches(1), vbNewLine)
                .Pattern = "^.{10,30}(?=([/_-]|$))"
                temp = Replace(.Execute(m(i).submatches(0))(0), "/", "_")
                If Sheets.Count < i + 1 Then Sheets.Add after:=Sheets(Sheets.Count)
                With Sheets(i + 1)
                    .Cells.Clear: .Name = temp
                    .[a1].Value = m(i).submatches(0)
                    .[a2].Resize(UBound(x) + 1).Value = Application.Transpose(x)
                    .Columns(1).TextToColumns .[a1], 1, other:=True, OtherChar:="|"
                    .Columns.AutoFit
                End With
            Next
        End If
    End With
End Sub
 
@tuspilica

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.

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: Forum Rules

Cross-posts:
http://www.mrexcel.com/forum/excel-...imited-parts-into-different-excel-sheets.html
http://www.excelguru.ca/forums/show...erent-excel-sheets&p=22477&posted=1#post22477
http://www.excelforum.com/excel-pro...imited-parts-into-different-excel-sheets.html
 
Back
Top