When using the regex is there a way to make it more accurate?

Question

0.00/5 (No votes)

See more:

The problem I am having is that when trying to import text in a formatted style, somethings go to their designated spots. However, this seems to happen when large text data is being imported. If I cut and paste each block of data individually the code seems to work.

Here is a sample of the information and format I am using:

Page:	a-01-1 & a-01-2
Number:	34
Deceased_name:	Wm Aaron
Race: W
Sex:	M
Age:	34
Marital_status:	
Place_of_birth:  DuBois
Occupation:	
Date_of_death: Feb 28, 1895
Place_of_death:	DuBois
Cause_of_death:	Erysiplas
Duration_of_illness:	2 wks
Cemetery:	Karthaus cemetery
Date_of_burial:	Mar 4, 1895
Fathers_name:	
Mothers_name:	 
Record_date:	June 4, 1895
	
Page:	a-05-1 & a-05-2
Number:	10
Deceased_name:	Mary Abdenhof 
Race:	W 
Sex:	F
Age:	1 mo
Marital_status:	
Place_of_birth:	Irvona Bor
Occupation:	
Date_of_death:	14-Apr-03
Place_of_death:	Borough
Cause_of_death:	Pneumonia
Duration_of_illness:	1 wk
Cemetery:	Hagertys X Road
Date_of_burial:	16-Apr-03
Fathers_name:	Edward H Abdenhof
Mothers_name:	Catharine Abdenhof
Record_date:	13-May-03

here is the code I am using:

VB

Dim sqlconn As New SqlConnection("constring")
       Dim sqladaptor = New SqlDataAdapter("select * from" + "[" + Form1.TreeView1.SelectedNode.Text.ToString + "]", sqlconn)

       sqlconn.Open()

       Dim ds As New DataSet
       sqladaptor.Fill(ds, Form1.TreeView1.SelectedNode.Text.ToString)
       Dim dt As DataTable = ds.Tables(Form1.TreeView1.SelectedNode.Text.ToString)
       sqlconn.Close()


       Dim rows() As String = Regex.Split(RichTextBox1.Text, "^\r?$", RegexOptions.Multiline)
       Dim pattern As New Regex("^\s*(?<label>[^:]+)\s*:\s*(?<value>.+)\s*\r?$", RegexOptions.Multiline)

       Dim ID As String = Nothing, Yearofbirth As String = Nothing, Firstname As String = Nothing, Lastname As String = Nothing, Middlename As String = Nothing, Placeofbirth As String = Nothing, Stateofbirth As String = Nothing,
               Race As String = Nothing, Residence As String = Nothing, Mothersname As String = Nothing, Mothersfirst As String = Nothing, Mothersmaiden As String = Nothing, Mothersbirthplace As String = Nothing, Maritalstatus As String = Nothing, Spouse As String = Nothing, Profession As String = Nothing,
               Yearofdeath As String = Nothing, SSN As String = Nothing, Placeofdeath As String = Nothing, Stateofdeath As String = Nothing, Mannerofdeath As String = Nothing, Funeralhome As String = Nothing, Placeofburial As String = Nothing, Stateofburial As String = Nothing,
               Inquest As String = Nothing, Inquestday As String = Nothing, Inquestmonth As String = Nothing, Inquestyear As String = Nothing, Microfilm_fileNumber As String = Nothing, Recordmonth As String = Nothing, Recordday As String = Nothing, Recordyear As String = Nothing,
               Statedeathcertificate As String = Nothing, Countydeathcertificate As String = Nothing, Statebirthcertificate As String = Nothing, Countybirthcertificate As String = Nothing, age As String = Nothing, Fathersname As String = Nothing, Fathersfirst As String = Nothing, Fatherslast As String = Nothing,
               Fathersbirthplace As String = Nothing, Sex As String = Nothing, Parents As String = Nothing, Burialorcremation As String = Nothing, Cemetery As String = Nothing, Causeofdeath As String = Nothing, Autopsy As String = Nothing, Funeralhomeaddress As String = Nothing,
               Claimed As String = Nothing, Unclaimed As String = Nothing, Hospital As String = Nothing, Instituteifgiven As String = Nothing, Instituteaddress As String = Nothing, Countyofdeath As String = Nothing, burial_date As String = Nothing, Monthofburial As String = Nothing,
               Dayofburial As String = Nothing, Year_of_burial As String = Nothing, month_of_death As String = Nothing, day_of_death As String = Nothing, Suffix As String = Nothing, Record_date As String = Nothing, Date_of_death As String = Nothing, Deceased_name As String = Nothing, Occupation As String = Nothing,
               Duration_of_illness As String = Nothing, Number As String = Nothing, Page As String = Nothing


       For Each row As String In rows

           Dim matches As MatchCollection = pattern.Matches(row)
           For Each match As Match In matches
               Dim label As String = match.Groups("label").Value

               '    For Each column In ds.Tables(Form1.TreeView1.SelectedNode.Text.ToString).Columns
               'do a for each statement based on either dataset from sql statement or from checkedlistbox items

               If String.Equals(label, "ID", StringComparison.OrdinalIgnoreCase) Then
                   ID = match.Groups("value").Value

               ElseIf String.Equals(label, "First_name", StringComparison.OrdinalIgnoreCase) Then
                   Firstname = match.Groups("value").Value
               ElseIf String.Equals(label, "Last_name", StringComparison.OrdinalIgnoreCase) Then
                   Lastname = match.Groups("value").Value
               ElseIf String.Equals(label, "Middle_name", StringComparison.OrdinalIgnoreCase) Then
                   Middlename = match.Groups("value").Value
               ElseIf String.Equals(label, "Year_of_birth", StringComparison.OrdinalIgnoreCase) Then
                   Yearofbirth = match.Groups("value").Value
               ElseIf String.Equals(label, "Place_of_birth", StringComparison.OrdinalIgnoreCase) Then
                   Placeofbirth = match.Groups("value").Value
               ElseIf String.Equals(label, "State_of_birth", StringComparison.OrdinalIgnoreCase) Then
                   Stateofbirth = match.Groups("value").Value
               ElseIf String.Equals(label, "Race", StringComparison.OrdinalIgnoreCase) Then
                   Race = match.Groups("value").Value
               ElseIf String.Equals(label, "Residence", StringComparison.OrdinalIgnoreCase) Then
                   Residence = match.Groups("value").Value
               ElseIf String.Equals(label, "Mothers_Name", StringComparison.OrdinalIgnoreCase) Then
                   Mothersname = match.Groups("value").Value
               ElseIf String.Equals(label, "Mothers_first", StringComparison.OrdinalIgnoreCase) Then
                   Mothersfirst = match.Groups("value").Value
               ElseIf String.Equals(label, "Mothers_maiden", StringComparison.OrdinalIgnoreCase) Then
                   Mothersmaiden = match.Groups("value").Value
               ElseIf String.Equals(label, "Mothers_birth_place", StringComparison.OrdinalIgnoreCase) Then
                   Mothersbirthplace = match.Groups("value").Value
               ElseIf String.Equals(label, "Marital_status", StringComparison.OrdinalIgnoreCase) Then
                   Maritalstatus = match.Groups("value").Value
               ElseIf String.Equals(label, "Spouse", StringComparison.OrdinalIgnoreCase) Then
                   Spouse = match.Groups("value").Value
               ElseIf String.Equals(label, "Profession", StringComparison.OrdinalIgnoreCase) Then
                   Profession = match.Groups("value").Value
               ElseIf String.Equals(label, "Year_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Yearofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "SSN", StringComparison.OrdinalIgnoreCase) Then
                   SSN = match.Groups("value").Value
               ElseIf String.Equals(label, "Place_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Placeofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "State_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Stateofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "Manner_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Mannerofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "Funeral_home", StringComparison.OrdinalIgnoreCase) Then
                   Funeralhome = match.Groups("value").Value
               ElseIf String.Equals(label, "Place_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   Placeofburial = match.Groups("value").Value
               ElseIf String.Equals(label, "State_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   Stateofburial = match.Groups("value").Value
               ElseIf String.Equals(label, "Inquest", StringComparison.OrdinalIgnoreCase) Then
                   Inquest = match.Groups("value").Value
               ElseIf String.Equals(label, "Inquest_day", StringComparison.OrdinalIgnoreCase) Then
                   Inquestday = match.Groups("value").Value
               ElseIf String.Equals(label, "Inquest_month", StringComparison.OrdinalIgnoreCase) Then
                   Inquestmonth = match.Groups("value").Value
               ElseIf String.Equals(label, "Inquest_year", StringComparison.OrdinalIgnoreCase) Then
                   Inquestyear = match.Groups("value").Value
               ElseIf String.Equals(label, "Microfilm_file_Number", StringComparison.OrdinalIgnoreCase) Then
                   Microfilm_fileNumber = match.Groups("value").Value
               ElseIf String.Equals(label, "Record_month", StringComparison.OrdinalIgnoreCase) Then
                   Recordmonth = match.Groups("value").Value
               ElseIf String.Equals(label, "Record_day", StringComparison.OrdinalIgnoreCase) Then
                   Recordday = match.Groups("value").Value
               ElseIf String.Equals(label, "Record_year", StringComparison.OrdinalIgnoreCase) Then
                   Recordyear = match.Groups("value").Value
               ElseIf String.Equals(label, "State_death_certificate", StringComparison.OrdinalIgnoreCase) Then
                   Statedeathcertificate = match.Groups("value").Value
               ElseIf String.Equals(label, "County_death_certificate", StringComparison.OrdinalIgnoreCase) Then
                   Countydeathcertificate = match.Groups("value").Value
               ElseIf String.Equals(label, "State_birth_certificate", StringComparison.OrdinalIgnoreCase) Then
                   Statebirthcertificate = match.Groups("value").Value
               ElseIf String.Equals(label, "County_birth_certificate", StringComparison.OrdinalIgnoreCase) Then
                   Countybirthcertificate = match.Groups("value").Value
               ElseIf String.Equals(label, "Age", StringComparison.OrdinalIgnoreCase) Then
                   age = match.Groups("value").Value
               ElseIf String.Equals(label, "Fathers_first", StringComparison.OrdinalIgnoreCase) Then
                   Fathersfirst = match.Groups("value").Value
               ElseIf String.Equals(label, "Fathers_name", StringComparison.OrdinalIgnoreCase) Then
                   Fathersname = match.Groups("value").Value
               ElseIf String.Equals(label, "Fathers_last", StringComparison.OrdinalIgnoreCase) Then
                   Fatherslast = match.Groups("value").Value
               ElseIf String.Equals(label, "Fathers_birth_place", StringComparison.OrdinalIgnoreCase) Then
                   Fathersbirthplace = match.Groups("value").Value
               ElseIf String.Equals(label, "Sex", StringComparison.OrdinalIgnoreCase) Then
                   Sex = match.Groups("value").Value
               ElseIf String.Equals(label, "Parents", StringComparison.OrdinalIgnoreCase) Then
                   Parents = match.Groups("value").Value
               ElseIf String.Equals(label, "Burial_or_cremation", StringComparison.OrdinalIgnoreCase) Then
                   Burialorcremation = match.Groups("value").Value
               ElseIf String.Equals(label, "Cemetery", StringComparison.OrdinalIgnoreCase) Then
                   Cemetery = match.Groups("value").Value
               ElseIf String.Equals(label, "Cause_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Causeofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "Autopsy", StringComparison.OrdinalIgnoreCase) Then
                   Autopsy = match.Groups("value").Value
               ElseIf String.Equals(label, "Funeral_home_address", StringComparison.OrdinalIgnoreCase) Then
                   Funeralhomeaddress = match.Groups("value").Value
               ElseIf String.Equals(label, "Claimed", StringComparison.OrdinalIgnoreCase) Then
                   Claimed = match.Groups("value").Value
               ElseIf String.Equals(label, "Unclaimed", StringComparison.OrdinalIgnoreCase) Then
                   Unclaimed = match.Groups("value").Value
               ElseIf String.Equals(label, "Hospital", StringComparison.OrdinalIgnoreCase) Then
                   Hospital = match.Groups("value").Value
               ElseIf String.Equals(label, "Institute_if_given", StringComparison.OrdinalIgnoreCase) Then
                   Instituteifgiven = match.Groups("value").Value
               ElseIf String.Equals(label, "Institute_address", StringComparison.OrdinalIgnoreCase) Then
                   Instituteaddress = match.Groups("value").Value
               ElseIf String.Equals(label, "County_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Countyofdeath = match.Groups("value").Value
               ElseIf String.Equals(label, "Date_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   burial_date = match.Groups("value").Value
               ElseIf String.Equals(label, "Month_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   Monthofburial = match.Groups("value").Value
               ElseIf String.Equals(label, "Day_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   Dayofburial = match.Groups("value").Value
               ElseIf String.Equals(label, "Year_of_burial", StringComparison.OrdinalIgnoreCase) Then
                   Year_of_burial = match.Groups("value").Value
               ElseIf String.Equals(label, "Month_of_death", StringComparison.OrdinalIgnoreCase) Then
                   month_of_death = match.Groups("value").Value
               ElseIf String.Equals(label, "Day_of_death", StringComparison.OrdinalIgnoreCase) Then
                   day_of_death = match.Groups("value").Value
               ElseIf String.Equals(label, "Suffix", StringComparison.OrdinalIgnoreCase) Then
                   Suffix = match.Groups("value").Value
               ElseIf String.Equals(label, "Record_date", StringComparison.OrdinalIgnoreCase) Then
                   Record_date = match.Groups("value").Value
               ElseIf String.Equals(label, "Date_of_death", StringComparison.OrdinalIgnoreCase) Then
                   Date_of_death = match.Groups("value").Value
               ElseIf String.Equals(label, "Deceased_name", StringComparison.OrdinalIgnoreCase) Then
                   Deceased_name = match.Groups("value").Value
               ElseIf String.Equals(label, "Occupation", StringComparison.OrdinalIgnoreCase) Then
                   Occupation = match.Groups("value").Value
               ElseIf String.Equals(label, "Duration_of_illness", StringComparison.OrdinalIgnoreCase) Then
                   Duration_of_illness = match.Groups("value").Value
               ElseIf String.Equals(label, "Number", StringComparison.OrdinalIgnoreCase) Then
                   Number = match.Groups("value").Value
               ElseIf String.Equals(label, "Page", StringComparison.OrdinalIgnoreCase) Then
                   Page = match.Groups("value").Value
               End If

           Next

           Try

               Dim sqlcon As New SqlConnection(constring)

               sqlcon.Open()

               Dim cb2 As New StringBuilder("INSERT INTO ")
               cb2.AppendFormat("[" + Form1.TreeView1.SelectedNode.Text.ToString + "] (")
               Dim sbOn As New StringBuilder("")
               Dim sbon1 As New StringBuilder
               Dim andRequired As Boolean = False

               For Each item As DataColumn In dt.Columns
                   If andRequired Then
                       sbOn.Append(", ")
                   End If

                   Dim columnName As String = item.ColumnName
                   sbOn.AppendFormat(columnName)
                   andRequired = True

               Next

               cb2.Append(sbOn.ToString() + ") values(")

               Dim andRequired1 As Boolean = False
               For Each item As DataColumn In dt.Columns ' DataGridViewColumn Form1.DataGridView1.Columns
                   If andRequired1 Then
                       sbon1.Append(", ")
                   End If

                   Dim columnName As String = item.ColumnName
                   sbon1.AppendFormat("@" + columnName)
                   andRequired1 = True

               Next

               cb2.Append(sbon1.ToString() + ")")

               '  MessageBox.Show(cb2.ToString)

               Dim cmd As New SqlCommand(cb2.ToString, sqlcon)
               'Order of items goes by the order of this string
               ' Make sure that all tables have the same type of information that can be entered, if not you will recieve an error.

               Dim I As Integer
               I += 1

               If dt.Columns.Contains("ID") Then
                   cmd.Parameters.AddWithValue("@Id", If(String.IsNullOrEmpty(ID), dt.Rows.Count + I, ID))
               End If
               If dt.Columns.Contains("Age") Then
                   cmd.Parameters.AddWithValue("@Age", If(String.IsNullOrEmpty(age), "", age))
               End If
               If dt.Columns.Contains("Fathers_name") Then
                   cmd.Parameters.AddWithValue("@Fathers_name", If(String.IsNullOrEmpty(Fathersname), "", Fathersname))
               End If
               If dt.Columns.Contains("Sex") Then
                   cmd.Parameters.AddWithValue("@Sex", If(String.IsNullOrEmpty(Sex), "", Sex))
               End If
               If dt.Columns.Contains("Place_of_birth") Then
                   cmd.Parameters.AddWithValue("@Place_of_birth", If(String.IsNullOrEmpty(Placeofbirth), "", Placeofbirth))

               End If
               If dt.Columns.Contains("Race") Then
                   cmd.Parameters.AddWithValue("@Race", If(String.IsNullOrEmpty(Race), "", Race))
               End If
               If dt.Columns.Contains("Marital_status") Then
                   cmd.Parameters.AddWithValue("@Marital_status", If(String.IsNullOrEmpty(Maritalstatus), "", Maritalstatus))
               End If
               If dt.Columns.Contains("Record_date") Then
                   cmd.Parameters.AddWithValue("@Record_date", If(String.IsNullOrEmpty(Record_date), "", Record_date))

               End If
               If dt.Columns.Contains("Date_of_death") Then
                   cmd.Parameters.AddWithValue("@Date_of_death", If(String.IsNullOrEmpty(Date_of_death), "", Date_of_death))

               End If
               If dt.Columns.Contains("Deceased_name") Then
                   cmd.Parameters.AddWithValue("@Deceased_name", If(String.IsNullOrEmpty(Deceased_name), "", Deceased_name))

               End If
               If dt.Columns.Contains("Occupation") Then
                   cmd.Parameters.AddWithValue("@Occupation", If(String.IsNullOrEmpty(Occupation), "", Occupation))
               End If
               If dt.Columns.Contains("Duration_of_illness") Then
                   cmd.Parameters.AddWithValue("@Duration_of_illness", If(String.IsNullOrEmpty(Duration_of_illness), "", Duration_of_illness))
               End If
               If dt.Columns.Contains("Number") Then
                   cmd.Parameters.AddWithValue("@Number", If(String.IsNullOrEmpty(Number), "", Number))

               End If
               If dt.Columns.Contains("Page") Then
                   cmd.Parameters.AddWithValue("@Page", If(String.IsNullOrEmpty(Page), "", Page))
               End If
               If dt.Columns.Contains("Mothers_name") Then
                   cmd.Parameters.AddWithValue("@Mothers_name", If(String.IsNullOrEmpty(Mothersname), "", Mothersname))
               End If
               If dt.Columns.Contains("Cause_of_death") Then
                   cmd.Parameters.AddWithValue("@Cause_of_death", If(String.IsNullOrEmpty(Causeofdeath), "", Causeofdeath))
               End If
               If dt.Columns.Contains("Cemetery") Then
                   cmd.Parameters.AddWithValue("@Cemetery", If(String.IsNullOrEmpty(Cemetery), "", Cemetery))
               End If
               If dt.Columns.Contains("Date_of_burial") Then
                   cmd.Parameters.AddWithValue("@Date_of_burial", If(String.IsNullOrEmpty(burial_date), "", burial_date))
               End If
               If dt.Columns.Contains("Place_of_death") Then
                   cmd.Parameters.AddWithValue("@Place_of_death", If(String.IsNullOrEmpty(Placeofdeath), "", Placeofdeath))
               End If
               cmd.Parameters.AddWithValue("@Fathers_first", If(String.IsNullOrEmpty(Fathersfirst), "", Fathersfirst))
               cmd.Parameters.AddWithValue("@Fathers_last", If(String.IsNullOrEmpty(Fatherslast), "", Fatherslast))
               cmd.Parameters.AddWithValue("@Fathers_birth_place", If(String.IsNullOrEmpty(Fathersbirthplace), "", Fathersbirthplace))
               cmd.Parameters.AddWithValue("@First_name", If(String.IsNullOrEmpty(Firstname), "", Firstname))
               cmd.Parameters.AddWithValue("@Last_name", If(String.IsNullOrEmpty(Lastname), "", Lastname))
               cmd.Parameters.AddWithValue("@Middle_name", If(String.IsNullOrEmpty(Middlename), "", Middlename))
               cmd.Parameters.AddWithValue("@Year_of_birth", If(String.IsNullOrEmpty(Yearofbirth), "", Yearofbirth))
               cmd.Parameters.AddWithValue("@State_of_birth", If(String.IsNullOrEmpty(Stateofbirth), "", Stateofbirth))
               cmd.Parameters.AddWithValue("@Residence", If(String.IsNullOrEmpty(Residence), "", Residence))
               cmd.Parameters.AddWithValue("@Mothers_first", If(String.IsNullOrEmpty(Mothersfirst), "", Mothersfirst))
               cmd.Parameters.AddWithValue("@Mothers_maiden", If(String.IsNullOrEmpty(Mothersmaiden), "", Mothersmaiden))
               cmd.Parameters.AddWithValue("@Mothers_birth_place", If(String.IsNullOrEmpty(Mothersbirthplace), "", Mothersbirthplace))
               cmd.Parameters.AddWithValue("@Spouse", If(String.IsNullOrEmpty(Spouse), "", Spouse))
               cmd.Parameters.AddWithValue("@Profession", If(String.IsNullOrEmpty(Profession), "", Profession))
               cmd.Parameters.AddWithValue("@Year_of_death", If(String.IsNullOrEmpty(Yearofdeath), "", Yearofdeath))
               cmd.Parameters.AddWithValue("@SSN", If(String.IsNullOrEmpty(SSN), "", SSN))
               cmd.Parameters.AddWithValue("@State_of_death", If(String.IsNullOrEmpty(Stateofdeath), "", Stateofdeath))
               cmd.Parameters.AddWithValue("@Manner_of_death", If(String.IsNullOrEmpty(Mannerofdeath), "", Mannerofdeath))
               cmd.Parameters.AddWithValue("@Funeral_home", If(String.IsNullOrEmpty(Funeralhome), "", Funeralhome))
               cmd.Parameters.AddWithValue("@Place_of_burial", If(String.IsNullOrEmpty(Placeofburial), "", Placeofburial))
               cmd.Parameters.AddWithValue("@State_of_burial", If(String.IsNullOrEmpty(Stateofburial), "", Stateofburial))
               cmd.Parameters.AddWithValue("@Inquest", If(String.IsNullOrEmpty(Inquest), "", Inquest))
               cmd.Parameters.AddWithValue("@Inquest_day", If(String.IsNullOrEmpty(Inquestday), "", Inquestday))
               cmd.Parameters.AddWithValue("@Inquest_month", If(String.IsNullOrEmpty(Inquestmonth), "", Inquestmonth))
               cmd.Parameters.AddWithValue("@Inquest_year", If(String.IsNullOrEmpty(Inquestyear), "", Inquestyear))
               cmd.Parameters.AddWithValue("@Microfilm_file_Number", If(String.IsNullOrEmpty(Microfilm_fileNumber), "", Microfilm_fileNumber))
               cmd.Parameters.AddWithValue("@Record_month", If(String.IsNullOrEmpty(Recordmonth), "", Recordmonth))
               cmd.Parameters.AddWithValue("@Record_day", If(String.IsNullOrEmpty(Recordday), "", Recordday))
               cmd.Parameters.AddWithValue("@Record_year", If(String.IsNullOrEmpty(Recordyear), "", Recordyear))
               cmd.Parameters.AddWithValue("@State_death_certificate", If(String.IsNullOrEmpty(Statedeathcertificate), "", Statedeathcertificate))
               cmd.Parameters.AddWithValue("@County_death_certificate", If(String.IsNullOrEmpty(Countydeathcertificate), "", Countydeathcertificate))
               cmd.Parameters.AddWithValue("@State_birth_certificate", If(String.IsNullOrEmpty(Statebirthcertificate), "", Statebirthcertificate))
               cmd.Parameters.AddWithValue("@County_birth_certificate", If(String.IsNullOrEmpty(Countybirthcertificate), "", Countybirthcertificate))
               cmd.Parameters.AddWithValue("@Parents", If(String.IsNullOrEmpty(Parents), "", Parents))
               cmd.Parameters.AddWithValue("@Burial_or_cremation", If(String.IsNullOrEmpty(Burialorcremation), "", Burialorcremation))
               cmd.Parameters.AddWithValue("@Autopsy", If(String.IsNullOrEmpty(Autopsy), "", Autopsy))
               cmd.Parameters.AddWithValue("@Funeral_home_address", If(String.IsNullOrEmpty(Funeralhomeaddress), "", Funeralhomeaddress))
               cmd.Parameters.AddWithValue("@Claimed", If(String.IsNullOrEmpty(Claimed), "", Claimed))
               cmd.Parameters.AddWithValue("@Unclaimed", If(String.IsNullOrEmpty(Unclaimed), "", Unclaimed))
               cmd.Parameters.AddWithValue("@Hospital", If(String.IsNullOrEmpty(Hospital), "", Hospital))
               cmd.Parameters.AddWithValue("@Institute_if_given", If(String.IsNullOrEmpty(Instituteifgiven), "", Instituteifgiven))
               cmd.Parameters.AddWithValue("@Institute_address", If(String.IsNullOrEmpty(Instituteaddress), "", Instituteaddress))
               cmd.Parameters.AddWithValue("@County_of_death", If(String.IsNullOrEmpty(Countyofdeath), "", Countyofdeath))
               cmd.Parameters.AddWithValue("@Month_of_burial", If(String.IsNullOrEmpty(Monthofburial), "", Monthofburial))
               cmd.Parameters.AddWithValue("@Day_of_burial", If(String.IsNullOrEmpty(Dayofburial), "", Dayofburial))
               cmd.Parameters.AddWithValue("@Year_of_burial", If(String.IsNullOrEmpty(Year_of_burial), "", Year_of_burial))
               cmd.Parameters.AddWithValue("@Month_of_death", If(String.IsNullOrEmpty(month_of_death), "", month_of_death))
               cmd.Parameters.AddWithValue("@Day_of_death", If(String.IsNullOrEmpty(day_of_death), "", day_of_death))
               cmd.Parameters.AddWithValue("@Suffix", If(String.IsNullOrEmpty(Suffix), "", Suffix))


               cmd.ExecuteNonQuery()

               ' sqlcon.Close()

           Catch ex As Exception
               MsgBox(ex.Message)

           End Try

       Next

Can someone explain why the code I am using is not importing properly?

What I have tried:

Since there are debugging errors, I can not figure out where I can change the code to make it more accurate.

Posted 19-Feb-18 5:41am

Member 11856456

Updated 19-Feb-18 9:01am

PIEBALDconsult

v5

Add a Solution

Comments

F-ES Sitecore 19-Feb-18 12:00pm

Given the problem is dependent on the text you give it, and we don't know what text you are giving it, it's hard to see how anyone can help. Even if we did have the text it's unlikely someone is going to go through your massive code dump to try and work out the issues. You should do some debugging to try and isolate the issue.

PIEBALDconsult 19-Feb-18 12:01pm

Where to begin? You're definitely working too hard -- such as .Text.ToString .
There are so many ways that that code can be simplified and improved.
But first, are you saying that you have these huge streams of key/value pairs stored in a database? What do they look like? Are the keys always present and in the same order?

Member 11856456 19-Feb-18 12:06pm

yes, I am trying to get these values into a database. Here are the information and the format that I am using at this time.

Page: a-01-1 & a-01-2
Number: 34
Deceased_name: Wm Aaron
Race: W
Sex: M
Age: 34
Marital_status:
Place_of_birth: DuBois
Occupation:
Date_of_death: Feb 28, 1895
Place_of_death: DuBois
Cause_of_death: Erysiplas
Duration_of_illness: 2 wks
Cemetery: Karthaus cemetery
Date_of_burial: Mar 4, 1895
Fathers_name:
Mothers_name:
Record_date: June 4, 1895

Page: a-05-1 & a-05-2
Number: 10
Deceased_name: Mary Abdenhof
Race: W
Sex: F
Age: 1 mo
Marital_status:
Place_of_birth: Irvona Bor
Occupation:
Date_of_death: 14-Apr-03
Place_of_death: Borough
Cause_of_death: Pneumonia
Duration_of_illness: 1 wk
Cemetery: Hagertys X Road
Date_of_burial: 16-Apr-03
Fathers_name: Edward H Abdenhof
Mothers_name: Catharine Abdenhof
Record_date: 13-May-03

what do you mean by key?

Patrice T 19-Feb-18 12:13pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

PIEBALDconsult 19-Feb-18 12:35pm

And you're trying to write the result to a different table -- does that table exist?
And where does the data come from before it's shoved into the database?

Richard MacCutchan 19-Feb-18 12:36pm

That's around 400 lines of code that you are expecting someone to debug for you. You need to do more work to find out what is happening, and explain clearly what goes wrong and under what circumstances.

PIEBALDconsult 19-Feb-18 13:00pm

And a lot of the code is needless.

3 solutions

Solution 3

Quote:
Since there are debugging errors, I can not figure out where I can change the code to make it more accurate.

Better accuracy imply a study of errors.
I usually handle this by detecting records with errors and dump those records to a log file, so I can see the reason of error and improve code.

Try to replace your RegEx with

^\s*(?<label>[^:\n]+)\s*:\s+(?<value>\w[^:\n]*)\s*$

matching looks better.

Just a few interesting links to help building and debugging RegEx.
Here is a link to RegEx documentation:
perlre - perldoc.perl.org[^]
Here is links to tools to help build RegEx and debug them:
.NET Regex Tester - Regex Storm[^]
Expresso Regular Expression Tool[^]
RegExr: Learn, Build, & Test RegEx[^]
This one show you the RegEx as a nice graph which is really helpful to understand what is doing a RegEx:
Debuggex: Online visual regex tester. JavaScript, Python, and PCRE.[^]

Posted 19-Feb-18 9:58am

Patrice T

Updated 19-Feb-18 17:47pm

v6

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2018-02-19T09:01:00

I won't debug your code, but i have one very important suggestion.

You should work on data instead of set of variables... By saying "data" i mean specific structure or object with set of members, properties, etc. See:
Object-Oriented Programming (Visual Basic) | Microsoft Docs[^]
Object Oriented Programming In VB.NET[^]

In that case i'd suggest to create a class. Take a look:

VB.NET

Public Class MyData

	Private sPage As String =""
	Private iNumber As Integer = 1
	Private sDeceasedName As String = ""
	Private sRace As String = ""
	Private sSex As String = ""
	Private sAge As String = ""
	Private sMaritalStatus As String = ""
	Private sPlaceOfBirth As String = ""
	Private sOccupation As String = ""
	Private dDateOfDeath? As Date
	Private sPlaceOfDeath As String = ""
	Private sCauseOfDeath As String = ""
	Private sDurationOfIllness As String = ""
	Private sCemetery As String = ""
	Private dDateOfBurial? As Date
	Private sFathersName As String = ""
	Private sMothersName As String = ""
	Private dRecordDate? As Date
	

	Public Property Page As String
		Get
			Return sPage
		End Get
		Set (value As String)
			sPage = value
		End Set
	End Property
	
	Public Property Number As Integer 
		Get
			Return iNumber 
		End Get
		Set (value As Integer)
			iNumber = value
		End Set
	End Property

	Public Property DeceasedName As String 
		Get
			Return sDeceasedName
		End Get
		Set (value As String)
			sDeceasedName = value
		End Set
	End Property
	
	Public Property Race As String 
		Get
			Return sRace
		End Get
		Set (value As String)
			sRace = value
		End Set
	End Property
	
	Public Property Sex As String 
		Get
			Return sSex
		End Get
		Set (value As String)
			sSex = value
		End Set
	End Property
	
	Public Property Age As String
		Get
			Return sAge
		End Get
		Set (value As String)
			sAge = value
		End Set
	End Property
	
	Public Property MaritalStatus As String 
		Get
			Return sMaritalStatus
		End Get
		Set (value As String)
			sMaritalStatus = value 
		End Set
	End Property
	
	Public Property PlaceOfBirth As String
		Get
			Return sPlaceOfBirth
		End Get
		Set (value As String)
			sPlaceOfBirth = value
		End Set
	End Property
	
	Public Property Occupation As String 
		Get
			Return sOccupation
		End Get
		Set (value As String)
			sOccupation = value
		End Set
	End Property
	
	Public Property DateOfDeath As Nullable(Of Date)
		Get
			Return dDateOfDeath
		End Get
		Set (value? As Date)
			dDateOfDeath = value
		End Set
	End Property
	
	Public Property PlaceOfDeath As String
	 	Get
			Return sPlaceOfDeath
		End Get
		Set (value As String)
			sPlaceOfDeath = value
		End Set
	End Property

	Public Property CauseOfDeath As String 
		Get
			Return sCauseOfDeath
		End Get
		Set (value As String)
			sCauseOfDeath = value
		End Set
	End Property
	
	Public Property DurationOfIllness As String 
		Get
			Return sDurationOfIllness
		End Get
		Set (value As String)
			sDurationOfIllness = value
		End Set
	End Property
	
	Public Property Cemetery As String
	 	Get
			Return sCemetery
		End Get
		Set (value As String)
			sCemetery = value
		End Set
	End Property
	
	Public Property DateOfBurial As Nullable(Of Date)
		Get
			Return dDateOfBurial
		End Get
		Set (value? As Date)
			dDateOfBurial = value
		End Set
	End Property
	
	Public Property FathersName As String 
		Get
			Return sFathersName
		End Get
		Set (value As String)
			sFathersName = value
		End Set
	End Property
	
	Public Property MothersName As String 
		Get
			Return sMothersName
		End Get
		Set (value As String)
			sMothersName = value
		End Set
	End Property
	
	Public Property RecordDate As Nullable(Of Date)
		Get
			Return dRecordDate
		End Get
		Set (value? As Date)
			dRecordDate = value
		End Set
	End Property

End Class

How to use it? Assuming, your data are stored in text file in the format you provided. You can use Linq to get a number of lines from text file then to split it by [:] delimiter.

VB.NET

Sub Main
        'full file name
	Dim sFileName As String = "D:\FullFileName.txt"
        'read all lines
	Dim lines As String() = File.ReadAllLines(sFileName)
         'get the number of lines
	Dim lCount As Integer = lines.Count()
        'i - counter, recordLength - the number of lines to import
	Dim i As Integer = 0, recordLength = 19
        'a list of custom class
	Dim myList As List(Of MyData) = New List(Of MyData)
        'single object
        Dim data As MyData = Nothing

	Do While i * recordLength <= lCount
		Dim record = lines.Skip(i * recordLength).Take(recordLength)
		data = New MyData
		With data
			.Page = record(0).Split(":")(1).Trim()
			.Number = record(1).Split(":")(1).Trim() 
			.DeceasedName = record(2).Split(":")(1).Trim()
			.Race = record(3).Split(":")(1).Trim()
			.Sex = record(4).Split(":")(1).Trim()
			.Age = record(5).Split(":")(1).Trim()
			.MaritalStatus = record(6).Split(":")(1).Trim()
			.PlaceOfBirth = record(7).Split(":")(1).Trim()
			.Occupation = record(8).Split(":")(1).Trim()
			.DateOfDeath = record(9).Split(":")(1).Trim()
			.PlaceOfDeath = record(10).Split(":")(1).Trim()
			.CauseOfDeath = record(11).Split(":")(1).Trim()
			.DurationOfIllness = record(12).Split(":")(1).Trim()
			.Cemetery = record(13).Split(":")(1).Trim()
			.DateOfBurial = record(14).Split(":")(1).Trim()
			.FathersName = record(15).Split(":")(1).Trim()
			.MothersName = record(16).Split(":")(1).Trim()
			.RecordDate = record(17).Split(":")(1).Trim()
		End With

		myList.Add(data)
		i += 1
	Loop

	'myList is ready to use ;)
	For Each data In myList
		Console.WriteLine("Name: '{0}' Sex: '{1}'", data.DeceasedName, data.Sex)
	Next data
End Sub

Above code "prints":

Name: 'Wm Aaron' Sex: 'M'
Name: 'Mary Abdenhof' Sex: 'F'

Note: treat above code as an inspiration. Feel free to change it.

phil.o · Accepted Answer · 2018-02-19T08:19:00

You should have a look at the free tool Expresso[^].
It will allow you to test your regular expression againt whatever input you want to test.
Applied to your question, I could spot in a few seconds that the empty line (holding a tab character) in the middle of the sample is not matched by the regular expression.

Proposed modification:

VB.NET

Dim pattern As New Regex("^\s*(?<label>[^:]+)\s*:\s*(?<value>.+)\s*?$|^[\t]*$", RegexOptions.Multiline)

(Since the regex is defined as multiline, you do not have to test for \r nor \n)
Now your sample properly validates; but, for the empty line in the middle, there are no values in the groups. Thus, you should test for that in your code.

Hope this helps. Kindly.

[Edit]
As stated in comments, you should definitely simplify your code, as there are a lot of things do not need to be there:

By default a string value is Nothing if not initialized; no need to enforce that.
Calling ToString() on a variable which already is a string is perceived as some kind of abomination for any serious programmer.

Note, I'm not trying to be harsch, really; you should understand these few remarks as an advise, not a pun.
[/Edit]

When using the regex is there a way to make it more accurate?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0