SQL Conditional SELECT from CSV

Question

I'm trying to load data from a CSV file using an SQL query SELECT * FROM ["+fileName+"];. It works as expected when I select all data with no condition, however, I would like to omit any records containing the string ".BAK" in the first column. Does anyone have any insight into how I could achieve this?

I've attempted variations of the WHERE NOT LIKE '%.BAK%' condition, but I can't get them to work as expected.

Thanks

have you entered the correct column 'WHERE col1 NOT LIKE '%.BAK%'? can you post the full query? — egreene
How do you connect to your Text file? Do you have HDR=YES or you don't have any name for your columns? — Steve
SELECT * FROM ["+fileName+"]; is the full working query. Then i tried to tack on WHERE column(1) NOT LIKE '%.BAK%' WHERE (1) NOT LIKE '%.BAK%' WHERE [1] NOT LIKE '%.BAK%' and substituted 0 in place of 1 as well. I don't have a column name in the CSV so my reference to the first column by number may be the issue. — Ax1sMundi
@Steve I connect to the CSV file via OleDb objects- using the sql query I fill an adapter I don't have column names and the first row is not the header so i have HDR=NO in the connection string — Ax1sMundi

Steve Steve · Accepted Answer · 2014-12-23T21:37:48

If your CSV file contains, in the first line, the column headers (for example)

firstHeaderName,secondHeaderName,thirdHeaderName, ......

then the query to execute is simply

string cmdText = "SELECT * FROM [" + filename + "] WHERE firstHeaderName NOT LIKE '%.bak%'";

Things are similar if you don't have a line with the column headers in the first line of your CSV.
In this case the connectionstring is something like this

string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;
                  Data Source=d:\temp\;
                  Extended Properties='text;HDR=No;FMT=Delimited'";

and your command text should refer to the first column using the conventional name F1

string cmdText = "SELECT * FROM [" + filename + "] WHERE F1 NOT LIKE '%.bak%'";

The convention is to name the columns with F1 (the first column), F2 (the second column) and so on.

SQL Conditional SELECT from CSV

1 Answers