Solved

Export MS Access

Posted on 2013-12-20
22
576 Views
Last Modified: 2014-01-04
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
Comment
Question by:Richard Korts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
22 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 39732610
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
 

Author Comment

by:Richard Korts
ID: 39732640
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39732759
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Richard Korts
ID: 39732970
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39732992
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
 

Author Comment

by:Richard Korts
ID: 39733105
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39733272
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
 

Author Comment

by:Richard Korts
ID: 39733708
PatHartman

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

Author Comment

by:Richard Korts
ID: 39733709
PatHartman

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

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

Author Comment

by:Richard Korts
ID: 39733851
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
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39734023
You have to do it in a query.  Then you export the query rather than the table.  Look at my previous suggestions.
0
 

Author Comment

by:Richard Korts
ID: 39740483
I have to yet figure out how to do what is suggested in a query.

rkorts
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39740536
Go back to one of my earlier threads.  I told you there how to do it in a query.
0
 

Author Comment

by:Richard Korts
ID: 39750272
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39750349
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
 

Author Comment

by:Richard Korts
ID: 39752478
See attached; this is what I am trying (obviously wrong). Produces message in 2nd image.

Thanks
AccessQuery.jpg
ErrMsg.jpg
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39752948
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
 

Author Comment

by:Richard Korts
ID: 39754326
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39754715
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
 

Author Comment

by:Richard Korts
ID: 39755389
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
 

Author Closing Comment

by:Richard Korts
ID: 39755391
The original problem is NOT solved;

"A" for effort.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39756507
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 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