MS Access: Table to receive CSV file and record row number as appears in CSV

  • Hello. I have CSV files from a coin counter:

    • The counter continually appends to the one CSV file for the day.
    • Each time coins are counted for one person a row is appended to the CSV data.
    • The counter does not add any unique key data to the CSV - just the totals for each type of coin.
    • I want to read the data into MS Access in parallel so the counters can confirm and see any mistakes.
    • I want to use DoCmd.TransferText to import the CSV data into a table and preserve the row order as appears in the CSV file.
      • Note: the rows are not labelled in the CSV - they just appear with return characters
    • If I have the row order I can then identify the rows (counts) that I have already processed and process the new ones.
      • E.g. Import the CSV file now and see rows 1,2 & 3 - I know I have processed 1 & 2 so I will process 3.
    • I want to key on row order and not the totals as it is possible for the same person to collect the same amount each day for example.
    • I can import the CSV file fine - my question is how do I set up a table field that matches row number on import?
      • I cannot use AutoNumber as I want row 1 in the CSV file to always be row 1 and on a reimport AutoNumber always increments
    • To be clear my pseudo code is:
      • I have TCounterCSV where I load in the counter CSV file.
      • I have TCounterCSVRowsProcessed where I store the rows processed in the current days file
      • Both tables start empty
      • I import the CSV data into TCounterCSV and (with your help) record in one field/column somehow the row number as it appears in the CSV
      • I deal with each row as I have processed none at the moment
      • I then store the row number of each row processed in TCounterCSVRowsProcessed
      • After 5 seconds, I then clear TCounterCSV and reload in the counter CSV file.
      • I can then compare row numbers in the two tables to find the rows I have not processed.
      • Repeat

    Any help would be greatly appreciated. Many thanks.