0

I have a .csv document in Microsoft excel that I import into Drupal that gets data pasted into it from Word. But when I try to import it, I usually get errors due to curly quotes, dashes, hyphens and all the other fancy styles that Word likes to put in its documents and that ends up getting copied over into the.csv file. So what I end up doing is manually find/replace these items one by one until I get no more errors.

I'd like to speed up this manual process so is there a formula I could run in excel that would convert the 'fancy' characters back into their standard ones so that my database could import them properly?

Pretty much I need the following to happen:

"‘" = "'"
"’" = "'"
"”" = '"'
"“" = '"'
"–" = "-"
"—" = "-"
Improve this question
3
  • You could do this with a simple PowerShell script (see stackoverflow.com/questions/60034/… ) or CMD script (superuser.com/questions/1004430/… )
    – DrMoishe Pippik
    Commented Sep 12, 2019 at 14:59
  • I've tried those PowerShell commands, however, it ends up corrupting the output file and I'm unable to open them. I'm just hoping there's a Function that can be created to do all this in one shot.
    – ACanadianCoder
    Commented Sep 12, 2019 at 15:14
  • You need something specific when a single CSV value starts with ‘ (presumably it wouldn't start with ’). In those special cases, XL sees the converted ' as an explicit text identifier and doesn't show it or export it.
    – user385793
    Commented Sep 12, 2019 at 21:04

1 Answer 1

Reset to default
1

A way around manually finding and replacing the characters is recording a macro to do it for you.

  1. Go to file → options → Customize Ribbon
  2. Under main tabs place a marker at “Developer”. Now close the option window.
  3. Click on the developer ribbon and press “record macro”. Now find and replace the characters, then stop recording.

Now a macro has been recorded that you can play again for your next file.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .