您如何查询文本文件作为ole db源(WHERE子句的语法)?

问题描述

大家好，
背景:
所以这是我的问题.我想从csv文件中获取数据，并使用.net 3.5框架中的vb.net 2010通过OLE将其填充到程序中的Datagridview中.听起来很简单，对吧?
好吧，我认为该文本文件也有不需要的数据，我想将其过滤掉，一个简单的where子句应该起作用.当我不添加WHERE子句时，一切正常(文本文件顺利进入datagridview)，但是当我尝试运行WHERE子句时(简单子句WHERE Date =``2011-04-11'')通过OLE连接在csv文件上显示错误.

问题:
1-有人知道我尝试做的事是否可能吗?
2-在查询文本文件的ole SQL字符串中添加WHERE子句的正确语法是什么?

Private Function EditingConnectionString(ByVal file As String)
        EditingConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= " & Filepath_Parse(file, 0, "\") & ";" & _
            "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""

    End Function

private sub gettextdata
      '' Retrieve the text data
        Dim objConnection As OleDbConnection
        objConnection = New OleDbConnection(EditingConnectionString(txtFilePath.Text))


        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As New DataSet
        Try
            objConnection.Open()
            objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\"), objConnection) ''
''---- The above line is where i want to add my WHERE syntax.  Already tried WHERE Date > ''4/11/2011'' , WHERE Date > "4/11/2011", WHERE Date > #4/11/2011# ----            
Filepath_Parse(OpenFileDialog1.FileName, 1, "\"))
            objAdapter.Fill(objDataSet, "Insitu")
            objAdapter.Dispose()
            DataGridView1.DataSource = objDataSet
            DataGridView1.DataMember = "Insitu"
            objConnection.Close()
            objConnection.Dispose()

        Catch ex As Exception
            MsgBox("Did You use the correct Column names? " & vbCrLf & ex.Message.ToString)
        End Try
    End Sub

解决方案

Hey Guys,
Background:
So here is my issue. I want to take data from a csv file and populate it into a Datagridview in my program via OLE using vb.net 2010 in the .net 3.5 framework. Sounds easy, right?
Well, The text file I have also has unwanted data I want to filter out, a simple where clause should work, i thought. When I don''t add the WHERE clause, it all works fine (the text file goes into the datagridview smoothly) but when i try to run the WHERE clause (Simple clause WHERE Date = ''2011-04-11'' )on csv file through the OLE connection, I get errors.

Questions:
1 - does anyone know if what i am trying to do is possible?
2 - What is the proper syntax for adding a WHERE clause to an ole SQL string that querys a text file?

Private Function EditingConnectionString(ByVal file As String)
        EditingConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= " & Filepath_Parse(file, 0, "\") & ";" & _
            "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""

    End Function

private sub gettextdata
      '' Retrieve the text data
        Dim objConnection As OleDbConnection
        objConnection = New OleDbConnection(EditingConnectionString(txtFilePath.Text))


        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As New DataSet
        Try
            objConnection.Open()
            objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\"), objConnection) ''
''---- The above line is where i want to add my WHERE syntax.  Already tried WHERE Date > ''4/11/2011'' , WHERE Date > "4/11/2011", WHERE Date > #4/11/2011# ----            
Filepath_Parse(OpenFileDialog1.FileName, 1, "\"))
            objAdapter.Fill(objDataSet, "Insitu")
            objAdapter.Dispose()
            DataGridView1.DataSource = objDataSet
            DataGridView1.DataMember = "Insitu"
            objConnection.Close()
            objConnection.Dispose()

        Catch ex As Exception
            MsgBox("Did You use the correct Column names? " & vbCrLf & ex.Message.ToString)
        End Try
    End Sub

解决方案

这篇关于您如何查询文本文件作为ole db源(WHERE子句的语法)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！