How do you query a text file as a ole db source (syntax for a WHERE clause)?

Question

4.50/5 (2 votes)

See more:

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

Posted 29-Apr-11 12:36pm

reseburg

Updated 2-May-11 7:09am

v5

Add a Solution

Comments

HimanshuJoshi 29-Apr-11 19:41pm

Edited to add pre tags around code.

R. Hoffmann 29-Apr-11 19:45pm

Can you post the errors you're getting?

It's certainly possible to have a WHERE clause in the SELECT being used as the adapter's SelectCommand (see http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.aspx, the Examples section).

Perhaps the dates are not in the correct format, and using placeholders and providing your dates as DateTime objects (thereby allowing the system to take care of the format) would solve the issue.

OriginalGriff 30-Apr-11 2:21am

It would help to have the following:
Error message if any, and the line it is complaining on (if any)
Header row from your CSV file
A few sample rows from your CSV file.

reseburg 2-May-11 13:12pm

Hey guys,
thanks for the responses.

I tried using a datetime object for my WHERE clause, I still got the error.

This is the extent of the error message i am getting:
"Syntax Error in WHERE Clause"

Below are a few lines from my csv data file

Sample_Date,Depth_ft,H2OTemp
12/10/2010 11:00,1.718,11.483
12/10/2010 11:15,1.69,11.511
12/10/2010 11:30,1.658,11.539
12/10/2010 11:45,1.631,11.592
12/10/2010 12:00,1.663,11.645
12/10/2010 12:15,1.721,11.661

Umair Feroze 2-May-11 13:49pm

I believe by replacing the following line:

objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\"), objConnection)

with:

objAdapter = New OleDbDataAdapter("SELECT Sample_Date, Depth_ft, H2OTemp FROM " & Filepath_Parse(OpenFileDialog1.FileName, 1, "\") & " WHERE Sample_Date = '2011-01-01'", objConnection)

will resolve your issue.

If not, please verify the selectcommand property of objAdapter and paste here

4 solutions

Solution 2

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.

Posted 2-May-11 8:56am

Eddy Vluggen

Solution 3

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

0) Create an object that holds the data:

C#

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).

Posted 2-May-11 9:01am

#realJSOP

Updated 2-May-11 9:02am

v2

Solution 5

Sergey's comment was the key - Try this syntax also WHERE [Sample_Date] > #01/25/2011#
The syntax i was looking for was the "#" sign. thanks everyone for your help.

Posted 2-May-11 10:38am

reseburg

Comments

Sergey Chepurin 4-May-11 17:42pm

I double-checked it and the correct syntax is: WHERE Month(Sample_Date) = 4 And Sample_Date > #04/21/2011#
- outputs dates only in April after 21-th.
The previous example will output data after certain date in every month.
Sergey Chepurin.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Chepurin · Accepted Answer · 2011-05-02T09:50:00

Solution 4

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

Posted 2-May-11 9:50am

Sergey Chepurin