Importing CSV

I am working to develop an app that will import two CSV files. During the import I need the single quotation marks removed from a couple of the fields. Then I need the data imported into a local SQLCE database. Once the user makes their changes I need to then take the two tables, group the data into three queries that then Union merge into one query that I can export as a CSV.

I have the forms completed. I need help with the easy part :) the code to make it all work.

Here is the code I currently have for importing it. It worked once, then I switched PCs and it stopped working. I get an error that too many connections are open. The code is...

Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "DataControl.sdf"
        'Dim password As String = ""
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        'connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DbName, password)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not System.IO.File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (15) null, " +
                "Reg numeric (5,2) null, " +
                "OT numeric (5,2) null, " +
                "FromDate nvarchar (100) null, " +
                "ToDate nvarchar (100) null, " +
                "Position nvarchar(20) null) "
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            While Not sr.EndOfData
                Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
                cmd1.CommandText = "SELECT * FROM hours"
                If cn1.State = ConnectionState.Closed Then
                    cn1.Open()
                End If
                Dim Line = sr.ReadFields()
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetDecimal(1, Line(1))
                rec.SetDecimal(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5))
                rs.Insert(rec)
            End While
        End Using
        Return True
rawilkenAsked:
Who is Participating?
 
nepaluzConnect With a Mentor Commented:
I will not refine this a lot, lest you get into more errors! Here's the complete re-hash which should not raise the error you are getting.
    Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "DataControl.sdf"
        'Dim password As String = ""
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        'connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DbName, password)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not System.IO.File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (15) null, " +
                "Reg numeric (5,2) null, " +
                "OT numeric (5,2) null, " +
                "FromDate nvarchar (100) null, " +
                "ToDate nvarchar (100) null, " +
                "Position nvarchar(20) null) "
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
            cmd1.CommandText = "SELECT * FROM hours"
            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetDecimal(1, Line(1))
                rec.SetDecimal(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Open in new window

0
 
nepaluzCommented:
SQLCE allows quite a few connections! you must be running the program multiple times doing the same thing. Anyhow, ni your last question, my last suggestion was to move these lines
Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
cmd1.CommandText = "SELECT * FROM hours"
If cn1.State = ConnectionState.Closed Then
    cn1.Open()
End If

Open in new window

outside the While Not sr.EndOfData line.
0
 
rawilkenAuthor Commented:
How do I delete the single quotes from the fields of data that have them?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nepaluzCommented:
Not sure what field contains double quotes, and these should have been removed by the TextFieldParser, anyhow,do this for the respective line
rec.SetString(0, Line(0).Replace("""", String.Empty))

Open in new window

Could you also give an example of the file if possible?
0
 
rawilkenAuthor Commented:
It now adds exponentially more records....
0
 
nepaluzCommented:
And the file?
0
 
rawilkenAuthor Commented:
I had a line out of place.
0
 
nepaluzCommented:
My bad. I moved the resultset declaration outside the loop (anddid not notice that you'd removed the unique key constraint from the table!)
    Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "DataControl.sdf"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not System.IO.File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            If cn1.State = ConnectionState.Closed Then cn1.Open()
            Dim sqlString As String = "create table hours (EmployeeID nvarchar (15) null, Reg numeric (5,2) null, OT numeric (5,2) null, FromDate nvarchar (100) null, ToDate nvarchar (100) null, Position nvarchar(20) null) "
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
            cmd1.CommandText = "SELECT * FROM hours"
            If cn1.State = ConnectionState.Closed Then cn1.Open()
            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetDecimal(1, Line(1))
                rec.SetDecimal(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Open in new window

PS. You do not need to send the file (not for me anyhow), I have checked this to work as you requested, bar removing the double quotes (for which you need to apply the .Replace("""", String.Empty) as elaborated in a previous post). I'll make this my last post here as I need to attend to other tasks, so if this does not meet your requirement, another expert can assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.