Append / Update from .csv files (2000)

Topic

New Reply

I am looking for a method of appending / updating records to a table from a series of .csv files

The .csv files are created by another system and are stored in a folder ( lets say f:csvdata ) in the format ddmmyyyyhhmmss.cvs dd=day, mm=month, yyyy=year, hh=hours, mm=minutes, ss=seconds eg. 29032006154925.csv

The contents of the file are date ( in mm/dd/yyyy format ), poduct code, quantity

eg. 03/29/2006, abc123, 100
03/29/2006, xyz987, 20
etc

I need to look in this folder and process any files that exist for todays date (processing the earliest file first).

Data is appended to a table. If a record already exists for the date / productid processed then the vale should be updated to the new entry.

If the update / append requires two routines – append then update that is fine, but the part I am not sure about is the selection and processing in order of the text files.

Anyone able to guide me?

Thanks in advance

John

Reply | Quote

Replies

It would be much easier if the file names were in the format yyyymmddhhmmss.csv – the Dir function would automatically return then in the correct order.

With the names you have, you must store the names in an array, extract a date/time value from the name into a second array, (or in a second column of the original array), then apply a bubble sort or something like that to sort the names in date/time order.

Reply | Quote

He could write the names to a table and use the Order By in a recordset to process them in order.

Reply | Quote

Yes, good idea.

Reply | Quote