Results 1 to 29 of 29

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

    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.
    Last edited by Enrique_; May 30th, 2015 at 10:03 PM.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

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

    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.

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

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

    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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.
    Last edited by Enrique_; May 31st, 2015 at 10:52 AM.

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by Enrique_ View Post
    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

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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).
    Last edited by Enrique_; May 31st, 2015 at 11:34 AM.

  7. #7
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

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

    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?
    Last edited by LaVolpe; May 31st, 2015 at 11:33 AM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by Enrique_ View Post
    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_ View Post
    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

  9. #9
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

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

    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"
    Last edited by LaVolpe; May 31st, 2015 at 11:42 AM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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.
    Last edited by Enrique_; May 31st, 2015 at 01:23 PM.

  11. #11
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Deleting rows from a CSV file, based on date column 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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

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

    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.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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
    Last edited by Enrique_; May 31st, 2015 at 01:34 PM.

  14. #14
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

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

    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
    Last edited by LaVolpe; May 31st, 2015 at 01:35 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    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.
    Attached Files Attached Files
    Last edited by dilettante; May 31st, 2015 at 01:34 PM.

  16. #16
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

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

    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.

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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.

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Jun 2009
    Posts
    64

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

    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?
    Last edited by Enrique_; May 31st, 2015 at 08:27 PM.

  19. #19
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

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

    DateTimeFormat

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

  20. #20
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

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

    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.

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by dilettante View Post
    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 View Post
    It tends to be especially iffy on Windows,
    No, it is definitely not.

    Quote Originally Posted by dilettante View Post
    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 View Post
    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 View Post
    ... 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 View Post
    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

  22. #22
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by dilettante View Post
    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

  23. #23
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by Enrique_ View Post
    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

  24. #24
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    Quote Originally Posted by Schmidt View Post
    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.

  25. #25
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    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.
    Attached Files Attached Files

  26. #26
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    Quote Originally Posted by Schmidt View Post
    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.

  27. #27
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    Quote Originally Posted by Schmidt View Post
    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.
    Last edited by dilettante; Jun 2nd, 2015 at 04:55 AM.

  28. #28
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    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.

    Name:  Victim.jpg
Views: 1922
Size:  16.1 KB
    Last edited by dilettante; Jun 2nd, 2015 at 05:13 AM.

  29. #29
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    400

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

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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width