您如何查询文本文件作为ole db源(WHERE子句的语法)? [英] How do you query a text file as a ole db source (syntax for a WHERE clause)?

问题描述

大家好，
背景:
所以这是我的问题.我想从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

解决方案

WHERE Date > 

不起作用，因为日期"不代表您的列名之一.

WHERE Sample_Date > 

可能有效.

添加模式 [ public class SampleItem { 公共 DateTime SampleDate { get ; 设置； } 公共 十进制 DepthFeet { get ; 设置； } public 十进制 WaterTemp { get ; 设置； } 公共 SampleItem() { SampleDate = 新 DateTime( 0 ); DepthFeet = 0M； WaterTemp = 0M； } 公用 SampleItem(日期时间日期，十进制深度，十进制 temp) { SampleDate =日期； DepthFeet =深度； WaterTemp =温度； } 公共 SampleItem(字符串数据) { 字符串 []个部分=数据.Split(' ，' )； SampleDate = DateTime.Parse(parts [ 0 ]); DepthFeet = 十进制 .Parse(parts [ 1 ]); WaterTemp = 十进制 .Parse(parts [ 2 ]); } }

1)一次读取一行文本文件，并使用字符串
实例化一个新的SampleItem
您可能还需要采取一些预防措施，以确保数据在构造函数中也同样有效.

也请尝试使用此语法 WHERE [Sample_Date] > #01/25/2011# .它适用于Access DB.
谢尔盖·吉普林(Sergey Chepurin).

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

解决方案

WHERE Date > 

wouldn''t work, as "Date" doesn''t represent one of your column-names.

WHERE Sample_Date > 

might work.

Adding a schema[^] might help too.

The way I''d do it is *without* any of that sql stuff...

0) Create an object that holds the data:

public class SampleItem
{
    public DateTime SampleDate { get; set; }
    public decimal DepthFeet { get; set; }
    public decimal WaterTemp { get; set; }
    public SampleItem() 
    {
        SampleDate = new DateTime(0);
        DepthFeet  = 0M;
        WaterTemp  = 0M;
    }
    public SampleItem(dateTime date, decimal depth, decimal temp) 
    {
        SampleDate = date;
        DepthFeet  = depth;
        WaterTemp  = temp;
    }
    public SampleItem(string data) 
    {
        string[] parts = data.Split(',');
        SampleDate = DateTime.Parse(parts[0]);
        DepthFeet  = decimal.Parse(parts[1]);
        WaterTemp  = decimal.Parse(parts[2]);
    }
}

1) Read the text file one line at a time, and instaniate a new SampleItem with the string

You might also want to take some precautions to ensure that the data is otherwise valid as well (in the constructors).

Try this syntax also WHERE [Sample_Date] > #01/25/2011# . It works and specific for Access DB.
Sergey Chepurin.

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