Best way to input data (when data receved by email in .csv format)

[Ally1205]

Hello, I would really appreciate some advice on ways to add customer data to my Access 2000 database, in cases where data has been sent to me in .csv format. The person who emails me the data is using some database that has different fields to those in my Access database. My Access database contains many fields that are not included in the data I receive in .csv format. However, most of the fields in the .csv records received, contain data pertinent to fields in my own database, e.g., "first name", "last name", "Date Joined", "address line 1", etc.

Until now, I have been inputting the new data by opening the .csv file in MS Works Spreadsheet, which I then print out. I then manually type the data into to my Access database. This is awkward, because there are a lot of fields to print and copy, etc.

Just recently, I concluded that there is a more efficient way of doing it: I could reconfigure my Access "members" table, so that the first 20 fields correspond to the 20 fields in the .csv records I receive by email. This way, I can copy each member record (ie., row) from the MS Works table, and paste it into my Access table. Is this the best solution? Problems might occur if the sender of the .csv records starts adding new fields to his database or changing the order of the fields without warning.

Many thanks..
A

[Dal Jeanis]

A CSV spreadsheet should be easy to import into Access. You shouldn't have to reconfigure your existing database at all.

1) Use MS Excel to open the CSV file.

2) Save the file as an excel workbook (.XLS or .XLSX) with a consistent name, for instance temp_import_file.XLS.

3) Back up your access database, and do all the following steps in a junk/play copy until you are sure it all works.

4) Open your Access database. In the external data group, click Excel. Browse to the desired file, and import it as a new table. Give it a consistent name that you choose - for example, temp_import_table. Click through the steps, and make sure that the imported table gets the field names that you want for each field. When you finish the import, it will ask you if you want to save the steps you just did. Answer yes, so you can run it automatically next time.

5) This next is the hard part, but you only have to figure it out once. You'll need to code a VBA module (don't panic) to copy the records (I said don't panic) from the temp table into the new table.

6) You can build a form with a button to run the VBA module, or you can just run it in the immediate window, depending on how geeky you'd like to feel when you do it.

So, does that sound too scary, or are you up for it? We can walk you through the setup steps for 5 and 6.

Here's the basic syntax for that kind of an SQL

INSERT INTO MyTableName ([Field1Name], Field2Name], ... for however many fields you have... [FieldXName]) 
    SELECT [TempField1Name], TempField2Name], ... for however many fields you have... [TempFieldXName]
    FROM [TempTableName];

And here's an example for three fields from tblOldCustomers to tblCustomers

INSERT INTO tblCustomers ([CustomerID], [Last Name], [First Name]) 
    SELECT [CustomerID], [Last Name], [First Name] 
     FROM tblOldCustomers;

Whadda-ya-say?

[Ally1205]

Dal,
Thank you very much for the suggested solution. It looks a bit more complicated than I was hoping, so I think I'll have a look at this when I have a good couple of hours to spare - and a fully focused brain... It might be in a day or two.
A

[Ally1205]

5) This next is the hard part, but you only have to figure it out once. You'll need to code a VBA module (don't panic) to copy the records (I said don't panic) from the temp table into the new table.
Whadda-ya-say?

I started to attempt this today. The first uncertainty I came up against is re coding the VBA module. This aspect of MS Access is a level above what I'm familiar with. In my database window, I clicked on "modules", then "new" and I get a code-inputting window as seen below:

As seen above, the line "Option compare database" is already inserted. Do I leave that line in place and then start typing my code after that, or should I delete "option compare database"? I did google for 'option compare database' but the explanation seemed abstruse enough to induce a soporific mental fog, whereas you (or someone) can probably answer this in a few seconds. Also: what should I do regarding the "general" and "declarations" drop-down options?

Thank you..