Solved

Export MS Access

Posted on 2013-12-20
22
525 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
  • 12
  • 10
22 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:Richard Korts
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
PatHartman

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

Author Comment

by:Richard Korts
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
You have to do it in a query.  Then you export the query rather than the table.  Look at my previous suggestions.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

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

rkorts
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
See attached; this is what I am trying (obviously wrong). Produces message in 2nd image.

Thanks
AccessQuery.jpg
ErrMsg.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
The original problem is NOT solved;

"A" for effort.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 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

11 Experts available now in Live!

Get 1:1 Help Now