<< How Hackers Attack SQL Server | Create a printable page in your websites using CSS >>

Read emails data from a CSV file to database

By Mohammad Mahdi Ramezanpour at July 18, 2008 22:31
Filed Under:

Sometimes you may need to import contact informations that you've exported from a mail service providers like Yahoo!, Windows Live and more to your database. In order to this you have to the following steps:

1. Write a method for add a record to database:
In order to insert only mails, you have to check the strings that come from CSV file to validate if the string is email address so:

Private Function AddNewEmail(ByVal Email As String, ByVal CatId As Int32) As Int32
    Dim check = From ma In c.Mails _
                Where ma.Email = Email _
                Select c
    If check.Count() = 0 Then
        Dim m As New Mail
        m.CatID = CatId
        m.Email = Email
        c.Mails.InsertOnSubmit(m)
        c.SubmitChanges()
    End If
End Function

2. Check the email validations:

Private Function ValidMail(ByVal EmailAddress As String) As Boolean
    If String.IsNullOrEmpty(EmailAddress) Then
        Return False
    End If

    If EmailAddress.IndexOf("@") > -1 Then
        If (EmailAddress.IndexOf(".", EmailAddress.IndexOf("@")) > EmailAddress.IndexOf("@")) Then
            Return True
        End If
    End If

Return False
End Function

In this section you have to write a method to connect to your database and insert a new record. I used LINQ to SQL becasue my project was based on .NET Framework 3.5:

3. Write a function to read data from CSV file and import them to our database using two previous functions:

Public Function ImportFile(ByVal FilePath As String, ByVal CatID As Int32) As Int32
    Dim result As Int32 = 0
    Dim CurrentLine() As String
    Using Stream As IO.StreamReader = System.IO.File.OpenText(FilePath)
        CurrentLine = Nothing
        While Not Stream.EndOfStream
            CurrentLine = Stream.ReadLine().Split(","c)
            If CurrentLine IsNot Nothing Then
                For Each c As String In CurrentLine
                    If ValidMail(c) Then
                        Dim final As String = c
                        If c.Contains(Left("""", 1)) Then
                            final = final.Replace(Left("""", 1), String.Empty)
                        End If
                        AddNewEmail(final, CatID)
                        result += 1
                    End If
                Next
            End If
        End While
    End Using
    Return result
End Function