Importing CSV

rawilken
rawilken used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Author

Commented:
How do I delete the single quotes from the fields of data that have them?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Commented:
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?

Author

Commented:
It now adds exponentially more records....

Commented:
And the file?

Author

Commented:
I had a line out of place.

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial