Export Text
By Richard Rost
3 years ago
Exporting Tables or Queries as Text from Access
In this Microsoft Access tutorial, I'm going to teach you how to export data from a table or query as a text file. This is the most portable solution and it's best if you don't know what kind of software the other person has. They may have Microsoft Access, but they might only have Excel, a text editor, or even just email. Text is the most versatile option. I'm going to present a non-VBA solution first for the non-programmers, and then I'll introduce a slightly more advanced option that requires one line of VBA code using the RunSavedImportExport command. This gives you a one-click solution. We'll also learn how to deal with the "run-time error 3625" problem.
Kimberly from Seattle, Washington (a Platinum Member): Is there a way I can quickly export my customer list to send it to my sales reps? Not all of them have Access. Some of them use Excel, but others don't even have that - just email. That's why I think text is safest and easiest for everyone. Now I need to do this multiple times a day, so is there a way to do it with just one click? Also, I don't want to give them all of my information; I need to keep out some of the sensitive financial data.
Members
Members will learn a slightly more advanced VBA method with the TransferText command. This approach provides more options, such as enabling you to change the table or query in use at runtime. You can also specify a filename.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Next Video
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, RunSavedImportExport, TransferText, export to text file, csv, export from access, currentproject.path, the text file specification does not exist, you cannot import export or link using the specification, run-time error 3625, export specification, edit import export specification, mSysIMEXSpecs, mSysIMEXColumns, First row contains field names
Subscribe to Export Text
Get notifications when this page is updated
Intro
In this video, I will show you how to export a table or query from your Microsoft Access database as a text file, making it easy to share your data with users on any platform. We will cover how to create a custom query to exclude sensitive information, export the data manually, save your export steps for future use, and add a button for one-click exports using a simple line of VBA code. This tutorial is perfect for anyone who needs to quickly and securely share selected data from Access, even with users who do not have Access or Excel.
Transcript
In today's video, I'm going to show you how to export a table or query as a text file from your Microsoft Access database.
You might be thinking, text, why would I want to make a text file? Well, text files are the most portable. If you're going to share this data with other people and you don't necessarily know what they've got, text is the best thing to send them. They might have Access, they might have Excel, they might just have a text editor or email, or they could be on a Mac. You never know. So that's why I like sending data out as text files if I'm not sure what the other person has on the other end as far as being able to read that data.
That's why I put here data sharing, simplify it. That was kind of cheesy, but here we go.
Today's question comes from Kimberly in Seattle, Washington, one of my platinum members. Kimberly says, is there a way I can quickly export my customer list to send it to my sales reps? Not all of them have Access. Some of them use Excel, but others don't even have that, just email. That's why I think text is safest and easiest for everyone and you are correct, Kimberly.
Now I need to do this multiple times a day, so is there a way to do it with just one click? Yep. Also, I don't want to give them all of my information. I need to keep out some of the sensitive financial data.
So Kimberly, I'm going to show you a couple of ways to do this. First, I'm going to show you the easy way. Then I'm going to show you a slightly more difficult way, but that will be easier in the long run. That's why I called this an expert and developer lesson. Expert means you're beyond the basics, but you're not quite a developer yet. That's why I like my middle ground. Developer involves some VBA programming.
I'm going to show everybody a way to do it without programming first. It involves a couple more clicks, but it's still pretty easy. Then if you want the little bit of additional stuff to make it even easier, we can do it with one click by just putting a button here that says export customers and you're done. So I'm going to show you both in this video.
Here I have my TechHelp free template. You can download a copy of this off my website if you want to. It's a free database.
Whenever I'm doing anything that involves importing and exporting, I like to put my database in its own folder. Make a folder here. I'll call it myDB, whatever, my database. We're going to move this database into that folder so all the stuff we do can happen inside of here.
You can work just on your desktop or you can work in your database folder wherever you want. This is where I'm going to put my stuff. When I export the file, I want it to go into this folder. It's going to go into C:\\Users\\Amor Kerr\\Desktop\\myDB.
Let's open up the database.
Kimberly wants to export all of her customers, but she doesn't want to export all the fields. So we're not going to export the customer table directly. She wants to keep out some of the fields like the financial data, like credit limit or family size or whatever. We're just going to send the basics.
That involves a query. Let's take this customer_LFQ, because this is a query where we took our first name and last name and put them together. We can export that too. If you have a query, you've got some other calculated fields you want to add to it, or you want to throw some criteria in there, you just make a query that shows exactly what you want to export. Then we'll export that and it will export just as if it was a table.
If you want to learn more about query criteria, go watch this video. I have this video on calculated fields if you want to learn how to put data together in a query.
Here I have the customer ID and the LF field (last name, first name). Let's throw in some other fields, whatever you want to export to your sales rep. Let's give them first name, last name, email, address, city, state, zip, country, phone number, maybe. Let's just put all these down here, other than what you want to export, but not the important stuff like your credit limit and stuff like that. We'll leave that out.
Save that, run the query, and just make sure this is all the stuff you want to export. Yep, that looks good. Save it, close it, back to the main menu.
Now we're going to export this manually one time. When we do, we can save those steps so that we can replay them later, kind of like a macro recorder. It's not really a macro recorder, but it's kind of like that.
Find your query. Here's my customer_LFQ. Right-click, export as a text file. Text file right there. This window pops up.
We're going to browse to where we want this to go. In my case, it's on my desktop, here's my myDB folder, and I'm going to put it right in here. It's going to be my customer_LFQ.text. Let's change this to just customer.text. Let's get rid of that LF. Customer.text. Save it, and then hit OK.
Here's what it's going to look like. Delimited or fixed width. Don't use fixed width. Almost no one uses this anymore. Delimited means there's a comma between each one of the fields. Next. Commas, you're delimited. That's good. Text qualifier means quotes are going to go around text fields. That's fine. I want to include field names on the first row. Yup. So you get all your field names there. Let's hit next.
It's going to export to this file. That's fine. Then hit Finish. OK. Your export is complete.
Very important. Click on this: Save export steps. It's going to give you this option. Save as... What do you want to name it? Well, that's not bad, but I want to get rid of that hyphen and just make it export_customer. Remember this now. Write this down somewhere. Put this exact thing in your Notepad or something. I'm going to take this export_customer. We have it for later. Copy that. Drop it in Notepad. Then minimize that. Just hang on to that exactly as it is right there.
Then hit Save. You just saved that in your saved exports.
Let's check and see what we got. Go back to our folder. Open up that customer.text. Here it comes in. Oh, look at that. All nice and pretty. You got your first row of field names, your ID, your data. Everything looks good.
Now this is what I can send to my sales reps or whoever else. You can send them this file as an attachment, or just copy and paste the text. Whatever you want to do. It's easy to get to somebody else.
For now, I'm going to close this. I'll move that export_customer back out of the way.
A couple days go by, and you want to update that information. You want to send them another customer file. How do you find what you just did without redoing all those steps? Go to External Data. Over here in the Export section, look for Saved Exports. There it is, right there. There's your export_customer. Hit Run.
Already exists. You want to replace that file? Yes. That's it. You're done. Anytime in the future you want to do that automatically, you just go in there, click Run, a couple clicks, and you're done. Easy enough. No programming required.
Now, one question that people always ask me at this point is, what if I want to make changes to this thing? Unfortunately, you can't change this here. You can click here to edit the description, like "this is my customer export," but that's about all you can do at this point. If you want to change this, you just have to delete it and then create it over again. It's not that hard to recreate it, but no, there's no tool in here to edit it.
If you want an even easier method, the one click method, you want to put a button right here. That's going to involve a little bit of programming, literally just one line of VBA code. But you have to know where to put that VBA code. I'm going to show you in just a minute.
It would make it much easier if you understand VBA first. Please go pause the video now and go watch this if you haven't yet already. It's about 20 minutes long and teaches you everything you need to know to get started with VBA. Just go watch this and then come on back.
We're going to put a button on our main menu to export our customers. Right-click, Design. I'm just going to copy one of the other buttons. Copy and paste. Let me make this bigger, slide you over here. We're going to go Export Customers. Open up the button's properties. Let's change the name to exportCustomerButton. Right-click, Build Event. Sorry if you can't see it. It's on the screen. Right-click, Build Event. There it is.
Right inside here, the exportCustomerButton_Click, I'm going to put one line of code in here. Are you ready for it? Here it is:
DoCmd.RunSavedImportExport
There it is. OK, space. Now what's the Saved Import Export name? Remember that thing I told you to save earlier and drop in Notepad? That guy, that's what you want. Copy him, come back over here, and put that inside quotes like that.
That's it. That's all you need. Maybe put like a message after it if you want to. I have my Status function, status "Export done" or whatever, and then a beep or whatever you want to put after that. If you don't know what the Status function is, go watch my Blink Template video. I tried to make the status, or I forgot, I actually made a separate video just on the status box because everybody kept asking me where to put that status box.
Anyway, it's going to run that export, it's going to show status done, and then it's going to beep. Save it. Close it. Close it.
Let's open it and click Export Customers. You ready? Done.
Go back to your folder. There it is. Don't believe me? Delete it. OK, I just hit delete. I deleted it. Let's come back over here and run it again. Done. There it is. There's your file, and it opens up over on the side.
That's it. You've got a one click export.
One problem people commonly have with this is that instead of using RunSavedImportExport, they try using the other popular command, which is DoCmd.TransferText. That's the other way you can save text files. I'm going to cover this in the extended cut because there are some additional options you get too.
They'll go through here and they'll pick Export Delimited, which is what you want. What's that specification name again? Is it export_customer? The table name: OK, that's customer_LFQ. The file name: they give you the file name. I'll just put in here something.text. It's not going to matter in a minute. Has field names: True. OK, whatever. They try doing that, and then when they run it, where are you? Over here. Run. You get runtime error 3625: The text file specification does not exist.
What do you mean it doesn't exist? I just created that specification. No, you didn't. We didn't create an import/export specification. That's something completely different. What we created was a saved export. The two things are completely different. A saved export is not the same as an import/export specification.
I am going to cover import/export specifications and the TransferText command in the extended cut for the members. You get a lot more options. You can control things like you can specify the table or query at runtime, so you can have different queries that you might want to run to export based on who you're exporting to or what kind of information you want to send.
You can change the file name at runtime, so you can specify a different file, maybe have it based on your sales rep name, and all that kind of stuff is options you can change. I'll cover that in the extended cut for the members.
For the rest of you, if you don't want that, you want what I had before. Let me see how I can undo and get back to it. There you go. That's the one you want. This one's simpler. It doesn't give you as many options, but it's easy to use. That's literally one easy line of code. So that's how you quickly and easily export text with one click. Once you got the setup done, it's just one click after that.
Come back tomorrow and I will show you how to import that data with just one click, which you can give this database to your other person, your sales reps. You can give them the runtime edition so they don't have to buy Access too. Tune in tomorrow, same bat time, same bat channel, and I'll show you how to import text. It's just as easy as this stuff was.
If you really want to learn about this stuff, in my Access Expert Level 19 class I cover all kinds of importing to Excel, to Word, we do a mail merge, we do text files, we do fixed width and delimited, other database files, HTML, all kinds of stuff. This is almost two hours long and I cover lots and lots of information in this class.
That is going to be your TechHelp video for today. I hope you learned something, folks. Live long and prosper. I'll see you next time.
Quiz
Q1. Why is exporting data as a text file considered the most portable option?
A. Text files can be imported into almost any program, regardless of platform
B. Text files have better security features
C. Text files are easier to edit directly in Access
D. Text files require less storage space than all other formats
Q2. When exporting customer data but omitting sensitive fields, what is the best way to control which fields get exported?
A. Export the entire table and delete columns manually
B. Use a query that selects only the desired fields before exporting
C. Export only the first five records
D. Hide the sensitive columns in the table before exporting
Q3. What method does the video first demonstrate for exporting data to a text file without programming?
A. Creating a macro from scratch
B. Exporting a query manually via right-click and using saved export steps
C. Using the TransferText VBA command
D. Copying and pasting data into Notepad
Q4. What is an important step after completing your manual export via the wizard, if you intend to repeat the export in the future?
A. Save export steps and give the export a clear name
B. Move the output file to a network drive
C. Close Access before sending the file
D. Email the export file immediately
Q5. What options should you select during the export wizard to create a typical CSV-like text file for sharing?
A. Delimited file with commas, include field names on first row, use text qualifier for text fields
B. Fixed-width file, do not include field names, no text qualifiers
C. Delimited file with semicolons, do not use text qualifiers, omit field names
D. Fixed-width file with spaces, include field names, use text qualifier
Q6. Where can you access and rerun your saved exports in Microsoft Access?
A. External Data tab under the Export section, Saved Exports
B. Home tab, under Recent Documents
C. File menu, under Templates
D. Database Tools tab, under Macros
Q7. If you want to change which fields or data are included in a saved export, what do you need to do?
A. Delete the saved export and create a new one
B. Edit the saved export directly in the list
C. Email Microsoft support
D. Update the export file in Notepad
Q8. What is the main benefit of adding a button with VBA code to perform exports?
A. You can export with a single click
B. It restricts export capabilities to admins only
C. It increases security of the export file
D. It allows you to edit records during export
Q9. What is the correct one-line VBA command to run a saved export?
A. DoCmd.RunSavedImportExport "export_customer"
B. DoCmd.TransferText "export_customer"
C. Export.Run "export_customer"
D. RunExport("export_customer")
Q10. What common mistake do users make when attempting to automate exports with VBA?
A. Using DoCmd.TransferText without creating an import export specification
B. Forgetting to save the exported file
C. Naming the export file using non-alphanumeric characters
D. Using the wrong text qualifier in the export wizard
Q11. What is the main difference between a saved export and an import export specification in Access?
A. A saved export saves all wizard settings for reuse, while an import export specification lets you define detailed file structure and can be referenced with TransferText
B. A saved export is only for importing data
C. An import export specification automatically sends the file by email
D. There is no difference; they are interchangeable
Q12. For more flexibility when automating exports (like choosing the file name or query dynamically), what advanced Access method should you use?
A. DoCmd.TransferText with an import export specification
B. Edit the query each time before exporting manually
C. Use mail merge in Word
D. Use the export wizard every time
Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will walk you through how to export a table or query as a text file from your Microsoft Access database.
You may wonder why you would want to export your data as a text file. Text files are one of the most portable formats for data sharing, especially when you are unsure of what software the recipients might have. Some people may have Access, others may have Excel, and some may only be able to open text files in a basic editor or via email. They could even be using a Mac. For all these reasons, text files make sharing data much simpler and more reliable.
Today's example revolves around a common situation: exporting a customer list to sales reps, some of whom do not have Microsoft Access or Excel. This method is ideal if you need to share data with a wide range of users, ensuring everyone can open the file without compatibility issues.
The first step for this process is to prepare your database environment. When dealing with importing and exporting, I always suggest placing your database inside its own dedicated folder. This way, all new files you generate will be organized in a single location. For this example, I named my folder "myDB" and placed the database in it. This will serve as the location for the exported text files.
Next, let's consider which data needs to be exported. Often, you do not want to export the entire table since you might have sensitive information like credit limits or family details. Instead, the best practice is to create a query that includes only the fields you want to share. For this instance, I start by using a query that combines first and last names, but you can customize this with any fields—email, address, city, state, zip code, phone number, etc.—excluding anything you want to keep confidential.
Once you have the proper query in place and verified that it contains the data you want to export, save and close it. Now you are ready to perform the export.
To export your query as a text file, right-click the query in the navigation pane and choose to export as a text file. In the export wizard, browse to your folder, name the file appropriately (like "customer.txt"), and proceed through the wizard. Always opt for "Delimited" rather than "Fixed Width" since it is much more widely supported, and ensure commas are used as the delimiter. Make sure to include field names on the first row for clarity. Finalize the export and the text file will be created in your specified directory.
After the first export, a crucial step is to save your export steps. This makes future exports significantly easier, as you can repeat the process without walking through every step again. When prompted, give your saved export an identifiable name like "export_customer." I recommend copying this name and recording it in Notepad or another handy place, as you will need it shortly.
To test the exported file, simply open it in your folder using any text editor. You should see the field names on the first row, followed by your data. This file can now be shared via email, attached as a document, or copied directly for your sales reps to use.
When the time comes to update and re-send the customer data, you do not have to recreate the export from scratch. Go to the External Data tab, locate the Saved Exports section, and select your saved export task. With a couple of clicks, your updated data will be exported to the same text file, replacing the previous version with fresh data.
Editing an existing saved export is somewhat limited. While you can change the description, any other changes would require you to delete and recreate the saved export. This is not as cumbersome as it sounds, but it is good to be aware of the limitation.
For those who want to make the export process as easy as possible, it is possible to add a button directly to your main menu that runs the export with a single click. This requires a minimal amount of VBA code. Place a button on your form, name it something like "exportCustomerButton," and add to its click event a single line of VBA that runs your saved export. Use the same name you copied earlier. You can even add a status message or beep if desired for feedback, though that is optional.
If you are new to VBA, I recommend watching my introductory video on VBA basics before trying this part. Once the button is set up, you can test it repeatedly. Delete the export file to verify that pressing the button correctly generates a new copy with ease.
A common question is why not use DoCmd.TransferText for this task. While TransferText does work for exporting text files, it requires an import/export specification, which is a different kind of object from a saved export. If you do not already have an import/export specification defined, you will get an error using this method. I will explore the finer points of import/export specifications and how to use TransferText in the Extended Cut for members, including the benefits and expanded options those commands offer, such as being able to dynamically set table and file names at runtime.
For those who just want a simple, reliable export, the method described above should suffice, as it gets the job done with just a few clicks or one click if you add the button.
In my next lesson, I will demonstrate how to import a text file back into Access just as easily, which is especially useful if you are providing these files to others who might not have Access themselves. They can use the free runtime edition for these tasks.
If you want in-depth training on all aspects of importing and exporting—including working with Excel, Word mail merges, text files, fixed width formats, and more—my Access Expert Level 19 class covers all of these topics in detail.
A complete video tutorial with step-by-step instructions on everything discussed here is available on my website at the link below. Live long and prosper, my friends.
Topic List
Exporting Access query results as a text file
Creating a folder for organizing export files
Selecting specific fields to exclude sensitive data
Building a query for customized export data
Running and saving an export to a text file
Choosing delimited vs fixed-width formats
Including field names in exported text files
Saving export steps for future reuse
Running saved exports from External Data tab
Renaming saved export tasks
Adding an Export Customers button to a form
Writing VBA code to automate exports with one click
Using DoCmd.RunSavedImportExport in VBA
Attaching a message or notification to export completion
Troubleshooting common export errors
Difference between saved exports and import/export specifications
|
