Solved

Remove CRLf in Access Table fields

Posted on 2014-01-04
14
1,102 Views
Last Modified: 2014-01-06
I have an Access database with a table that has CRLf characters in several columns; 4 to be exact. These are text fields ( I think limited to 200 characters). I need to export this table to a tab delimited text file that I can use to import into a MySQL database.

In working with an EE Expert, it was suggested that I use this Access coding to remove these; this is an example in ONE of the 4 columns (ServiceComments1): Replace(Replace(Nz([ServiceComments1],""),Chr(10)," "),Chr(13)," ")

I did this & the CRFf's are still in the middle of some of the instances, same as before with NO Replace.

Also, prior to doing the Replace, I did a search, Find of chr(10), Chr(13), Vbcr and vblf on the columns; none found in ANY case.

I might be able to edit this out in the resultant text file bu it is tedious & requires it be manually done for over 100 records in total.

Is there a way I can get this done in Access BEFORE I export the table?

Thanks
0
Comment
Question by:Richard Korts
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39756424
Can you upload DB with some sample records?
0
 
LVL 6

Expert Comment

by:r3nder
ID: 39756429
string s = string.Empty;
s = replace(s, vbcrlf, " ") 'if it's a CR/LF pair
or

 s = replace (s, chr(10), " " 'if it's just a Line Feed
 or

 s = replace (s, chr(13), " " 'if it's just a Carraige Return
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39756575
If you post a database with just the table in it holding a couple of records that create multiple records on export, I will examine the table with a hex editor to see what character is actually causing the problem.  Make sure to obscure anything sensitive.

What exactly is the output format you need?  Do you have any flexibility?  A csv file with quoted text fields eliminates the problem of the extraneous crlf's.
0
 

Author Comment

by:Richard Korts
ID: 39756591
I did a search (in Access) for vbcrlf; no match.

Previous search shows no match for chr(10) or chr(13).

See attached.

I will try to build a small single table Access database of JUST some of the problem records. It will be a struggle for me because the problem records (obviously) are NOT contiguous.

I am trying to produce a TAB delimited txt file (readable by Excel). I choose this format because data often contains commas, thus csv doesn't work.

Thanks
cant-find-vbcrlf.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39756613
As I told you in the other thread, csv DOES work if you select the option to surround the text fields with quotes.  The quotes handle embedded commas but do not solve the problem of CrLf which users frequently put in their text.  There are other characters which could be causing the problem which is why I would like more than one example.
0
 

Author Comment

by:Richard Korts
ID: 39756802
Attached is an excel export of a small portion of the table. The fields that have the CRLF problem within the field are ServiceComments 1, ServiceComments2, Comments1 & Comments2.

In this small sample the only ones that have this issue are the ones with Key 10034, 10048, 10059 & 10082. I do NOT know which of the 4 fields has the problem, but in those 4 records, at least ONE does.

I could not figure put how to build a query & then export to Access; it says you can, but it didn't work.

If you have to have Access, tell my how to export a SMALL portion of a large table into Access. It might be that exporting it to Excel fixes the problem; if it does, I can work with that too.

Thanks
sample1.xlsx
0
 
LVL 6

Expert Comment

by:r3nder
ID: 39756877
can you make me an empty acess db with a table and the fields - Thanks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:PatHartman
ID: 39757000
There is no problem with the Excel Export.  No additional records were created.

Create a make table query.

SELECT yourtable.*  INTO tempTable
FROM yourtable
WHERE Key = 10034 OR Key = 10048 OR Key = 10059 OR Key = 10082;

Then create a new empty database and import only tempTable.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39757013
I don't see any errors in your logic. Look at sample with imported Excel table (Sheet1).
QueryFind show all records with CrLF (4 records, as expected in ServiceComment1 - 2 records and in Comment1 - also 2). QueryUpd replace CrLF with space. Look at sample
DBCRLF.accdb
0
 

Author Comment

by:Richard Korts
ID: 39759704
To als315,

I looked at your query design; I accidentally ran your update query, then I ran the find, it finds nothing, which I THINK is good.

You query design has a row called Update To: Mine does not. How do I get that row inserted into design view?

Thanks
0
 
LVL 39

Expert Comment

by:als315
ID: 39759985
You should change type of query to update query and manually add "update to" string
update query
0
 

Author Comment

by:Richard Korts
ID: 39760049
To als315,

The Update Query actually changes the content of the table, is that correct? I ran it & it said it updated some 12000 records.

Thanks
0
 
LVL 39

Expert Comment

by:als315
ID: 39760111
Yes, table should be updated, now you can search CrLf in your table
0
 

Author Closing Comment

by:Richard Korts
ID: 39760156
Works perfectly!!

Thanks so much, I thought I would NEVER overcome this.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now