Hi all, First time post, hope someone can help.

I have had to develop a contact database for about 10-15 users that currently have approx 2000 - 3000 contacts each.

I have built an Access DB and imported some of the existing data, but the original format of data that I need to import is Excel and when imported into the Access 97 table, it retains the carriage returns (from Excel) and represents them as a square (In the Access table). I need to replace the carriage return with a comma or a space. Is there any way that I can make a query that will remove or replace a carriage return??

Sample data: OPTILU�2008TU

I have tried to simply search for UniCode Alt+0129, but no good.

I'm not a great user of Access, but how about trying to replace \n? (in MySQL you would use something like UPDATE table SET Field = REPLACE(Field,'\n',','))

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Thanks for the suggestion Andy, I have tried, but unfortunately Access does not like the 'REPLACE' component of the query. It would also seem as though the Access Update statement does not allow anything but a string, like UPDATE table SET Field = "String Only" WHERE Field = "1234" I might just have to delete them manually

Any other ideas would be hugely appreciated

MAD_MICK

I would try the following:

- Create a new field in your table to receive the changed data - a
nice safety idea
- Open New Query in design view
- drag in the table
- drag the field containing "�" to the bottom pane, I'll call it
[CodeField] for now
- drag the [NewField] to the bottom pane
- click the "Query Type" button and select Update Query
- In the [Codefield] Criteria line, put
Like "�"
- In the [Newfield] UpDateTo line, put
"" or " "

Click the big fat exclamation mark which will run the update.

hope that helps ...

Thanks Ilccoo, but unfortunately the query or access does not recognise the character if I copy and paste it or if I use the Uni code (Alt0129). So I can build and run the query but there are 0 results.
Mick

Is this a "one-time" thing or is this something you have to do often?

Could you export the date as tab/pipe delimited and then import it into access that way? I only get the funky boxes when going from Unix to PC.

Hi Mick,

Seeing how you mentioned that you were using Excel to do your list. I figured i ask my gf for advice, she is an expert with Excel and its functions :)
I would think access would have a similar feature like excel has (FIND and REPLACE). I have tested this in Excel 2000. And it works.
Here is what she suggests:

select all rows and columns in the access table you wish to do the replace. I know the you only want to do it on one field, but this will work too. ( I also believe you can do it with just one column, but I don't want the integrity of the data to get screwed up ) Copy the data in the table. Open up Excel, and paste the data into a work sheet. Once you are in Excel, select the character you want to replace and copy that into the clipboard. Then select (highlight) the column that you wish to do the replace in. Press ctrl-f to bring the find and replace and paste the selected character into the find field. Then select the replace option and enter the value you wish to replace it with. This should do a find � and replace , for all values in the column you had selected. Go back to access and copy the table, just the structure. In Excel hightlight all the data w/o the column headers and copy. Then open the new table in Access and select the empty row and paste the data.

I hope the explanation is clear.

Let me know how it goes.

Hi Ilccoo, Thanks for the suggestions, but no good. It might be the versions, Access 97 and Excel 98, but when I copy the data from the Access table to excel the symbol ( � Carriage return) actually becomes a carrige return (Alt Enter) If I run a search for the character, it will not find anything.

This is a one off thing, and I am thinking that I will get each of the users to re-enter all of the data manually anyway. I do not think that the versions that I have are up to the task or contain the functionality that I need, and have applied to the bosses that they be to upgraded to office 2000, but by then the users will have entered their contacts in to the tables correctly through the form.

Thanks for all of the helpful suggestions to all that have contributed, but I will retire from this problem and call it quits. (Making the users do the data entry makes me happy anyway, the problem has been created by them using excel and outlook incorrectly, so they should suffer for their wrong doings )

Mick

Good luck. Wished I could of have helped more.
But hopefully you'll get MS 2000 and you will be all set for future problems.

=)

Spoken like a true Admin.

i second that notation!

I have a solution - I know it's about a year and half late for you, but I noticed this thread has been read over 3000 times, so I figured I'd post a solution anyway. I had the same problem this morning.

The way to remove those pesky characters in excel is to do the following:

With your data in excel, add a column to the right of your data and populate the rows with a string that won't appear in your real data (I use very foul swear words for this).

Save your spreadsheet as a text file (CSV or Tab delineated).

Open this text file in word, and use the search/replace function replace ^p with a space (or whatever)

Then search and replace your unique string (or foul swear word) with ^p

Save your text file and close it.

Open it in excel and behold the wonders

This did not work for me, when I returned to the excel sheet all the columns had been jumbled up.

However I managed to find another solution with one line of VBA:

Worksheets("Sheet1").Columns("I").Replace What:=vbCrLf, Replacement:="?"

Should be self explanatory, the vbCrLf is the VB code for a carriage return and just replace the ? with whatever you want in place of the carriage return.

Brian

Morning,

I doubt that the originator of this thread is losing sleep over it these days, but I've found a solution that works for me and Office 2007:-

Copy the cells into Word. Within Word, within the "Find and Replace" dialogue select the "Special" menu & select "Manual Line Break".

Worked for me and I think should work with any Word which has a "Special" menu within its Find function.

Sam.

to correct this and remove carridge returns in Access you can use the following.

Replace([data],Chr(10),"")

CHR(10) = LF
CHR(13) = CR

you might need to replace both.

you can put that command in an update query update to field.


** EDIT: This is how to do it in Access 2003, not positive if it will work in previous version but worth a try.

Hi all,

Another first time poster. I read all the above and I'm just not getting it. I have a similar situation, but in my case I want to preserve the carriage returns and they are not moving cleanly between the apps. They are within a string of text and the text needs to be preserved. I import a spreadsheet from Excel to Access (running in 2007 but db & sheet are still in 2000 format). When I import into Access the carriage returns aren't coming through properly and appear as squares. If I manually edit them (Shift-F2, Ctrl-Enter) to make a proper carriage return they "magically" turn into a proper carriage return, only AFTER I add my own carriage return.

Seems rather stupid that this character cannot properly import/export between Excel and Access. Note that when exporting to Excel from Access, the same thing happens - proper carriage returns in Access turn into squares in Excel.

Any help would be greatly appreciated!

I'd like to do one of two things:

1. (preferred) do a straight replace on this funky code, not sure what to use though (trouble searching for it)
2. (if not possible) replace the carriage return in Excel with a placeholder, which I then convert into a proper carriage return in Access.

I want to do this with a query, don't understand VBA enough to do it this way. It looks like the Replace function will do the trick, but I'm just not finding enough explanation about the pieces to understand it.

Found: Replace («stringexpr», «find», «replace», «start», «count», «compare»)

So... preference 1 - Replace (string expression, Chr(13) & Chr(10), Chr(13) & Chr(10))
Or... preference 2 - Replace (string expression, "|HRT|", Chr(13) & Chr(10))

I just don't get string expression - is that supposed to be the table name, field name, type of string? Looks like it's type of string, but don't understand how it relates? Sorry, really a newbie here. Doesn't seem like I would need start, count or compare for this replace, not sure though.

Search problem: I can't even figure out how to create the proper criteria for searching for Chr(13) & Chr(10) within a larger text string. Trying in a select query '* Chr(13) & Chr(10) *' is bad syntax and '* & Chr(13) & Chr(10) & *' turns into ' "*" & Chr(13) & Chr(10) & "*" '.

More info:

Table name = Upload to T_ECCN Master
Field name = Notes
Field type = Memo
Search for, either 1 = assuming Chr(13) & Chr(10), but since it's not displaying properly not sure
or 2 = my placeholder - "|HRT|"
Replace with = Chr(13) & Chr(10), with leading and trailing text remaining intact

I do not know if this works on a memo field, so it may not.

<string expression> is the field that you are searching.

Do one character replacement at a time with the special characters. Once you get it working that way then experiment with getting them working together.


so an example would be REPLACE (Notes, char(13), char(13))

you do not need the start, end or count like you thought.

The character that is being imported may not be a CR/LF you might want to take and copy/paste the field value into Word and look at the text with the paragraph marks on, this is how I found out what the characters I needed to remove where.

Hope this helps to clear things up.