Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Importing CSV

Posted on 2012-03-11
8
Medium Priority
?
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 37707885
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 2000 total points
ID: 37707902
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
ID: 37707913
How do I delete the single quotes from the fields of data that have them?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 17

Expert Comment

by:nepaluz
ID: 37707921
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
 

Author Comment

by:rawilken
ID: 37707977
It now adds exponentially more records....
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37707979
And the file?
0
 

Author Comment

by:rawilken
ID: 37708015
I had a line out of place.
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37708021
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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