?
Solved

Export MS Access

Posted on 2013-12-20
22
Medium Priority
?
592 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 40

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 40

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 40

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 40

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 40

Accepted Solution

by:
PatHartman earned 2000 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 40

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 40

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 40

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 40

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 40

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

850 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