Importing From Excel

[homevestors]

Hello,

I have a table that I would like to update with information from a spreadsheet. The table are all of the property records for the county I live in. The table has a unique data field for every record called a "PIN" (Property Indentifier Number). It is in this format: 12-3456-78-90. Because the PIN's were hand entered, sometimes there is a space between the dashes and the numbers, but each PIN is different for every record.

The data from the spreadsheet I want to import has updated sale information for the property records in my table. I want to associate the PIN's in the spreadsheet to the PIN's in my table and only import the new owners name, sale date, and sale price from the spreadsheet to the table.

How would I do this import? It is probably straight forward, but I am a newbie with Access.

I am concerned that because the PIN from the Speadsheet was typed differently from the one table, the import won't work. The numbers are the same, there are just spaces between some of the numbers and dashes.

Thanks

 

[M8KWR]

the first suggestion would be to get all the PIN number in the same format.

You could use a simple find and replace with excel the erase the spaces with a "-" symbol.

To update a table that you already have in access with an excel file. You can either import or link the file to access.

If the table layout is exactly the same, then you can either open both tables select the entire table from the one and copy and paste it into the other; or do an ammend query.

If the layout is different then an ammend query would be better, but if you can alter the table deisgn to make it the same, then i would do so.

Hope this helps.

 

[homevestors]

Thanks for your help.

Unfortunately, find and replace will not search for a blank space and replace it with a dash "-". Some of the characters in the PIN field from the spread sheet such as "?", and "!" need to be removed as well, but I can't seem to get the find and replace function to do that for me. I would do it by hand, but I have over 100,000 records.

Any suggestions?

 

[M8KWR]

it it only numbers within this field?

If so what i would do is take the value

12-3456-78-90 or 12!3456 78?90 and make them

1234567890. Then if there is two number at the start, then 4 then 2 then 2 at the end you can re-format all of them in 2 procedure.

The 1st would be to remove all symbols

2nd to re-format.

Would this do for you. This is very easy, and i can give you all the code, and explain how to do it step by step, or even better i could write you something to do it for you and post it. If you want me to i do not mind. Post me a txt file or excel etc, and i will transfer it to access, and create the form to alter it all.