• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Export MS Access

Hello,

This question is related to Q_28310426.html.

I have exported a table using the techniques as described in Q_28310426.html. It works for MOST records in the table; about 1 out of 40 it fails & outputs 2 records (for one database record) instead of one.

I am attempting to show the individual record content with the attachments.

The Excel spreadsheet cust_10034.xls shows the first Access record that failes on export (to .txt file).

The Excel file exported_10034.xls is the two records exported from the single record in Access. I opened the .txt exported file & told Excel it was tab delimited.

I can provide other examples in this format or another; I can also show segments of the exported file in Notepad.

Why is this happening?

Thanks
cust-10034.xlsx
exported-10034.xlsx
0
Richard Korts
Asked:
Richard Korts
  • 12
  • 10
1 Solution
 
PatHartmanCommented:
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

Open 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.
0
 
Richard KortsAuthor Commented:
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
0
 
PatHartmanCommented:
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,
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Richard KortsAuthor Commented:
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
0
 
PatHartmanCommented:
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
0
 
Richard KortsAuthor Commented:
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
0
 
PatHartmanCommented:
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
0
 
Richard KortsAuthor Commented:
PatHartman

I checked no boxes; I clicked OK. It just does it, there are no other options / choices.
0
 
Richard KortsAuthor Commented:
PatHartman

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

Strange, they usually ENHANCE things, not take things away.
0
 
Richard KortsAuthor Commented:
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
0
 
PatHartmanCommented:
You have to do it in a query.  Then you export the query rather than the table.  Look at my previous suggestions.
0
 
Richard KortsAuthor Commented:
I have to yet figure out how to do what is suggested in a query.

rkorts
0
 
PatHartmanCommented:
Go back to one of my earlier threads.  I told you there how to do it in a query.
0
 
Richard KortsAuthor Commented:
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!
0
 
PatHartmanCommented:
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.
0
 
Richard KortsAuthor Commented:
See attached; this is what I am trying (obviously wrong). Produces message in 2nd image.

Thanks
AccessQuery.jpg
ErrMsg.jpg
0
 
PatHartmanCommented:
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.
0
 
Richard KortsAuthor Commented:
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
0
 
PatHartmanCommented:
Some columns may be null.  Use the Nz() function to replace null with a ZLS (zero length string)

Expr2: Replace(Nz([ServiceComments2],""),Chr(10)," ")
0
 
Richard KortsAuthor Commented:
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.......................
0
 
Richard KortsAuthor Commented:
The original problem is NOT solved;

"A" for effort.
0
 
PatHartmanCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now