Problems trying to import a .csv file

Question

HI there

Someone sent me a CSV file to import into an Access database. I've tried dropping it into Excel to overcome the problem which I'm about to describe but to no avail. The text contains data about the person's address. When I import the address which contains carriage returns, the end of each 'line' has a rectangle with a question mark inside, and of course, the carriage return doesn't appear, so all of the text is on a single line. I've tried formatting the field as memo but the problem persists. As there are about 8000 records, I'm a bit reluctant to manually delete the boxes and replace them with carriage returns. I've even tried a search and replace, but as the non printing character isn't in the character set, I can't even cut and paste it into the find and replace dialogue box.

Any help gratefully received.

Dave

Answer 1

You'll need to find out what the ASCII value of that non-printable char is. Here is one way to do that: use DLookup to get the value, then use Mid$ to get a character in that value, and then Asc to get the ASCII value of that character. For example in the Immediate window you could write:

?Asc(Mid$(DLookup("myAddressField", "myTable", "ID=1"),1,1))

This would get the first characters ASCII value. Change it to get the second, third etc char until you get to the non-printable one. My guess is you will get a value of 9, 10, or 13.

Then write an update query to replace that character with CrLf. Something like:

update myTable set myAddressField =Replace(myAddressField, Chr(9), Chr(13) & Chr(10))

(replace the 9 with whatever value you find)

---

-Tom. Microsoft Access MVP

Answer 2

Access uses two characters Chr(13) - carriage return - and Chr(10) - line feed -for a new line.  The two characters must be in that order.

The single character you are seeing is probably either one or the other.  I would try using an update query and replacing Chr(10) with Chr(13) & Chr(10).  If that fails then try replacing Chr(13) with Chr(13) & Chr(10).   If that fails then you will need to identify the character that is represented by a square box.  If you know the position of the character you could try an expression like

    Asc(Mid([AddressField],32,1)) to get the character number.

UPDATE YourTable)

SET [AddressField] = Replace([AddressField],Chr(10), Chr(13) & Chr(10)

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 3

Thanks John

I did what you suggested and ran the update query and it worked first time. It was apparently a Chr(10) that was causing the problem.

Many thanks

Dave

Answer 4

Thanks Tom

It was actually a chr(10) so the update worked a treat :)

Dave