Solved

Importing CSV

Posted on 2012-03-11
8
448 Views
Last Modified: 2012-03-13
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
0
Comment
Question by:rawilken
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
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
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
Comment Utility
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
 

Author Comment

by:rawilken
Comment Utility
How do I delete the single quotes from the fields of data that have them?
0
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:rawilken
Comment Utility
It now adds exponentially more records....
0
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
And the file?
0
 

Author Comment

by:rawilken
Comment Utility
I had a line out of place.
0
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now