If you absolutely must use VB6 (see my comment above - oh and I've just seen your response!) then this method should work for you ... my apologies but I can't test any of this as I no longer have VB6.
Read the entire CSV file into a
RecordSet. This
link[
^] should help you with that. Incidentally that site (which is nothing to do with me) is quite handy for finding code snippets in VB6 (at least until it disappears). I'm reproducing the code here in case the link breaks in the future...
Dim connCSV As New ADODB.Connection
Dim rsTest As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String
path = "C:\Testdir\"
& path & ";Extended Properties='text;HDR=NO;FMT=Delimited'"
connCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& path & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
rsTest.Open "Select * From test.txt", _
connCSV, adOpenStatic, adLockReadOnly, adCmdText
Do While Not rsTest.EOF
MsgBox rsTest(0)
rsTest.movenext
Loop
Note it also shows how you can iterate through the data.
A recordset can be sorted - but there may be issues depending on the content of the data - if you have problems then have a look at the suggestions on this
link[
^]. You can either loop through the dataset applying your selection criteria, try to do something clever with Distinct or try the dictionary approach advocated
here[
^] (This is where my lack of being able to test anything is making this a bit vague - sorry).
I would suggest have a 2nd dataset (same schema) to copy the records you want into which can then be saved or whatever when you are complete e.g.
save to CSV[
^]
Having said all that, if you have access to a database (e.g. MS Access) then it might be worth saving the data into that and using database functions to manipulate the information.
Have a crack at it then come back if you hit any issues
[EDIT - alternative - a suggested method for de-duplicating on the database side]
If you put your current query into a CTE (Common Table Expression) you can do the de-duplication as a subsequent query against that CTE (see
Common Table Expressions(CTE) in SQL SERVER 2008[
^] for a more detailed explanation)
For example:
-- Assume these are passed in
DECLARE @dtmYesterday DATETIME
DECLARE @dtmNow DATETIME
DECLARE @flagid int
-- test data
SET @dtmYesterday = dateadd(dd, datediff(dd, 0, getdate()) - 1, 0)
SET @dtmNow = dateadd(dd, datediff(dd, 0, getdate()), 0)
-- Date's are passed is as midnight (based on what I saw)
-- so set to 06:00 hours
SET @dtmYesterday = dateadd(hh, 6, @dtmYesterday)
SET @dtmNow = dateadd(hh, 6, @dtmNow)
SET @flagid = 5
;WITH CTE AS
(
SELECT
Stamps.Stampnr as Stampnr, Stamps.Time as 'Time', Stamps.amount as 'Amount',
Products.Productname as 'Productname', Products.Articelnumber as 'Articlenumber',
FlagContainer.id as 'FlagId', FlagContainer.FlagId as 'Flag',
Process.prnr as 'CurrentPrNr', Process.numProcesses as 'ProcessNumbers'
FROM
Stamps
INNER JOIN process ON Stamps.prnr = Process.prnr
INNER JOIN Products ON Process.productnr = Products.productnr
INNER JOIN Flagcontainer ON Stamps.ID = Flagcontainer.id
WHERE
Stamps.Time > @dtmYesterday
and Stamps.Time < @dtmNow
and Flagcontainer.flagid = @flagid
)
select CTE.* from CTE
inner join (SELECT FlagId, Productname, MIN([Time]) as mintime FROM CTE GROUP BY FlagId, ProductName) A
ON CTE.FlagId=A.FlagId AND CTE.Productname = A.Productname
AND CTE.[Time] = A.mintime
ORDER BY FlagId, Productname
This might need tweaking for your purposes as I used both FlagContainer.id and Products.Productname to drive query. I assumed that if there were more than one id + Productname pairing then all the rest of the data had to come from the oldest entry - just remove Productname from the second query if you don't need it, or add in other columns if you need them.
Also note the way I've used local (sql) variables - ideally you would put this into a Stored Procedure that accepts those arguments.
Submit an article or tip