CSV to Dataset

[Tinnin]

Hi All,

I'm taking a different approach to importing the csv to access and trying to import the csv to the dataset.

I've found the following code but it's giving me an error

Dim fileToOpen As String
        Dim da As New OleDbDataAdapter()
        Dim ds As New DataSet()

        fileToOpen = ImportFiles.SelectedItem

        If fileToOpen <> "" Then
            Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\myFilePath\;Extended Properties=" & _
            ControlChars.Quote & "Text;HDR=YES;FMT=Delimited" & ControlChars.Quote)
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM C:\Users\myFilePath\" & fileToOpen, cn)

            cn.Open()

            da.SelectCommand = cmd
            da.Fill(ds)

            cn.Close()

            Me.DataGridView1.DataSource = ds.Tables(0)
        Else
            MsgBox("Please choose a file to open from the list below!")
        End If

The error: Syntax error in FROM clause

I can't figure out why it is producing this error.

[Tinnin]

Okay, I think I've found the problem. One of the files in the file path has spaces between the words in its filename.

How do I get around that without changing the filename?

[Tinnin]

Got around it with this

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM 'C:\Users\Me\My File\" & fileToOpen & "'", cn)

But now I'm getting a new error:
''C:\Users\Me\My File\File To Open.csv'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

[deceptikon]

Just use the file name and not the full path in the query. The path without the filename would go in the connection string. Here's a working example you can use as a template if you'd like:

    Private Function CsvGenerateView(filename As String, hasHeader As Boolean) As DataSet
        Dim connectionString = String.Format( _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""{0}"";Extended Properties=""Text;HDR={1}FMT=CSVDelimited"";", _
            Path.GetDirectoryName(filename), _
            If(hasHeader, "Yes", "No"))
        Dim query = "select * from [" & Path.GetFileName(filename) & "]"
        Dim ds As New DataSet

        Using connection As New OleDbConnection(connectionString)
            Using cmd As New OleDbCommand(query, connection)
                connection.Open()

                Using da As New OleDbDataAdapter(cmd)
                    da.Fill(ds)
                End Using
            End Using
        End Using

        Return ds
    End Function

[Tinnin]

Thanks deceptikon. Reading the query properly now.
My new problem however is that the data in the DataGrid is showing up as gibberish.

This is what I'm using to call the function and put show the data in the DataGrid

        Dim fileToOpen As String
        Dim ds As DataSet()

        fileToOpen = ImportFiles.SelectedItem

        If fileToOpen <> "" Then
            ds = CsvGenerateView(fileToOpen, True)

            Me.DataGridView1.DataSource = ds.Tables(0)
        Else
            MsgBox("Please choose a file to open from the list below!")
        End If

Is my code the problem or is it the csv file?

[deceptikon]

Can you post a sample of the CSV file?

[Tinnin]

I'm just using a simple one to test with:

UserNo   FirstName   Surname   Blah
123456   Joe         Bloggs    1

That's all that's in it.

[Tinnin]

Looks like my files became corrupted.
I used this to change them to csv onLoad so I assume it happened then

        Dim importsFileList As New ArrayList
        Dim sFile As String
        Dim extension As String
        Dim newExtension As String

        sFile = Dir$("C:\Users\filePath" & "\*.*", vbDirectory)
        Do Until sFile = vbNullString
            If sFile <> "." Then ' relative path meaning this directory
                If sFile <> ".." Then ' relative path meaning parent directory 
                    extension = IO.Path.GetExtension(sFile)
                    If extension = ".xls" Or extension = ".xlsx" Then
                        newExtension = IO.Path.ChangeExtension(sFile, ".csv")
                        My.Computer.FileSystem.RenameFile("C:\Users\filePath\" & sFile, newExtension)
                    End If
                End If
            End If
            sFile = Dir$()
        Loop

        sFile = Dir$("C:\filePath" & "\*.*", vbDirectory)
        Do Until sFile = vbNullString
            If sFile <> "." Then ' relative path meaning this directory
                If sFile <> ".." Then ' relative path meaning parent directory 
                    extension = IO.Path.GetExtension(sFile)
                    If extension = ".csv" Then
                        importsFileList.Add(sFile)
                    End If
                End If
            End If
            sFile = Dir$()
        Loop

        For Each importedFile In importsFileList
            ImportFiles.Items.Add(importedFile)
        Next