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

I'm struggling with what seemed like a simple task in the beginning.
I need to delete rows of a CSV file based on the Date column, like Delete any Date > Reference value.
Or write any row with Date < reference value to a new CSV file with column names.

Example data structure: data1,data2,data3,data4,Date,data6,data7.

I'm doing a very similar task already to create recordsets for datareports.

So my weakness is getting the Selected recordset converted back to CSV file form.

Any help greatly appreciated.

Little difficultly here trying to understand your issue.
First, what is the format of your CSV file? Are there Carriage Returns (and or Line Feeds) at the end of each 'Line', or is it one continuous string.
Where in the 'line' is the date located? Beginning, end, anywhere within?

I believe what you are asking is to find some line in the CSV, and then delete that line if the date in the line matches something from your Recordset...is THAT correct?

A SAMPLE CSV file would be really helpful here.

Are you able to read the csv into a recordset?
Do you know how to export the recordset to CSV?

You can 'delete' the records a few ways:
1) When opening the csv, use a WHERE clause to exclude the unwanted records & re-save the csv
2) Open the csv to include all records, then filter the recordset to exclude the unwanted records. Save the filtered recordset
3) Make the recordset connection-less, loop thru & delete the unwanted records then save to csv

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.

Code:

---

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.

Quote:

---

Originally Posted by Enrique_

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

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.

Code:

---

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

Tip: To ensure your fields are not converted to some "best guess" data type, use a schema.ini file. Whether it fixes your time values or not, you'll have to test it

Don't think I'm mistaken here, but Access does not need to be installed on a system to use a mdb file via ADO. Maybe your desire to move away from Access is due to a false impression?

Quote:

---

Originally Posted by Enrique_

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.

Quote:

---

Originally Posted by Enrique_

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

Quote:

---

Originally Posted by Enrique

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

---

If Time_On is a date/time data type field, try including the # signs
iSQLstring = "SELECT * FROM Data.csv WHERE Time_On < #" & arDate & "# ORDER BY Elapsed DESC"

I totally agree this will be better using an Access database. Going back after this learning experiment.
I have used Access tables with VB5/6 applications since 1997.

LaVolpe, we think alike.
This is my now working SQL string:

Code:

---

arDate = "05/11/2015" ' example
iSQLstring = "SELECT * FROM Data.csv WHERE ((Time_On) > #" & arDate & "#);"

---

(Lifted from my old VB6/Access db project)

Now I just need to fix the Elapsed format issue, to 00:03:30 from 12:03:30.

Final code now (with tainted Elapse time value) is:

Code:

---

Dim rs As New ADODB.Recordset
Dim txt As TextStream
Dim iSQLstring As String
arDate = "05/11/2015"

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 & "#);"
  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

---

rs.Fields(6) is the Elapsed value.

Quote:

---

Originally Posted by Enrique

Now I just need to fix the Elapsed format issue, to 00:03:30 from 12:03:30.

---

See my previous post regarding schema.ini files. IMHO, it is the only sure solution to preventing ADO from best guessing field data types when using csv format

Could you also not create your own .mdb file with an empty table (with code), making field(6) a text Field, and then import the CSV file into that table? Haven't tried it, but it would SEEM to be the same as using a 'real' database table.
Just guessing.

LaVolpe

I have created the Schema.ini file as described in the MSDN article, how does it look?

[Data.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Data1 Text Width 4
Col2=Data2 Text Width 4
Col3=Data3 Text Width 15
Col4=Data4 Text Width 10
Col5=Time_On DateTime Width 21
Col6=Time_Off DateTime Width 21
Col7=Elapsed Text Width 8
CharacterSet=ANSI

Does this look correct?
I'm getting a blank result file, using the Schema.ini

The width parameters are not necessary for character delimited files -- but mandatory for fixed width fields, where fields are not delimited. I'd generally not add that parameter should you want to change one or more fields to be variable text length. Did it do the trick?

Edited: Didn't see your last question: Yes, schema files are used for input & if multiple csv files exist in the folder, you can append their definitions to the same schema file ... only one schema file per folder

I also avoid using MaxScanRows if I am defining each field in the csv -- no point in ADO pre-reading the file

The schema.ini file format does a lot of nice things, but one thing it does not support is time formatting. You can specify either date or date and time formats, but not time alone.

So then you have to punt, and take over formatting yourself using a Jet SQL Expression:

Code:

---

Private Const CONN_STRING As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$DIR$';" _
  & "Extended Properties='Text'"

:
:

Private Function FilterAsNewData(ByVal DataSourceDir As String) As Long
    'Filter for rows where "Time_On" is <= 30 days ago.
    '
    'Returns output record count.
    Dim Connection As ADODB.Connection
    Dim Command As ADODB.Command

    On Error Resume Next
    Kill DataSourceDir & "\New Data.csv"
    On Error GoTo 0

    Set Connection = New ADODB.Connection
    Connection.Open Replace$(CONN_STRING, "$DIR$", DataSourceDir)
    Set Command = New ADODB.Command
    With Command
        .ActiveConnection = Connection
        .CommandType = adCmdText
        'Since we want more control over the export format of the Elapsed field
        'we must define it in schema.ini as a Char (text, i.e. String) field and
        'format it exactly as we want.
        '
        'While we can specify formats for dates or for dates and times together
        'in schema.ini there is no way to specify just a time format, so:
        .CommandText = "SELECT Description,Amount,[Time_On]," _
                    & "Format$(Elapsed,'Hh:Nn:Ss') AS Elapsed " _
                    & "INTO [New Data.csv] FROM [Data.csv] " _
                    & "WHERE [Time_On] <= ? ORDER BY Elapsed DESC"
        .Execute FilterAsNewData, DateAdd("d", -30, Date), adExecuteNoRecords
    End With
    Connection.Close
End Function

---

The schema.ini to match this looks like:

Code:

---

[Data.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
TextDelimiter=none
DecimalSymbol=.
Col1=Description Char Width 50
Col2=Amount Single
;Use a specific date format here:
Col3=Time_On DateTime(MM-DD-YYYY)
;Since we created this using a generic 24-hour time
;format we don't need to get specific (we can't
;anyway because we can't specify just a time format
;here):
Col4=Elapsed DateTime

[New Data.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
TextDelimiter=none
DecimalSymbol=.
Col1=Description Char Width 50
Col2=Amount Single
;Use a specific date format here:
Col3=Time_On DateTime(MM-DD-YYYY)
;Define as Char because we want fine control over the
;format and we'll do that via SQL:
Col4=Elapsed Char

---

Note the use of a parameter query above. That not only gets around potential SQL Injection but it also gets away from problems that often stem from invalid date formats (just as in VB6, Jet SQL Date typed literals are always formatted for the INVARIANT_LOCALE, i.e. dates are MM/DD/YYYY).


I'd also advise avoiding SQLite like the plague.

It tends to be especially iffy on Windows, where data access libraries for it often embed old buggy versions statically compiled in so they can't be updated. Some bungle character encoding, doing things as silly as UTF-16LE to UTF-8 on write but returning UTF-8 on read. Others have really poor performance overall and mangle BLOB data unpredictably.

If you are going to use it look for a commercial SQLite OLEDB Provider. If you can't do that because of cost, then look for a working ODBC Driver and use that... but TEST, TEST, TEST. You may have to try several.

Plus the upshot is that it doesn't come with Windows anyway, so it becomes one more nasty to deploy.

I am sure the last post is the best so far, but another method could be open the csv file, 'convert' the "XX:XX:XX" ELAPSED TIME to a total number of seconds. For example, 01:03:30 could be converted (and replaced by) a string 'value' of "3210" (if my math is correct).
These values could be appended to each line (or replace the last 8 characters (less the CRLF)) and THEN opened up in a RS. If appended, fields(7) would be total number of elapsed seconds which could be compared to a similar conversion on the other end.

SummarY:
Read the csv in line by line.
Append each line with the elapsed seconds
Write new csv with these 8 'fields'
Open new csv into recordset
Do comparison.

Sam
That's an easy fix, and easy to store, in previous stages of my app in DateDiff calculations. My older DB Table version SQL-calculated Elapsed time on the fly based on seconds.

dilettante
That is an amazing example, Thank you.
Can I also use my existing type of stored Time/dat format like: 5/11/2015 7:01:17 PM?
My timed events may only be a few minutes/seconds long.

Maybe I deserve a bit of punishment for testing text/CSV files for simple data sets, in place of DB tables.
But I was proud to have all of my small project working from CSV files, as I get CSV files from outside systems to process.

I feel bad for taking so much of everyone's time on this matter.
I could never have imagined that deleting a few lines from a CSV file would be so complicated. Db tables have always made these processes simple.

Sam?

I'm testing the use of Seconds stored in place of a pre-calculated Elapsed time like 00:12:20 as you suggested.

Then doing a hh:mm:ss format during the SQL recordset derivation to send to the DataReport.

Using:

Code:

---

iSQLStr = "Format(Int(Elapsed)/3600),""00"") & "":"" & Format((Int(Elapsed)/60)-Int(Elapsed)/3600)*60),""00"") & "":"" & Format((Elapsed) Mod 60),""00"") AS Duration SELECT TOP 25 * FROM Data.csv ORDER BY Duration DESC"
 
  Set rs = Cn1.Execute(iSQLStr)
 
  Set DataReport1.DataSource = rs
 
    DataReport1.Show vbModal

---

My report gives a Duration Field not found error. There is no error with the rs.field used directly.

I have used this method in the past with DB tables, not CSV file, should that matter?

If you define a field as DateTime and haven't provided a format, the Jet Text IISAM will assume current settings:

Quote:

---

DateTimeFormat

In the absence of a format string, the Windows Control Panel short date picture and time options are used.

---

If you APPENDED the number of elapsed seconds to each line of the CSV and then inserted the data from the csv into your recordset, then the last field(probably (7) in your case) should be numeric field.
Then, in order to do your comparison, you use whatever you are comparing to that number in that field of your recordset.

Not sure what that last SQL was intended to do.

Quote:

---

Originally Posted by dilettante

I'd also advise avoiding SQLite like the plague.

---

Which is really bad advice (apparently given by someone who doesn't use it,
because any SQLite-using developer I know of, will disagree with you on that).

Quote:

---

Originally Posted by dilettante

It tends to be especially iffy on Windows,

---

No, it is definitely not.

Quote:

---

Originally Posted by dilettante

where data access libraries for it often embed old buggy versions statically compiled in so they can't be updated.
Some bungle character encoding, doing things as silly as UTF-16LE to UTF-8 on write but returning UTF-8 on read. Others have really poor performance overall and mangle BLOB data unpredictably.

---

Such old wrappers should be specified by you here exactly, so that people can avoid these
"old wrapper versions".
So which ones did you test and based your opinions on?

For VB6 there's two current (free and well-maintained) SQLite-wrappers, which show none
of the alleged issues you mentioned above.
The free ODBC-Driver from Christian Werner:
http://www.ch-werner.de/sqliteodbc/

And the download-wise less voluminous VB6-based wrapper (faster - and with more directly
accessible "native SQLite-functionality") which comes with the vbRichClient5-Framework:
http://www.vbRichClient.com

Quote:

---

Originally Posted by dilettante

If you are going to use it look for a commercial SQLite OLEDB Provider.

---

Bad advice again, since both of the above mentioned, free SQLite-wrappers are
out there for more than 10 years, hardened over time and actively maintained.

Quote:

---

Originally Posted by dilettante

... but TEST, TEST, TEST. You may have to try several.

---

Given the context of SQLite, that's a quite "FUD-ish" statement, because any
SQLite-version has to pass one of the largest Test-Suites on this planet
(Millions of Tests) before becoming a new release. Please inform yourself on:
https://www.sqlite.org/testing.html
before posting such misleading statements.

That huge test-coverage is one of the reasons for its popularity in all kind of languages
(along with, that it runs on any device and supports its DB-Format unchanged across all these devices).

Quote:

---

Originally Posted by dilettante

Plus the upshot is that it doesn't come with Windows anyway, so it becomes one more nasty to deploy.

---

The above will be a false statement already next month, because Win10 will officially
ship with SQLite from then on (MS has finally seen "the Light" I guess).

Olaf

Quote:

---

Originally Posted by dilettante

If you define a field as DateTime and haven't provided a format, the Jet Text IISAM will assume current settings:

---

That's not true - from my tests, the given Format-Strings are pointless on a non-US locale
(at least the Date-Format-Strings).
E.g. on a german Win8 system, your posted code is falling over, hard.

Meaning, that the Format-Strings you gave in your Schema.ini aren't working (are
apparently ignored) on the given system-locale here (where Days come before the Month)

Here's some reduced Test-Input for Data.csv (only 10 lines)

Code:

---

Noobly,2543.63,08-07-2014,17:44:57
Woobly,1054.16,04-10-2015,22:00:56
Goobly,3414.07,08-21-2014,13:24:56
Doobly,4952.60,07-04-2014,07:43:01
Foobly,4670.57,09-01-2014,11:09:09
Roobly,1459.00,05-26-2015,18:11:24
Yoobly,1637.91,03-03-2015,04:07:05
Toobly,2112.26,01-29-2015,17:47:48
Boobly,1454.97,05-23-2015,23:32:43
Soobly,4659.18,06-01-2014,04:38:42

---

Running your "Leave only records, older than 30 days from now"-Filter,
I get (in New Data.csv) the following results:

Code:

---

Toobly,2112.26,01-29-2015,17:47:48
Noobly,2543.63,08-07-2014,17:44:57
Goobly,3414.07,08-21-2014,13:24:56
Foobly,4670.57,09-01-2014,11:09:09
Doobly,4952.60,07-04-2014,07:43:01
Soobly,4659.18,06-01-2014,04:38:42
Yoobly,1637.91,03-03-2015,04:07:05

---

But the "Woobly-record" (second line in the original Data.csv) should be contained in the
result as well (because the 10th of April 2015 is definitely older than 30 days from now).

When I switch my system to the US-locale - then run your App again, the correct
result is generated (not having changed anything, Schema.ini + your Code all original).

So, even that Schema.ini based approach (which is quite convoluted for my taste)
doesn't really help to ensure predictable behaviour of the MS-CSV-TextDrivers which
come with ADO-JET (or ADO-ODBC) across different system-locales.

So, I guess (in case the OP is planning to use his solution not only in the US),
that we're back to square one.

The only reliable solution being: "parsing by hand" (transferring the Data, correctly
converted from Strings - into properly typed DB-Table-Columns - using a real DB-Engine).

Olaf

Quote:

---

Originally Posted by Enrique_

My report gives a Duration Field not found error. There is no error with the rs.field used directly.

I have used this method in the past with DB tables, not CSV file, should that matter?

---

The CSV-TextDrivers for ADO behave "funny" (Schema.ini or not) - so it can very well be,
that the same query (on properly typed JET-Date-Columns of a real JET-DB-Table)
behaves differently.

Seriously, I don't know why you're trying to handle your Data in CSV-Files (especially
since you're planning to use complex queries and filters - as well as Sorts on them).

Parsing a CSV by hand (and importing the records into properly typed DB-Table-Columns)
is not rocket-science, and could look this way (using SQLite as the target-DB).

The following CSV-content would need to be placed in a File in: App.Path & "\Data\Data.csv"

Code:

---

Noobly,2543.63,08-07-2014,17:44:57
Woobly,1054.16,04-10-2015,22:00:56
Goobly,3414.07,08-21-2014,13:24:56
Doobly,4952.60,07-04-2014,07:43:01
Foobly,4670.57,09-01-2014,11:09:09
Roobly,1459.00,05-26-2015,18:11:24
Yoobly,1637.91,03-03-2015,04:07:05
Toobly,2112.26,01-29-2015,17:47:48
Boobly,1454.97,05-23-2015,23:32:43
Soobly,4659.18,06-01-2014,04:38:42

---

Then, using the above CSV-File as the Input-Source, the following CSV-to-SQLite-importing will work,
based on the vbRichClient5-based SQLite-wrapper:

Code:

---

Option Explicit

Implements ICSVCallback
Private Cnn As cConnection, Cmd As cCommand, Csv As cCSV 'Connection-, Command- and Csv-Parser Defs

Private Sub Form_Load()
  TypedImport_CSV2SQLite App.Path & "\Data\Data.csv", App.Path & "\Data\DBImport.db3"
End Sub

Sub TypedImport_CSV2SQLite(CSVFileName As String, DBFileName As String)
  If New_c.FSO.FileExists(DBFileName) Then New_c.FSO.DeleteFile DBFileName 'delete a potentially existing DB-File
 
  Set Cnn = New_c.Connection(DBFileName, DBCreateNewFileDB) 'create a new DB-File (as well as Table T below)
      Cnn.Execute "Create Table T(Description Text, Amount Double, Time_On Date, Elapsed Time)"
  Set Cmd = Cnn.CreateCommand("Insert Into T Values(?,?,?,?)") 'open a CmdObj on T for the Inserts in the CSV-Callback
 
  Set Csv = New_c.Csv 'create a new Parser-Instance
      Csv.ParseFile CSVFileName, Me 'parse the File (triggering the CallBack below)
End Sub

Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
Dim S As String
    S = Csv.GetStringValue(B, BValStartPos, BValLen)
    Select Case ColNr + 1
      Case 1: Cmd.SetText 1, S
      Case 2: Cmd.SetDouble 2, Val(S)
      Case 3: Cmd.SetDate 3, DateSerial(Mid$(S, 7, 4), Mid$(S, 1, 2), Mid$(S, 4, 2))
      Case 4: Cmd.SetTime 4, CDate(S)
              Cmd.Execute '<- after the last Column-Value was set in the line above, we trigger the DB-Insert before the next Text-Row starts
    End Select
End Function

---

An example for ADO/JET importing would look quite similar...
And in case you prefer an example for that, just say so and I'll provide one -
but SQLite is truly a great choice, in case your mentioned plan of:
"Switching away from Access" was meant in the wider sense of "switching away from JET".

Olaf

Quote:

---

Originally Posted by Schmidt

That's not true - from my tests, the given Format-Strings are pointless on a non-US locale
(at least the Date-Format-Strings).
E.g. on a german Win8 system, your posted code is falling over, hard.

Meaning, that the Format-Strings you gave in your Schema.ini aren't working (are
apparently ignored) on the given system-locale here (where Days come before the Month)

---

Things work exactly as I said, i.e. when not specified the current settings are used. These "settings" are the UI settings for things like date/time display formats. So yes if you rely on the defaults and the defaults are different from the format used in the file you get incorrect results.

Ideally for dates you would use something less ambiguous like YYYYY-MM-DD but of course this still needs to be known by anyone who needs to consume your CSV data.


I have tried specifying all three formats: MM-DD-YYYY, DD-MM-YYYY, and YYYY-MM-DD in the schema.ini file and all of them work as long as they match the format used to write out the original data.

So if anything is "falling over" on a German Win8 system I sure can't account for it.

I finally tracked the problem down, and it wasn't within Jet or the Text IISAM at all. The problem appears to occur in ADO, in particular in parameters passed to the Command.Execute method.

It looks like these are converted to String values and then coerced back to strongly-typed values behind the scenes. In a deviant locale all kinds of things can occur, from Date mangling to decimal point disasters in numeric values.

The workaround for running with deviant locale settings is to avoid that implicit coercion. We can do that here by using the Command.Parameters collection, through which we can pass strongly-typed values. In this case the required change is:

Code:

---

        .CommandText = "SELECT Description,Amount,[Time_On]," _
                    & "Format$(Elapsed,'Hh:Nn:Ss') AS Elapsed " _
                    & "INTO [New Data.csv] FROM [Data.csv] " _
                    & "WHERE [Time_On] <= ? ORDER BY Elapsed DESC"
        .Parameters.Append .CreateParameter(, adDate, , , DateAdd("d", -30, Date))
        .Execute FilterAsNewData, , adExecuteNoRecords

---

That was all it took.


In addition, since I was using an Amount field of type Single I had to create a universal form of the Format$() function to ensure that decimal points were properly created as "." characters as well. This issue arises during the creation of the original file and is unrelated to ADO, Jet, or schema.ini contents at all since the program just writes the original as a text file.

I'm also writing an extra row at the end now with a [Time_On] value of 30 days ago. That makes it a little easier to verify proper operation when inspecting the output file.

Thanks for bringing this to my attention. It is a very subtle but important point to be aware of, one that can burn you and be hard to track down. This also led me to correct the value of the LOCALE_INVARIANT constant in some other programs.

Quote:

---

Originally Posted by Schmidt

So, even that Schema.ini based approach (which is quite convoluted for my taste)
doesn't really help to ensure predictable behaviour of the MS-CSV-TextDrivers which
come with ADO-JET (or ADO-ODBC) across different system-locales.

---

My example program does not use any ODBC Text Driver, it uses Jet's Text IISAM, not the same thing but they share some things in common such as the used of schema.ini files.

"Convoluted" sounds like "I don't know how to use them." This is fairly common. Mostly because Microsoft has done a rather poor job of documenting them, and partly because some things are determined by registry settings and there isn't any schema.ini syntax to override them. One of the painful examples of the latter is the ExportCurrencySymbols value (Boolean) which exists in the registry but has no effect in a schema.ini file because it has no meaning there.

Aside from a few things like that though schema.ini files work perfectly well and can be quite powerful.

Quote:

---

Originally Posted by Schmidt

For VB6 there's two current (free and well-maintained) SQLite-wrappers, which show none
of the alleged issues you mentioned above.
The free ODBC-Driver from Christian Werner:
http://www.ch-werner.de/sqliteodbc/

And the download-wise less voluminous VB6-based wrapper (faster - and with more directly
accessible "native SQLite-functionality") which comes with the vbRichClient5-Framework:
http://www.vbRichClient.com

---

You missed another one: OLE DB Provider for SQLite databases, which at least uses SQLite 3.8.4.3 instead of some of the earlier and buggier versions.

All of these have problems. I'm pretty sure people have had trouble with yours returning UTF-8 String field values in threads here recently. But perhaps that was a usage error... the thread got long and full of lengthy reply posts and I probably got too bored and wandered away. ;)

Cherry City's SQLite OLE DB Provider is the only one I'd even consider recommending today.


In any case there isn't any portability with SQLite because of its poor data type system. Even something as simple as a Boolean value can get implemented 12 different ways by 12 different wrappers.

Datatypes In SQLite Version 3

Avoid SQLite on Windows like the plague.

BTW:

SQLite does not ship in any version of Windows.

Watch the video A Developer's Guide to Windows 10: (10) SQLite Local Database where they clearly say you have to go get SQLite, then go get a wrapper (for .Net Store Apps), then install both and deploy them as part of your Store Apps.

Again, plague.

Attachment 127135

@dilettante,
Please start thinking with your brain again... to stop saying crap :rolleyes:

http://www.sqlite.org/famous.html
http://www.sqlite.org/mostdeployed.html