Export MS Access

When extra records are created in delimited files, it is usually because one of the fields contains a carriage return/line feed.  This is especially common in text fields.  The simplest solution is to add quote delimiters for text fields.  That will encapsulate the CrLf and not let it cause a new record.  I have that problem with one of my exports since it includes notes fields where the users may include carriage returns to make the text look better.

                strComments = Replace(strComments, vbCr, " ")                    'get rid of embedded carriage returns 
                strComments = Replace(strComments, vbLf, " ")                    'get rid of embedded line feed

Select allOpen in new window

Extra delimiters - tabs for tab delimited, comma for comma delimited, you would need to get rid of them also.

In general, the easiest solution is to simply use well formed exports and delimit your text fields.

To PatHartman

That makes perfect sense; I am sure that is exactly the case.

I am just not clear on where to put that code in; I am exporting using what you told me on the other question, I don't recall any place to enter code.

Or is it in the "advanced" tab?

Thanks

In my case, I was using FSO to write the records rather than TransferText.  You would need to create a query or modify the one you are already using to do the function inside the query but in that case, you need to substitute the ascii characters you want to exclude.  It gets a little complicated if you need to exclude more than one character since you need to nest the Replace functions.

Select ..., Replace(Replace(yournotes, "the character"," "), "the other char", " "), as fld1,

To find the ascii value, you can use the asc() function.
print asc(vblf)
 10
print asc(vbcr)
 13

In the query, you need to replace "the character" with whatever ascii character you want to eliminate using the chr() function.

so you should end up with something like:
Select ..., Replace(Replace(yournotes, chr(10)," "),  chr(13), " "), as fld1,

To PatHartman,

I know almost NOTHING about Access; I don't know how to make a query; think I figured out the basics. Note the previous issue (previous question) DID NOT require a query.

So I set out to build a basic one; see attached, I included ALL fields from the Customers table.

In the cell where it says "Criteria", do I put that function you recommended? Replace(Replace(yournotes, chr(10)," "),  chr(13), " ").

Where I put in the actual fieldname for the fields (columns) that I know are effected, replacing the fieldname "yournotes" with the actual field name?

Note in the 2nd attachment, two of the actual fields are servicecommens1 & servicecomments2. So do I put the Replace in the Criteria cell in those coulmns?

Thanks
access-query.jpg
fields.jpg

Why don't you change the export so that it has quotes around text fields?  That doesn't require any programming or modification to your query.

Most applications will not be affected by this change or like Access, they will have settings to define the formatting.
Export.JPG

To PatHartman

It is not clear how enclosing things in quotes will eliminate <cr><lf> sequences.

But even if it did, mine does NOT work as you suggest.

When I right click on the table name, I then click export, I get a list of file types (I cannot screen capture that) Excel, SharePoint List, Word RTF File, PDF or XPS, Access, Text File, XML file, ODBC Database, HTML document, Word Merge, I choose Text File & I get the attached; it has none of the choices you show.

I select the top checkbox & it exports the file. I have no choices.

I am using Access 2013; maybe I don't have it configured correctly?

Thanks
export-text-file.jpg

It doesn't eliminate them.  It encapsulates them so they don't cause a new record.

Don't check any of the boxes. Press OK on the pictured screen and you will see more details.    Press the Advanced button on that page to get to the image I posted

PatHartman

I checked no boxes; I clicked OK. It just does it, there are no other options / choices.

PatHartman

I'm guessing I cannot do it in MS Access 2013.

Strange, they usually ENHANCE things, not take things away.

PatHartman

I was wrong, it DOES do what it should, just like you said; I had not waited long enough for the size of the file.

However, it only clears up a portion of the bad ones, maybe half. The others are still there.

Is there some way to remove <cr><lf> & replace with a space WHILE it's exporting?

Thanks

Rkorts

I have to yet figure out how to do what is suggested in a query.

rkorts

Go back to one of my earlier threads.  I told you there how to do it in a query.

To pathartman,

Please give me an exact example of the query you are referring to.

I guess I am a dummy but I have no clue how to do it; the access query thing has always been confusing to me.

Thanks & happy new year!

I can't code the query for you since I have no clue what you want.

It will end up looking something like this:
Select ..., Replace(Replace(yournotes, chr(10)," "),  chr(13), " "), as fld1,
From yourtable;

Open the query builder and select all the column you want.
substitute the Replace(..) for the "yournotes" column.

If it doesn't work, switch to SQL view and copy the string and paste it here along with the EXACT error message.

See attached; this is what I am trying (obviously wrong). Produces message in 2nd image.

Thanks
AccessQuery.jpg
ErrMsg.jpg

It looks like you have used the Replace() expression as criteria.  That isn't where the expression goes.  You need to replace the Field with the expression.  Access will automatically assign an Alias such as expr1, expr2, expr3, and expr4 for the four expressions.  You can use those names or replace them with meaningful names.  You just can't use the original names or the name of any other column in the data source.

PS, if you had pasted the actual string, I would have fixed the problem for you.

Thanks.

I did that, it accepted the changes & I ran it. It sort of worked, see attached. The issue is that Expr2 is almost always "#Error". Some instances of 1 & 4 are like that too.

Expr2 is like this: Expr2: Replace([ServiceComments2],Chr(10)," ")

Any ideas?

How can I detect what #Error means?

Thanks
query-errors.jpg

Some columns may be null.  Use the Nz() function to replace null with a ZLS (zero length string)

Expr2: Replace(Nz([ServiceComments2],""),Chr(10)," ")

To PatHartman,

Finally got it to create a query & export.

But the original problem is NOT solved.

I will give you the points in appreciation for all your effort. I will have to attack the problem in another manner. Don't know yet what that is.

I will comment that Access is VERY difficult to figure out. I used it in the mid 90's extensively. Don't remember it being that hard, but.......................

The original problem is NOT solved;

"A" for effort.

But the original problem is NOT solved

1. Based on the last example you posted, you were only getting rid of the line feeds.  You were not getting rid of the carriage returns.  My original example of the Replace() showed it nested so that the inner Replace() got rid of the line feed and the outer Replace() got rid of the carriage return.
2.  Did you ever try exporting using the quote delimited style for text fields?