Visual Basic

start with http://www.emoreau.com/Entries/Articles/2011/11/Using-the-FileHelpers-Library.aspx and http://emoreau.com/Entries/Articles/2010/05/Do-you-know-the-TextFieldParser.aspx

handle the button's click event, add code to parse the csv file then import into your database. Some clarification wanted though:
1. Local database you say, what database?
2. How is your CSV file formed? i.e, what fields in the CSV file?
3. What are the fields that you want imported into the database?

The csv is comma delimited. There are 6 fields but no field labels. Four of the fields are enclosed in single quotes that I want to remove. The fields are...
EmployeeID string (enclosed in single quotes)
Reg single
OT single
From DateTime (enclosed in single quotes)
To datatime (enclosed in single quotes)
Position string (enclosed in single quotes)

I have a local table called hours that I want to import the data into

FileHelprs throws errors on testing class.

I think I have the code to read the csv into an array. Now I need to input that array into the local sdf database table named hours.

Public Function ReadCSV(FileName As String)
        Dim strPath As String
        Dim strAp As String
        strPath = GetPath()
        strAp = "\" & FileName & ".csv"
        Dim engine As New FileHelperEngine(Of Hours)()
        Dim res As Hours() = engine.ReadFile("C:\Data Control\Macro Network Solutions\TimeAndPay\TimeAndPay\bin\Debug\hours.csv")
        For Each record As Hours In res
            Return record
        Next
        Return res
    End Function
    <DelimitedRecord(",")> _
    Public NotInheritable Class Hours
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public EmployeeID As String
        Public Reg As Single
        Public OT As Single
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public FromDate As String
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public ToDate As String
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public Position As String
    End Class

I have put together a boolean function for you. Pass the file path as argument and it will read your csv and update a database. Feel free to change the names etc

    Public Function SaveToDB(ByVal FileName As String) As Boolean

        Dim DBName As String = "YourDataBase.sdf"
        Dim password As String = "YourPassword"
        Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", FileName, password)
        'connectionString = String.Format("Data Source=""{0}"";", fileName)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (25) not null, " +
                "Reg nvarchar (10) not null, " +
                "OT nvarchar (12) not null, " +
                "FromDT nvarchar (15) not null, " +
                "ToDT nvarchar (15) not null, " +
                "constraint pk_EmployeeID primary key (EmployeeID))"

            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            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 AccountsTable"

            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.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Select allOpen in new window

PS. I have added a password to the database (on creation) to automatically encrypt the database on creation. Simply remove the password to create an un-encrypted one.

Wow. I can track most of this. I am not picking up where the path and file for the csv is provided.

It throws an error when running it.
Error3.jpg

when you call the function, you pass the file path in the variable FileName.
Have you tried running it, and what are your results?

move thisline

Dim cmd1 As SqlCeCommand = cn1.CreateCommand()

Select allOpen in new window

from line 27 to between lines 30 and 31 (i.e after the While Not sr.EndOfData)

Actually, move both lines and amend the second one too!

Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
cmd1.CommandText = "SELECT * FROM hours"

Select allOpen in new window

I have also added a check for the number of fields in any line read from the CSV to be more than 4 fields, so you have

    Public Function SaveToDB(ByVal FileName As String) As Boolean

        Dim DBName As String = "YourDataBase.sdf"
        Dim password As String = "YourPassword"
        Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", FileName, password)
        'connectionString = String.Format("Data Source=""{0}"";", fileName)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (25) not null, " +
                "Reg nvarchar (10) not null, " +
                "OT nvarchar (12) not null, " +
                "FromDT nvarchar (15) not null, " +
                "ToDT nvarchar (15) not null, " +
                "constraint pk_EmployeeID primary key (EmployeeID))"

            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            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()
                If Not Line.Count > 4 Then Continue While
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Select allOpen in new window

I modified the filed check to the five fields I actually am using. I am getting a URI format not supported error. Here is a screenshot of it.
Error-4.JPG

Is there a simple way to delete the data that is in these tables before I go and import more data?

What is the path that you supplied?
I also found another error in the connectionString variable, change it to:

Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DBName, password)

Select allOpen in new window

I have checked the code and it runs OK, infact, you did not need to change the position of the lines of code I made earlier, but you need to add the check for a connection, i.e you can move these lines to just before the second while loop

Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
cmd1.CommandText = "SELECT * FROM hours"
If cn1.State = ConnectionState.Closed Then
    cn1.Open()
End If

Select allOpen in new window

This is the delete I came up with...

Dim sqlString As String = "DELETE FROM hours"
        Dim cmd = New SqlCeCommand(sqlString)
        cmd.ExecuteNonQuery()

Path is current path of application plus file name...
I do a get path and then append the file name...

file:\C:\Data Control\Macro~\hours.csv

I am still getting the URI function error on the "Using sr..." line

Here is the code as I have it...

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 float (8,2) null, " +
                "OT float (8,2) null, " +
                "FromDT nvarchar (25) null, " +
                "ToDT nvarchar (25) 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.SetString(1, Line(1))
                rec.SetString(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

Screen shot of error...
Error-5.JPG

Got it. The GetPath needed to be modified.

I have not heard a response from you for hours so I will assume that my code suggestion answered your query.

I am getting a strange error on the database. It is saying the "path is not a legal form". I fixed this once by deleting and recreating the database. Is there a different solution?

Still needs refining.