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.
