Importing Excel Records into Existing Table

[dianemarie]

Hello, I have someone who wants to import an Excel spreadsheet into an existing Access table. The spreadsheet columns have the exact names as the Access table. There are several phone fields. They are formatted as text with an input mask in Access, but in Excel, they are text and the phone numbers are in different formats. (Ex: (555)555-5555; 555-555-5555, etc.) This seems to be stopping her from being able to successfully import the records. There are hundreds of rows, and several phone fields. Does anyone know how to work around this? Thank you.

 

[jasek78]

I would try importing the excel file into a *new* access database, and then append (copy/cut and paste the records into the original table.

Hope that helps...

Jason

 

[dianemarie]

Thank you Jason. I tried that but Access is still tripping up over the input mask (I think that's the problem!) and will not import OR copy the records into the table. I think I'll try deleting all the input masks? The user has some very involved forms that she prints out as hard copies, so I'm a little nervous about the dominoe effect.

 

[THoey]

You are going to want to have these as a standard format. So, why don't you create a temp table to load the data in with no formats (copy old structure and then remove them). Then, import the data. Then, parse through the fields with the extra characters and remove them. Then copy your data into the existing table.

If you do a Keyword Search in this forum on "parse" or look in the FAQ, you should be able to find some code to help you parse the field.

If you need any more help, let us know... Terry M. Hoey

 

[dianemarie]

Thank you Terry. I'm printing out your instructions and will work through them.