Solved Importing Excel to Access - getting 'amp;' after ampersands

Microsoft Office 2010 professional
July 11, 2013 at 07:12:43
Specs: Windows XP
This is so annoying! I moved a batch of 440+ accounts yesterday from a spreadsheet into my database and every name that had the & symbol (Jack & Jill Plumbers) showed up with & instead of just the & (Jack & Jill Plumbers). I understand it's stating it's an ampersand so the html for the ampersand, maybe? Any idea how to make it quit though? I had to tab through the accounts and remove them manually when I noticed them in Access and would like to not do that again. Sometimes the batches I move will be 1500+. That's a lot of tabbing and backspacing!

See More: Importing Excel to Access - getting amp; after ampersands

Report •


✔ Best Answer
July 15, 2013 at 13:16:08
Look here and see if it is of any assistance:

http://support.microsoft.com/kb/261320

MIKE

http://www.skeptic.com/



#1
July 11, 2013 at 09:32:07
I don't use Access, so I have no idea if this will work, but I'll toss it out there anyway.

In Excel, do a "Global Replace".

Replace every occurrence of & with a string that you know doesn't exist in your data, such as 1xx1.

Then, after you import the data into Access, reverse the process by Replacing 1xx1 with & - assuming that Access has that feature.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 14, 2013 at 19:37:47
"Global Replace," or "Find and replace." Are they the same thing? I can do a Find and Replace (Ctrl + F, select Replace) and get the same result. Select "Replace all," or "Find Next," depending on whether you want every instance of the symbol in question replaced.
Sorry to butt in, I had this problem a while back and thought I'd help!!

Report •

#3
July 14, 2013 at 19:59:22
Yes, I am suggesting to do a Find and Replace, Replace All in Excel.

Assuming the same feature is available in Access, this process should work.

We haven't heard from the OP in a while so I'm not sure she's listening anymore.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 15, 2013 at 05:15:47
Out of office over the weekend, guys, sorry.

But I wasn't asking how to replace the extra symbols but why are they there and how can I get rid of them. Whether it's something I have set in Excel or Access or if it's something internal in the applications...well, I have no idea! Just hoping someone who's worked with both might be able to shine some light on the subject.


Report •

#5
July 15, 2013 at 06:18:36
re: "But I wasn't asking how to replace the extra symbols"

Either you are misunderstanding my suggestion or I am misunderstanding you.

Here's what I meant in more detail:

You have &'s in the original Excel data. These &'s are not importing properly into Access, therefore it might help to temporarily replace the &'s in Excel so the import feature in Access doesn't have to deal with them.

1 - In Excel use Find...Replace - Replace All to replace the &'s with 1xx1 or some other string that you are 100% sure doesn't show up anywhere else in the data. In Excel you would now have: "Jack 1xx1 Jill Plumbers"

2 - Do your "move" into Access, where you should still see "Jack 1xx1 Jill Plumbers".

3 - Finally, assuming Access has a Find...Replace - Replace All feature similar to Excel, Replace All 1xx1 with & to get back to "Jack & Jill Plumbers".

Again, I don't use Access, so I don't know if this will work. I'm just tossing it out there as a suggestion. There are not a lot of Access users in this forum, so you might need to try a forum dedicated to Access for a better solution.

Good Luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 15, 2013 at 06:48:02
I understood your suggestion but I'd still end up in Access with unnecessary characters to replace. Whether it's your 1xx1 or the already occurring 'amp;' it is still an extra step. Find and replace is fine but not in this situation.

If an Access user knows of a way to stop the error from happening, that's what I need to know.


Report •

#7
July 15, 2013 at 07:15:52
Not to belabor the issue, but in your OP you said that you needed to "tab through the accounts and remove them manually".

Does that mean that you can not use Replace All in Access to remove them all at once? That's a curiosity question on my part.

re: "If an Access user knows of a way to stop the error from happening, that's what I need to know."

As I said in my previous response, you may have to try a different forum because I don't see a lot of Access questions answered here. I'm not trying to send you away, I'm simply saying that there aren't a lot of Access experts hanging around this forum. As Moderator I see all of the threads in this forum and Access questions don't get many answers. You can try this forum which is still at computing.net or you can DAGS for other Access forums:

http://www.computing.net/forum/dbas...

BTW, you said "Whether it's your 1xx1 or the already occurring 'amp;' it is still an extra step. "

Then: "If an Access user knows of a way to stop the error from happening"

Wouldn't implementing that solution also be an "extra step"? ;-) Kidding! (sort of)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
July 15, 2013 at 07:35:09
I do not use Access, but have used similar programs in the past.

In Access doesn't the Ampersand symbol have a special meaning?
Could that be causing your problems?

Shouldn't you be "escaping" it in some manner to turn off it's special
characteristics and making it a plain ampersand?

Sometimes a backslash character is used to "escape" the special character,
like "\&" or perhaps a double symbol, like "&&"?

MIKE

http://www.skeptic.com/


Report •

#9
July 15, 2013 at 07:52:35
Oh, sorry, this is the Office 2010 forum, so since Excel and Access are both from that package....but I'll check the database forum next.

And....
Then: "If an Access user knows of a way to stop the error from happening"

Wouldn't implementing that solution also be an "extra step"? ;-) Kidding! (sort of)

Nope. Implementing a solution will be stopping an error before it happens because I really think this is a setting or rule or some-such-thing in either Access or Excel. Hopefully someone will reply who knows what to do!

Thanks anyway, DerbyDad.


Report •

#10
July 15, 2013 at 08:01:17
MMcconaghy:

Thank you! I knew someone would have seen something similar somewhere! The fact that it was giving me what looked like html made me believe it had to be a setting of some type or a command or a......well, something I wasn't experienced enough to know.

I'm not working with the spreadsheets today but I'll try the backslash tomorrow and see what happens. I'll let you know!

Thanks again, D-dad and McConaghy. I appreciate your time and advice!


Report •

#11
July 15, 2013 at 08:10:16
re: "this is the Office 2010 forum"

Actually, it's just Office, not any specific version.

re: "so since Excel and Access are both from that package"

I know exactly what you are saying. Yes, Access is part of the Office suite, but since we are all volunteers, we just answer the questions on the applications we are familiar with.

For the most part we have some folks that know Excel, a couple that know Outlook, and the occasional Word question gets answered. By far, the largest percentage of Q&A's are related to Excel.

I suggested the Database forum because, as Mike pointed out above, an & might be a special character, not only in Access but in other database application as well. Hopefully you can find a solution there. Good luck!

re: Nope. Implementing a solution will be stopping an error before it happens

Yeah, but the initial implementation is an "extra step" that must be taken, at least once. (I'm just messing around by being a language cop.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
July 15, 2013 at 08:32:55
re: Nope. Implementing a solution will be stopping an error before it happens

Yeah, but the initial implementation is an "extra step" that must be taken, at least once. (I'm just messing around by being a language cop.)

Alright, language cop, but your solution is still an extra step; step one is to add the 1xx1 to Excel and step two is to remove it from Access. If Mike's solution with the backslash works, that's a single step, one only, uno, hito, un, 1, roman numeral I, unus, ana, ein, een, etc! ;-) (Yes, you'll notice I found a website with numbers in many languages to make my point, LOL. Can't resist an argument.)

Also, even if I mistakenly stated this was the Office 2010 forum instead of the plain Office forum, it, *ahem, would still have included the two applications to which I refer. So <insert raspberry here>! ;-)


Report •

#13
July 15, 2013 at 09:01:19
Also, even if I mistakenly stated this was the Office 2010 forum instead of the plain Office forum, it, *ahem, would still have included the two applications to which I refer

I believe I addressed that issue. I even agreed with you when I said "I know exactly what you are saying" and then explained the reality regarding what types of questions receive the most responses here.

Undeserved rasberry deleted!

<insert raspberry here>!


Report •

#14
July 15, 2013 at 10:09:36
LOL! And you even refrained from commenting on my misuse of the italics button. Thank you!

Report •

#15
July 15, 2013 at 13:16:08
✔ Best Answer
Look here and see if it is of any assistance:

http://support.microsoft.com/kb/261320

MIKE

http://www.skeptic.com/


Report •

Ask Question