Multiple Regex String Patterns (Different Fields)

Question

I am trying to extract text from a word document with the following format and inserting the data into a SQL database.

Word Document

Name of House: Aasleagh Lodge
Townland: Srahatloe
Near: Killary Harbour, Leenane
Status/Public Access: maintained, private fishing lodge
Date Built: 1838-1850, burnt 1923, rebuilt 1928

Source Code

        var wordApp = new Microsoft.Office.Interop.Word.Application();
        var wordDoc = wordApp.Documents.Open(@"C:\Users\mhoban\Documents\Book.docx");
        var txt = wordDoc.Content.Text;

        var regex = new Regex(@"(Name of House\: )(.+?)[\r\n]");

        var allMatches = regex.Matches(txt);
        foreach (Match match in allMatches)
        {
            var nameValue = match.Groups[2].Value;
            var townValue = match.Groups[2].Value;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            SqlCommand com = new SqlCommand();

            com.CommandText = "INSERT INTO Houses (Name, Townland) VALUES (@name, @town)";

            com.Parameters.Add("@name", SqlDbType.NVarChar).SqlValue = nameValue;
            com.Parameters.Add("@town", SqlDbType.NVarChar).SqlValue = townValue;

            com.Connection = con;

            con.Open();

            com.ExecuteNonQuery();

            con.Close();
        }

This works perfectly the only thing is how would I write the code to insert the other fields of text for example this line

var regex = new Regex(@"(Name of House\: )(.+?)[\r\n]");

Inserts the name of the house in this case "Aasleagh Lodge" but how would I write this line to insert the townland?

I tried replacing "Townland" in the regex with the field name I require but I end up with singular records each only holding one different column value.

Is there a way I could insert the data at the same time maybe by using a list or something so this would not occur.

New Source Code

var wordApp = new Microsoft.Office.Interop.Word.Application();
            var wordDoc = wordApp.Documents.Open(@"C:\Users\mhoban\Documents\Book.docx");
            var txt = wordDoc.Content.Text;

            using (var sr = new StringReader(txt))
            {
                var s = string.Empty;
                var nameValue = new StringBuilder();
                var townValue = new StringBuilder();
                while ((s = sr.ReadLine()) != null)
                {
                    if (s.StartsWith("Name of House"))
                    {
                        nameValue.Append(s.Split(new[] { ':' })[1].Trim());
                    }
                    else if (s.StartsWith("Townland"))
                    {
                        townValue.Append(s.Split(new[] { ':' })[1].Trim());
                    }

                    if (nameValue.Length > 0 && townValue.Length > 0)
                    {
                        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
                        SqlCommand com = new SqlCommand();

                        com.CommandText = "INSERT INTO Houses (Name, Townland) VALUES (@name, @town)";
                        com.CommandText = "INSERT INTO Houses (Name) VALUES (@name)";

                        com.Parameters.Add("@name", SqlDbType.NVarChar).SqlValue = nameValue;
                        com.Parameters.Add("@town", SqlDbType.NVarChar).SqlValue = townValue;

                        com.Connection = con;

                        con.Open();

                        com.ExecuteNonQuery();

                        con.Close();

                        nameValue.Clear(); townValue.Clear();
                    }
                }
            }

Database Fields

[Id]          NCHAR (10)     NULL,
[Name]        NVARCHAR (MAX) NULL,
[Townland]    NVARCHAR (MAX) NULL,
[Near]        NVARCHAR (MAX) NULL,
[Status]      NVARCHAR (MAX) NULL,
[Built]       NVARCHAR (MAX) NULL,
[Description] NVARCHAR (MAX) NULL,
[Families]    NVARCHAR (MAX) NULL,
[Images]      IMAGE          NULL

Answer 1

Here is a solution without regex. You really do not need it here.

var txt = "Name of House: Aasleagh Lodge\r\nTownland: Srahatloe\r\nNear: Killary Harbour, Leenane\r\nStatus/Public Access: maintained, private fishing lodge\r\nDate Built: 1838-1850, burnt 1923, rebuilt 1928\r\nName of House: House of Lan\r\nTownland: Another town land\r\nNear: Killary Harbour, Leenane\r\nStatus/Public Access: maintained, private fishing lodge\r\nDate Built: 1838-1850, burnt 1923, rebuilt 1928\r\nName of House: New Lodge\r\nTownland: NewTownLand\r\nNear: Killary Harbour, Leenane\r\nStatus/Public Access: maintained, private fishing lodge\r\nDate Built: 1838-1850, burnt 1923, rebuilt 1928";
using (var sr = new StringReader(txt))
{
   var s = string.Empty;
   var nameOfHouse = new StringBuilder();
   var townland = new StringBuilder();
   while ((s = sr.ReadLine()) != null)
   {
      if (s.StartsWith("Name of House"))
      {
          nameOfHouse.Append(s.Split(new[] {':'})[1].Trim());
      }
      else if (s.StartsWith("Townland"))
      {
           townland.Append(s.Split(new[] { ':' })[1].Trim());
      }

      if (nameOfHouse.Length > 0 && townland.Length > 0)
      { 
          // INSERT THE VALUES AND RESET THEM
          nameOfHouse.Clear(); townland.Clear();
      }
   }
}

Answer 2

You can use the following regex:

(.*?\: )(.+?)[\r\n]

See DEMO

Also.. you could replace [\r\n] in your regex with $ for matching last like where \r or \n are optional.

i.e:

var regex = new Regex(@"^(.*?\: )(.+?)$");

Answer 3

Yes, this is possible. However, just making the regex generic only solves half your problem, because you also have to know what database column each value maps to.

Here is the general approach I would take:

1. Have something that defines each parameter name you might encounter in the file and its corresponding column in the database. This could be just a dictionary in your code, but a more mature design would involve putting it in some kind of external configuration.

2. Use a simple String.Split based on : for each line to put all of the key/value pairs into a dictionary (a regex is overkill here).

3. Build the insert statement based on the data in the first two steps above.

Answer 4

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication21
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.txt";
        static void Main(string[] args)
        {
            StreamReader reader = new StreamReader(FILENAME);
            string inputLine = "";
            List<TakenBMI> takenBMIs = new List<TakenBMI>();
            TakenBMI newTakenBMI = null;
            while ((inputLine = reader.ReadLine()) != null)
            {
                inputLine = inputLine.Trim();
                if (inputLine.Length > 0)
                {
                    string[] inputArray = inputLine.Split(new char[] { ':' });
                    switch (inputArray[0].Trim())
                    {
                        case "Name of House":
                            newTakenBMI = new TakenBMI();
                            takenBMIs.Add(newTakenBMI);
                            newTakenBMI.Name_of_House = inputArray[1].Trim();
                            break;
                        case "Townland":
                            newTakenBMI.Townland = inputArray[1].Trim();
                            break;
                        case "Near":
                            newTakenBMI.Near = inputArray[1].Trim();
                            break;
                        case "Status/Public Access":
                            newTakenBMI.Status_Public_Access = inputArray[1].Trim();
                            break;
                        case "Date Built":
                            newTakenBMI.Date_Built = inputArray[1].Trim();
                            break;
                    }
                }

            }

            reader.Close();

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            string SQL = "INSERT INTO Houses (Name, Townland, Near, Status, Built)" +
                "VALUES ('@name', '@town', '@near', '@status', '@built')";

            SqlCommand com = new SqlCommand(SQL,con);
           

            com.Parameters.Add("@name", SqlDbType.NVarChar);
            com.Parameters.Add("@town", SqlDbType.NVarChar);
            com.Parameters.Add("@near", SqlDbType.NVarChar);
            com.Parameters.Add("@status", SqlDbType.NVarChar);
            com.Parameters.Add("@built", SqlDbType.NVarChar);
 
            foreach (TakenBMI takenBMI in takenBMIs)
            {
                com.Parameters["@name"].Value = takenBMI.Name_of_House ;
                com.Parameters["@town"].Value = takenBMI.Townland;
                com.Parameters["@near"].Value = takenBMI.Near;
                com.Parameters["@status"].Value = takenBMI.Status_Public_Access;
                com.Parameters["@built"].Value = takenBMI.Date_Built;
               
                com.ExecuteNonQuery();
            }

        }
    }
    public class TakenBMI
    {
        public string Name_of_House { get; set; }
        public string Townland { get; set; }
        public string Near { get; set; }
        public string Status_Public_Access { get; set; }
        public string Date_Built { get; set; }
    }
}