Deleting rows from a CSV file, based on date column value.

[Enrique_]

Thanks for your reply.
Sam:
My data set is like this:
Column1,Column2,Column3,Column4,Column5, Column6,Column7
data1,data2,data3,data4,Date,data6,data7
data1,data2,data3,data4,Date,data6,data7
data1,data2,data3,data4,Date,data6,data7
....

LaVolpe:
1. I can read a CSV file into a recordset, and succeeded to filter records using SQL string on the rs, but my last column is an elapsed time like 00:03:30 and it converted to time like 12:03:30(and my sample code places quotes at beginning & end of each row I couldn't determine how to remove).

I use CSV convert to RS for Datareports - no problems.

At the moment I'm testing The CSV to RS W/SQL string as below, but in the below, each line has begin & end quotes, and it alters last Elapsed column/field( from 00:03:30 to 12:03:30). Working on it.

Dim Cn1 As ADODB.Connection 'Simple ADODB RS to CSV file filter demo
Dim rs As New ADODB.Recordset
Dim txt As TextStream
Dim iSQLstring As String

Set Cn1 = New ADODB.Connection
    Cn1.ConnectionString = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "DefaultDir=" & "C:\"
    Cn1.Open

iSQLstring = "SELECT  * FROM Data.csv WHERE Time_On < arDate ORDER BY Elapsed DESC" '-- Has syntax error in FROM clause --
  Set rs = Cn1.Execute(iSQLstring)
    Open "C:\Test.csv" For Output As #1
   While Not rs.EOF
    Print #1, rs.Fields(0) & "," & rs.Fields(1) & "," & rs.Fields(2) & "," & rs.Fields(3) & "," & rs.Fields(4) & "," & rs.Fields(5) & "," & rs.Fields(6)
   

    rs.MoveNext
  Wend
Close 1

So after changing Write #1 to Print #1, my resulting data lines looks like this:
data1,data2,data3,data4,date1,date2,12:03:30

Then I added the WHERE date in the sql that has an error.

[Schmidt]

So the resulting data lines looks like this:
"data1,data2,data3,data4,date1,date2,12:03:30"

But need to be formatted as below:
data1,data2,data3,data4,date1,date2,00:03:30

The ADO-accessible (ODBCbased) TextDriver tries to be too "intelligent"
in this case (interpreting your original Elapsed-Value, depending on the
current System-settings of the OS with regards to DateTime-Values).

Here on a german locale your Elapsed-Column is left intact (at 00:03:30),
because we default to 24h-format with regards to Time-Values.

Though I guess an:
"it will work on a german system ... (usually)"
will not help you much.

And what for example does *not* work on a german system, is the
Order By clause when given directly in the SQL-string.

To achieve a true predictable behaviour (in case you want to work with
SQL-Statements against your Data) - I'd properly import your CSV-Data
"by hand into a real DB-Table first" (not leaving anything to chance with the
ODBC-TextDriver and its interpretation of System-Locale-Settings).

Try to solve that problem first (Import of your CSV-Files into proper
DB-Tables which contain proper Field-Types which are unambiguos,
no matter what System-Locale is present).

Then Select and Filter your Recordsets from these DB-Tables in whatever way
you like best (use them in Reports - or convert them back to CSV, ... in case
that step is needed at all).

As for your Python-Code-Example...
VB6 has only limited CSV-reading-capabilities built-in (using Line-Input), so -
for a decent CSV-Reader you would have to use either external Libraries
(which of course exist for VB6) - or write a proper CSV-Reader yourself.

I'd also like to ask, why you want to write the filtered Results back to a
CSV-File, since this FileType (although "human readable") is really difficult
to handle across different locales, with regards to Unicode-support and
proper DataTypes.

So why not base your "WorkFlow which follows" on a DB-Format instead of *.csv?

Olaf

[Enrique_]

Thanks Olag

I was trying to move this project away from having a MS Access dependency, and I'm very close.

As for my goal, I am collecting data that could be days to weeks old, and in the process partly in my post, The application will automatically delete records Older than XX days.
So each day's record event lines are collected, and once/day records older than XX days would be deleted.

I'm so close now, just stuck on SQL syntax in my last step.

iSQLstring = "SELECT * FROM Data.csv WHERE Time_On < " & arDate & " ORDER BY Elapsed DESC" 'has error

Then retain my elapsed values as 00:03:12 and not convert to time of day. (Format Elapsed, Time, etc.)
My DataRepports had the Elapsed/time format problem, it was corrected by setting the report field data type to Time, then it retained the original data as-is (strangely enough).

[Schmidt]

I was trying to move this project away from having a MS Access dependency, and I'm very close.

First, the term "MS Access" describes an MS-Office-Application, which is just one
of many *consumers* of ADO (and the JET-eninge).

VB6 can make use of ADO (and the JET-Engine, or the ADO-ODBC-engine)
also without an installed "MS Access"-Application.

If your goal is, to decouple also from ADO - and to come up with a DB-Format
which is accessible more universally (e.g. on nearly all devices on this planet, as
well as from any programming-language - e.g. from Python which might run on
a WebServer), then I suggest SQLite instead of CSV-Files.

As for my goal, I am collecting data that could be days to weeks old, ...

Since you write, that it's you who's "collecting the data" - why not
collecting them properly in a real DB-File, already in the first place?

As said, this could be a JET-DBFile (no MS Access needed in this case) -
or in case you want to place your collected Data in a more common format,
accessible on Windows/Linux/Android/... and from all kind of languages,
you could always use SQLite-DBs.

Olaf