none
SQL / CSV path problem

    Question

  • using the code below, i can read a .csv file, but only when the file path contains no spaces.

    can anyone explain how to work around that?

     

    Imports System.Data
    Imports System.Data.OleDb
    Imports ADODB
    
    Public Class Form1
    
      Dim con As New ADODB.Connection
    
      Dim fileName1 As String = "C:\Users\Paul\Desktop\csv.csv" 'works with this 1
      Dim fileName2 As String = "C:\Users\Paul\Desktop\New folder\csv.csv" 'doesn't work with this 1
    
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DBConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & IO.Path.GetDirectoryName(fileName1) & ";Extended Properties=""text;HDR=No;FMT=Delimited(,)"";Persist Security Info=False"
        con.ConnectionString = DBConnection
        con.Open()
    
        Dim SQL As String = "SELECT * FROM " & fileName1
        Dim rs As New ADODB.Recordset
        rs.Open(SQL, con, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        da.Fill(ds, rs, "result")
        MsgBox(ds.Tables("result").Rows(ds.Tables("result").Rows.Count - 1).Item(ds.Tables("result").Rows(ds.Tables("result").Rows.Count - 1).ItemArray.Count - 1))
      End Sub
    
    End Class
    
    

     

    Monday, August 30, 2010 2:29 PM

Answers

  • no error, it just stops executing the code at the rs.open line.

    is this some windows issue? i'm using win7 x64


    Is the Platform option under Configuration Manager set for x86? There is no 64-bit OLEDB Provider for Jet.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 3:48 PM
  • You may wish to check this:

    Jet for Access, Excel and Txt on 64 bit systems

    Or you could just parse the csv file yourself if it's simple.

    --
    Andrew

    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 4:47 PM
  • As long as I have the x86 option selected as the Platform your code works fine for me under 64-bit Windows 7. I am using a Desktop sub folder called "New folder" and a file name called csv.csv in that folder.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 6:09 PM

All replies

  • Have you tried enclosing the path in quotes, thus:

     Dim DBConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & IO.Path.GetDirectoryName(fileName1) & """;Extended Properties=""text;HDR=No;FMT=Delimited(,)"";Persist Security Info=False"

    --
    Andrew

    Monday, August 30, 2010 2:35 PM
  • i tried that + it doesn't work.

    could it be something similar to the way ie urls replace spaces?

    Monday, August 30, 2010 2:40 PM
  • spaces in the filename shouldn't matter. I use the below and it works fine - there is a space in the Directory and in the File:

    filename = "C:\Users\Joe\Desktop\Test Folder\XCel Test.xls"
    strConn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & filename & ";Extended Properties=Excel 8.0;"
    
    Monday, August 30, 2010 3:12 PM
  •  

    Spaces embedded in the path works fine for me. What is the error/exception?

    BTW, you should probably be using the following for special folders:

    Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, August 30, 2010 3:37 PM
  • no error, it just stops executing the code at the rs.open line.

    is this some windows issue? i'm using win7 x64

    Monday, August 30, 2010 3:41 PM
  • If you use the connect string as posted with just the filename it will fail - I think that is a problem with the text provider - doesn't support spaces in the filename.

    Paul - you could copy the file to a temp file w/o spaces in the name.

     

    Monday, August 30, 2010 3:42 PM
  • Why are you using a recordset instead of a dataset?

    This code below is tested.

        Dim file As String = "Test.txt"
        Dim path As String = "C:\Test2\"
        Dim ds As New DataSet
        Try
          If IO.File.Exists(path & file) Then
            Dim ConStr As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
            Using conn As New OleDb.OleDbConnection(ConStr)
              Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
              file, conn)
              da.Fill(ds, "TextFile")
            End Using
          End If
        Catch ex As Exception
          MessageBox.Show(ex.ToString)
        End Try
        DataGridView1.DataSource = ds.Tables(0)
    


    Success
    Cor
    Monday, August 30, 2010 3:44 PM
  • no error, it just stops executing the code at the rs.open line.

    is this some windows issue? i'm using win7 x64


    Is the Platform option under Configuration Manager set for x86? There is no 64-bit OLEDB Provider for Jet.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 3:48 PM
  • yes. as i said here's 2 example filenames:

     

    Dim fileName1 As String = "C:\Users\Paul\Desktop\csv.csv" 'works with this 1
    Dim fileName2 As String = "C:\Users\Paul\Desktop\New folder\csv.csv" 'doesn't work with this 1

    Monday, August 30, 2010 3:50 PM
  • I think you need to put a \ after your datasource, before the Extended Properties:

    "Provider=Microsoft.Jet.OleDb.4.0;" & "Data Source=" & Path.GetDirectoryName(fileName) & "\;Extended Properties=""Text;HDR=Yes;FMT=Delimited""")
    
    Monday, August 30, 2010 3:57 PM
  • i tried that too
    Monday, August 30, 2010 4:01 PM
  • i might try that if i don't get a better answer. thanks
    Monday, August 30, 2010 4:03 PM
  • yes. as i said here's 2 example filenames:

     

    Dim fileName1 As String = "C:\Users\Paul\Desktop\csv.csv" 'works with this 1
    Dim fileName2 As String = "C:\Users\Paul\Desktop\New folder\csv.csv" 'doesn't work with this 1


    Who are you responding to?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, August 30, 2010 4:05 PM
  • i tried that too

    Tried what?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, August 30, 2010 4:10 PM
  • yes. as i said here's 2 example filenames:

     

    Dim fileName1 As String = "C:\Users\Paul\Desktop\csv.csv" 'works with this 1
    Dim fileName2 As String = "C:\Users\Paul\Desktop\New folder\csv.csv" 'doesn't work with this 1


    reply to Paul P Clement IV
    Monday, August 30, 2010 4:18 PM
  • i tried that too

    reply to jwavila
    Monday, August 30, 2010 4:18 PM
  • another thing is you need to only have the filename in your SELECT statement. Since filename1 and filename2 are a complete path, you need to extract just the filename:

    Dim SQL As String = "SELECT * FROM " & Path.GetFileName(fileName1)
    
    Monday, August 30, 2010 4:31 PM
  • another thing is you need to only have the filename in your SELECT statement. Since filename1 and filename2 are a complete path, you need to extract just the filename:

    Dim SQL As String = "SELECT * FROM " & Path.GetFileName(fileName1)
    
    

     

    as i said, the code i posted works with filename1 as it is, but using filename2 with a space in the path doesn't work

    Monday, August 30, 2010 4:41 PM
  • You may wish to check this:

    Jet for Access, Excel and Txt on 64 bit systems

    Or you could just parse the csv file yourself if it's simple.

    --
    Andrew

    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 4:47 PM
  • As long as I have the x86 option selected as the Platform your code works fine for me under 64-bit Windows 7. I am using a Desktop sub folder called "New folder" and a file name called csv.csv in that folder.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Wednesday, September 08, 2010 10:34 AM
    Monday, August 30, 2010 6:09 PM
  • Hello .paul._,

    Thanks for your post.

    What's the situation on your side? If you have any concerns, please feel free to follow up.

    Best regards,
    Liliane
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. Thanks
    Monday, September 06, 2010 6:43 AM