Importing select lines from a CSV 1

[bujin]

Hi

I have a file of CSV data which I want to import into an Access table. The table contains the following fields for each trainee record:

NI number, Surname, Forenames, Employer, TLO*, Date Finished, Day Count.

*TLO = Trainee Liaison Officer
The Date Finished is the date that the trainee finished a course.
The Day Count is: Current Date - Date Due End + 10000

(Date Due End is the predicted date that the trainee will finish the course. The reason 10000 is added to it is because the program which outputs the CSV data doesn't like negative numbers for some reason!)

The problem is that my database table uses the NI number as a key field, yet trainees may have done more than one course in the past two years (the range of the data), and so may have more than one record output. I want to just import the most recent data.

Below is an example of the CSV:

"JW988873B","Johnston","Desi Stephen","Burlington Toiletries","GW","01/11/00",10275
"JW988873B","Johnston","Desi Stephen","Burlington Toiletries","GW","",9728

It has the same NI, name, employer and TLO. The top one has a Date Finished, and a Day Count of 10275 (Due End was 275 days ago). The bottom one does not have a Date Finished, and the Day Count is 9728 (Due End is in 272 days). This last one is the most recent. So, I want to delete the top one and just import the last one.

Trainees may have more than two records too, occasionally (and I don't know why it does this), the same record is output twice - again, I only want to import one of them.

And just to complicate things further, when I import the data, I only want to import the following fields:

NI, Surname, Forenames, Employer, TLO

If I've managed to hold your attention this long, well done! If anyone can be of assistance, I would be grateful!

Thanks

 

[JimHorton]

Two ways. Easiest is to import the whole table to a temp table, then use DAO to do your logic on what actuall is kept and what is discarded.

Second way is to use the Open the textfile, and use Input or LineInput statements/functions to read lines into a variable where you can parse them before actually reading them in. Here though, you'd have to parse line-by-line then field by field, writing to the appropriate fields of the destination recordset. The first option is definitely the easier from a coding standpoint.
--Jim

 

[bujin]

Thanks, Jim. I've been spending all morning looking at the second option you gave. I was thinking along the lines of concatenating the NI and DayCount fields, then sorting on this field in ascending order, taking just the top one for each NI. Unfortunately, I simply couldn't work out how to sort the data!!!

I'll have a look at your first option - it does sound a heck of a lot easier!

 

[MichaelRed]

While I would normally agree that the import to a temp table is the better (easier) approach, there are situations where the code soloution is desireable or necessary. One such situation is when the import will recur -frequently or with large recordsets- In this case, the storage space consumed may be intolerable, particularly if the app is a 'production' oriented process which may be used by individuals who can not (or will not) take the time to compact the db after these operations. For these situations, there are techniques to import the CSV file into a multidimensionsal array, which may be used to accomplish the sorting and filtering prior to actually appending the 'new' rrecords to the db.

One routine to accomplish the import of the CSV file to the multidimensional array - along with a routine to test it is shown below:

PLease note the fName argument for the routine needs to be the FULLY QUALIFIED PATH to the CSV file. See the comment in the TEST routine.

Public Function basCSV2Array(fName As String, _
                             Optional RecSep As String = vbCrLf, _
                             Optional FldSep As String = ",") As Variant

    Dim Fil As Integer
    Dim NRecs As Long
    Dim NFlds As Long
    Dim Idx As Long
    Dim J As Long                               'indices
    Dim RawFile As String                       'Holds the entire contents of the file
    Dim RawSplit() As String                    'the file split up on a line per line basis
    Dim OneLine() As String                     'used to hold the tokens for one line
    Dim RptAry() As String                      '2d array. Holds X lines & N elements per line

    Fil = FreeFile                              'get the next free file number

    'This works for large files. I (Troy Williams)tried it
    'with a 50 meg file on a computer with 128 Mb of ram and it worked fine.
    'open the file and dump the contents into the rawfile variable

    Open fName For Binary As #Fil               'Open file
    RawFile = String$(LOF(Fil), 32)             'Create "empty" String of Length
    Get #Fil, 1, RawFile                        'Fill "Empty Str with File
    Close #Fil                                  'Close File

    'Get the Nunber of Records and Fields
    RawSplit = Split(RawFile, RecSep)           'Split the file up by lines
    NRecs = UBound(RawSplit) - 1                'Num Lines/Records in the file

    OneLine = Split(RawSplit(0), FldSep)        'Split the first line
    NFlds = UBound(OneLine)                     'Get the Number of Fields

    ReDim RptAry(0 To NRecs, 0 To NFlds)

    For Idx = 0 To NRecs
       OneLine = Split(RawSplit(Idx), FldSep)
       For Jdx = 0 To NFlds
          RptAry(Idx, Jdx) = OneLine(Jdx)
       Next Jdx
    Next Idx

    basCSV2Array = RptAry

End Function

'________________________________________________________

Public Function basTstCSV2Array()

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim Ldx As Long
    Dim FilNme As String
    Dim MyArray As Variant

'   Change the File path & Name Below to YOUR CSV file
    
    FilNme = "C:\My Documents\MyCsvFile.Txt"

    MyArray = basCSV2Array(FilNme)
    
    Idx = UBound(MyArray, 1)
    Jdx = UBound(MyArray, 2)

    For Kdx = 0 To Idx
        For Ldx = 0 To Jdx
            Debug.Print MyArray(Kdx, Ldx);
        Next Ldx
        Debug.Print
    Next Kdx

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[bujin]

Thanks for that MichaelRed. The files I'm working with are not that big - around 2-3000 records or so (a "50Mb file on a 128Mb computer" is mentioned - I'm currently working on 135Kb files on a 32Mb machine, but I'm hoping to get an upgrade soon!).

My database has locks on it so that one person acts as the administrator and can access all areas of the application including the importing of trainee data, and everyone else who uses it only has read access. This way, the administration of the database will only be done by one trained person, who at the moment is me. But you have given me an idea - I'll need to write the code to compact the data file (all data is held in a separate MDB file to the application).

Cheers

 

[bujin]

I've written the code to import the data now. It's odd - what it does is more complicated than the code that was there in the first place, but takes up less lines of code! Seems to work, but I've had to include a crude bit of code to pause the execution (a loop 10000 times), otherwise the program tries to delete the temporary table before it's finished with it! Anyway, it may be crude, but it works!

Thanks for your help.

Right - onto database compacting...