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

Andre' Gardiner

Rate me:

4.83/5 (5 votes)

26 Jan 2010CPOL2 min read

27.6K

1.8K

Parsing text file data to a Microsoft Excel database or formatted text file.

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. :)

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);
    }
}