Click here to Skip to main content
11,482,402 members (72,579 online)
Click here to Skip to main content

Text Parser Using Regex Method to Export Searched Text to Microsoft Excel

, 26 Jan 2010 CPOL 14.9K 1.2K 11
Rate this:
Please Sign up or sign in to vote.
Parsing text file data to a Microsoft Excel database or formatted text file.

filtermitxt_exe

Introduction

I have developed a method to parse text file data to Microsoft Excel database or a formatted text file. In the early stage of development, I decided to use C# because it would save me a lot of time and it's faster to develop with. This program helps anyone who knows Regular Expressions to search for specific text in an unformatted text file and place their search results in a table so that they are able to manipulate the data found after exporting it to Microsoft Excel. Therefore, I developed that I can put together to achieve a fully functional desktop program.

Enjoy. Smile | :)

Background

These is a list of custom method/projects used to achieve this program functionality:

  • Extract - Reads data from the ListView and exports it to Microsoft Excel using COM plug-ins.
  • UListview -This is a custom ListView which you can alter and add controls and so forth to it.
  • Urlcombo - This is a custom combo box, but this project was meant for a browser.
  • Text2ExcelParser - This project is the GUI for the filterMitext project.

Using the Code

Extract function: Using Microsoft .NET headings, I simply used a foreach and for loop to read data from the ListView. Refer to the code snippet below:

//
// using Microsoft.Office.Interop.Excel;
worksheet.Name = reportName;

//Headers
foreach (ColumnHeader columnHeader in listView.Columns)
    worksheet.Cells[1, columnHeader.Index + 1] = columnHeader.Text;    
    //Content
    for (int i = 0; i < listView.Items.Count; i++)
        for (int j = 0; j < listView.Columns.Count; j++)
            worksheet.Cells[i + 2, j + 1] = 
                listView.Items[i].SubItems[j].Text.ToString();
    //Also, you can use the Windows System.Windows.Form.SaveFileDialog to achieve this.
    savefDlg.Filter = " Excel Text format|*.xls| Text |*.txt| All files |*.*";
    if (savefDlg.ShowDialog() == DialogResult.OK)
    {
        list2txtfilename = savefDlg.FileName;
        Listview2Text(list2txtfilename);
        this.Text = filename + " - FilterMiText Expresso";
    }

Text2ExcelParser: This is the GUI for the project. I made a text editor with useful functions which you can use to easily edit text. Also, the parser function is built in.

This code snippet is really the back bone of the project. The algorithm is based on a Find/Search method, but I spiced it up so that the data found is extracted to the ListView. See the details below.

//Refer to parse Text Information region.
 
while (_shouldStop == false)
{                
    if (FindFirst)
    {
        FindFirst = false;
        regex1 = new Regex(First_urlComboBox.Text, 
                           RegexOptions.Multiline);
        match1 = regex1.Match(richTextBox1.Text);
                        
    }else if (!FindFirst)
    {                             
        match1 = match1.NextMatch();
    }       
    if (match1.Success){
        lvi = new ListViewItem(new string[] {   
           match1.ToString() });  
    }
    else
    {
        FindFirst = true;
        MessageBox.Show(String.Format("Finish Parsing {0} records in {1}", 
              UltimateListView1.Items.Count, stopWatch.Elapsed),
              Application.ProductName + " Complete :)", 
              MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

Points of Interest

This project is about developing ideas for parsing text to Excel. Feel free to add or upgrade this program because it may be useful. Enjoy Smile | :)

History

  • 2007-2009 project.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Andre' Gardiner
Software Developer
Jamaica Jamaica
I'm fascinated about new technology and innovations.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 80532276-Sep-12 8:57
memberMember 80532276-Sep-12 8:57 
GeneralIs it possible to... Pin
fairplay228428-Oct-10 19:18
memberfairplay228428-Oct-10 19:18 
GeneralRe: Is it possible to... Pin
Andre' Gardiner29-Oct-10 10:32
memberAndre' Gardiner29-Oct-10 10:32 
GeneralRe: Is it possible to... Pin
fairplay228430-Oct-10 21:24
memberfairplay228430-Oct-10 21:24 
AnswerRe: Is it possible to... Pin
Andre' Gardiner15-Nov-10 11:34
memberAndre' Gardiner15-Nov-10 11:34 
Well, you can do it in a number of cases but using regex expression in your application is one. Wink | ;)
for example.
If this was the text show below, therefore, to copy the 2line before and after the keyword. You can think of a regex exp. to do so.

TextTextTextTextText
TextTextTextTextText
TextTextTextTextText
Keyword
TextTextTextTextText
TextTextTextTextText
TextTextTextTextText

I think of one by using this regex exp. ((.+)$\n(.+)$\n^)(Keyword)(\n^(.+)\n^(.+)). This would get the 2line before and after the keyword including the keyword with can be easily replace. Try it using my application to test it.

Regular expression is really flexible because you can use more than one technique to capture text. Thumbs Up | :thumbsup:

Hope this helps. Cool | :cool:
Sir.Dre

GeneralRe: Is it possible to... Pin
fairplay228417-Nov-10 1:00
memberfairplay228417-Nov-10 1:00 
GeneralMy vote of 5 Pin
deqa15-Jul-10 21:27
memberdeqa15-Jul-10 21:27 
GeneralRe: My vote of 5 Pin
Andre' Gardiner2-Aug-10 12:08
memberAndre' Gardiner2-Aug-10 12:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 26 Jan 2010
Article Copyright 2010 by Andre' Gardiner
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid