Discussion on:

Normalizing the data with Table Analyzer

The tip to save excel data as a text file is a good one - I have used it many times.
One other advantage to this is to save an Import Specification (click on the Advanced... button to get to that screen). If the import will be done more than once this will save a lot of setting up, and the process can be automated using a macro or VBA code.

For a quick shortcut to set up for a one of import - insert a new first data row between your headings and the real data. Put the correct sort of data in that row, for example use a decimal with lots of decimal places, or create a text entry at least 300 characters long if you want a memo field to be created.

1) When using csv formats without headings, make sure that the last column has data in it, even if it is only a space. If the last column is empty, the Excel "Save as" process may drop the column and therefore the last comma will also be dropped.

To make matters worse, Excel bases its decision on the column data format on the first few rows. Therefore, if the last column is empty, and those first few rows on the next column are also empty, the comma will be dropped on those rows as well.

Thus the idea is to force Excel to make the correct decision. The idea of putting a dummy row at the top with correct data is a perfect solution.

2) When importing csv data which includes text, be wary of descriptive columns which include commas in the text. If necessary, use a different delimiter.

The article states that "[normalizing the imported data] can be extremely time-consuming and difficult. Or you can work with the unnormalized data".

For those of us who don't like either of these options, there are user-friendly tools like DataDefractor (http://www.datadefractor.com) which both extract data from Excel spreadsheets and normalize it at the same time.

I want to connect a particular field of an existing Access table with an Excel column....
That Access table field will get updated as soon as the Excel entry will be done...
Can anyone tell me the process?

I want to connect a particular field of an existing Access table with an Excel column....
That Access table field will get updated as soon as the Excel entry will be done...
Can anyone tell me the process?

One of the main issues of uploading to Access relates to validation, particlary if you are importing thousands of rows and row number 995 happens to fail.

I found will validate the data for you using transaction / rollback.

Just a matter of selecting the destination table, choosing the fields, setting any default values / mandatory columns and sending the data.

I likes the fact I could use Excel formula for default values which is nice, and UPDATE is also possible using the custom SQL feature - looks like the next version will make UPATE easier.

http://leansoftware.net/en-us/productsdownloads/exceltoaccess.aspx

I managed to validate and upload tens of thousands of rows - for free!