Database Error WHERE Clause

[ken gray]

I'm trying to include a WHERE clause into this query but it gives me the error below.  It works if I remove the WHERE clause.  Note: it also fails if I try to include any of the column names (file contents below) in the select clause. 

Can anyone shed some light on this here?  I'm just trying to learn how to query a text file like a database.


Macro Macro6

Code:

Copy      Help

Database dbt.Open(dbt.CsText("C:\Temp\"))
ARRAY(str) at
dbt.QueryArr("SELECT * FROM move.txt where rep=10" at)
out at

Error (RT) in <open ":3116: /64">Macro6:  0x80040E10,
    [Microsoft][ODBC Text Driver] Too few parameters. Expected 1.    <help #IDP_ERR>?

Date    Rep    Total
7/19/2022 7:48:34 AM    10    600
7/19/2022 8:18:56 AM    10    600
7/19/2022 8:39:38 AM    10    600

 

[Gintaras]

Must be CSV, ie comma-separated. If it is tab-separated, let file extension be tab.

Note: this macro replaces text in file "C:\Temp\move.tab".

Macro Macro3129

Code:

Copy      Help

out
;tab-separated columns
str s =
;Date    Rep    Total
;7/19/2022 7:48:34 AM    10    600
;7/19/2022 8:18:56 AM    10    600
;7/19/2022 8:39:38 AM    20    600
s.setfile("C:\Temp\move.tab")

Database dbt.Open(dbt.CsText("C:\Temp"))
ARRAY(str) at
dbt.QueryArr("SELECT * FROM move.txt WHERE rep=10" at)
int i
for i 0 at.len
,out F"Date={at[0 i]}, Rep={at[1 i]}, Total={at[2 i]}"

[ken gray]

Thanks.
What about the SQL language it uses?  I've tried T-Sql and Sqlite for casting a string as a datetime but can't seem to make it work.
Any thoughts on what I'm doing wrong here?

Macro Macro7

Code:

Copy      Help

out
str s =
;Date,Rep,Total
;7/19/2022 7:48:34 AM,10,600
;7/19/2022 8:18:56 AM,10,600
;7/19/2022 8:39:38 AM,20,600
s.setfile("C:\Temp\move2.csv")

Database dbt.Open(dbt.CsText("C:\Temp"))
ARRAY(str) at
dbt.QueryArr("SELECT cast(date as date) FROM move2.csv where rep=20" at)
out  at

[Gintaras]

Class Database uses ADO API. It works with many databases, and each can have own SQL dialect. I don't know what SQL is used for CSV files.

https://www.google.com/search?q=ADO+CSV+SQL+dialect

CsText uses this code to create the connection string:

Member function Database.CsText

Code:

Copy      Help

str s.format("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=%s;Extensions=asc,csv,tab,txt;" _s.expandpath(folder))
s+moreParams

[ken gray]

oooo....not a fun exploration there.  hehehehe.
Will keep trying.
thanks.