append query and duplicates

[sparky]

I have a two part question. Access 97.

I want to know first if I can append from a Excel file that is a .csv, or do
I have to convert it first to a .xls?

I know how to append from a .xls file.

The .csv file (before conversion to .xls) is downloaded from another
machine. each time the the file is downloaded (every month), it contain the
total data, which will consist of hourly readings. Each download has the
previous month's data, with the present month's data added to end of file.
There is no set limit to number of months, that is, at present, my download
for December 07 contains october and Novermber's data as well as December's,
whereas my November's download had just oct. & Nov's data. I can manually
delete the unecessary data, keeping the current month'd data only. This
takes a little bit of work, and I want to get this setup so this work is not
necessary.

My thought was to take the .csv file, append it straight-away (or after
converting it to .xls) to my table in Access. The Append query needs to be
setup to ignore duplicate info, which will allow only current data to be
appended. I'm just not sure how to do this.

My table has an autonumber id field, time field, date field, and various
"other" fields. I won't be able to use the time field, as each day of each
month will have the same time of readings. The date field is where I think
my criteria needs to be enetered, but I don't know what expression I need to
use to keep the duplicate info from being appended to my table.

Or would it be better to allow the duplicates to be appended along with the
new data, then just run a delete duplicate query? Any help on the setup of
this type would be appreciated.

Rick

 

[Klatuu]

To import a csv file, use the TransferText method instead of the
TransferSpreadsheet method.

It would be a good idea to create an import specification for the import so
you can get the field names and data types like you want them. To do this,
do an import manually (Files, Get External Data, Import). When you get the
import wizard, click on Advanced. In there you can set up the
specifications. Then click on Save As and give the spec a name. You then
use the spec name in the TransferText (see VBA Help for details).

I would agree with importing the csv file into an existing table and using
an append query to move the data to your production table. There are a
couple of ways you can filter the data. You could use a form and have a text
box for the user to enter the month(s) to import and reference the text box
in the append query. Or if you want to get all records, regardless of month,
that are not in the production table, you could use a sub query to filter for
records that are not in the production table. something like:

Where MyDate Field Not IN SELECT MyDateField FROM Production Table

 

[Jerry Whittle]

Hi,

No need to convert to Excel. You can import the CSV file into Access, or you
could link table to that file if the file name stays the same.

To prevent duplicates, create a new index that combines all the fields which
would make the data unique except for the autonumber field. Them make this
multi-field query a unique index. That will prevent duplicates from appending
to the table. However if you already have some dupes in the table, you'll
need to clean them up as Access will not create the unique index if there is
already a problem.