Access supports exporting data to plain text files with a variety of options. Two of them produce table-like structures that maintain the visual appearance of columns and rows.
- [Instructor] Access supports exporting data to plain text files with a variety of options. Two of them produce table-like structures that maintain the visual appearance of columns and rows. Let's take a look at these by exporting the Customers table, I'll make sure it's selected in the navigation pane, and we'll go to the External Data tab and take a look at the options under Text File. First, we're going to browse out and make sure that we're saving these inside of our Exercise Fields Folder, Chapter Two, and then the 02_02_TextFiles folder. I'll leave the file name for now, Customers.txt, and press the Save button.
Next, I want to take a look at the option Export data with formatting and layout. I'll go ahead and turn that on. Now you might think that a plain text file can't store any formatting information anyway, so what does this matter? And you're absolutely right. We can't store any bolding or font face or any images or anything like that inside of a text file, but you actually might be surprised by the results. Let's go ahead and leave that checkbox turned on and press the OK button. Next we get to choose the character encoding and I'll just leave it as the Windows default. Let's go ahead and say OK and that'll export the file.
I'll close the Save Steps option and go ahead and switch into my text editor and take a look at the file that we just exported. When exporting a data table as a text file with formatting, Access inserts these hyphens and pipe characters to preserve the format of a column and row layout. Now if your file is looking a little bit scrambled, you might need to turn Word Wrap off in order to get this full text like this. In my case, the text is allowed to flow off the screen. So this is what a formatted text file looks like. It makes it easy to read but it would be kind of difficult for a computer to parse this information.
For instance, you'd have to determine where these pipe characters are in order to determine the values for each of these columns. Let's go ahead and close this down and take a look at another option inside of Access. The next file that I want to look at is still underneath the Text File option, so I'll go ahead and choose that again. This time we'll leave Export data with formatting and layout turned off. I'll go ahead and overwrite the same file so we'll use the same file path that we had just a moment ago. This time I'll press the OK button, and it's going to ask me if I want to overwrite it. I do, and it'll give me some additional options. We'll talk about the delimited option in the next movie, but for now I want to focus on fixed width.
In the fixed width formatting Access is going to add additional space characters to the end of each value in order that each column will automatically line up together. Let's go ahead and choose the Next button. On this page we get to determine exactly how wide each column is going to be. In order to determine its width, we just move these lines between each of these columns. For instance, the width of the first column right now is determined to be about 12 characters. I can go ahead and change this by dragging the arrow to the left. Now the width that's coming in is being determined by the value in the properties from the underlying data table.
This second column which is the first name is pretty wide right now. If I go ahead and scroll over to the right, we can see exactly how wide it is. Here at 260 characters is the end of the first name column and the beginning of the last name column. If I wanted to, I could change its width by dragging this arrow all the way back over here to the far left. Now this is going to be a tedious process, so rather than do that, what I want to do is actually fix the underlying data table. Let's just press the Cancel button and we'll go into the Customers table in Design View. Just right-click on it and choose Design View.
If I click on the Data Type for the FirstName column we can see that it has a field size of 255. This is why our column was showing up as wide as it was. Now we actually don't need 255 characters worth of space for our first names. I'm just going to go ahead and change this to 20. If you're doing this on your own data tables you want to make sure that you're not going to be truncating any text. Let's go ahead and change the last name to 20 as well. The phone number, I only need about 10 characters to store that. For the address, we'll make it 50 characters. For the city, we'll change that to 20.
The state, we're actually going to have a problem with because it's actually linked as a relationship to another table. If I try changing this value to two which is the only number of characters that we need to store the state abbreviation, we'll get this warning message that says we can't change it because it was part of a relationship. For now let's just leave it at 255. For the Zip code I only need five characters, and for the email address, let's go ahead and change that to 30. I'll go ahead and save the table. It's going to tell me that I might be losing some data, and for the purposes of this example that's just fine. We'll go ahead and close out the Customers table, and we'll go back to the External Data tab and click on Text File one more time.
We'll leave the same file name. I'll make sure that Export data with formatting and layout is unchecked, and press OK. It's going to overwrite the existing file and that's fine. Then, once again we'll switch it back to Fixed Width. Now we can see that we have much more manageable column widths. Let's press Next. If I want to fine tune these again I could just go ahead and drag these arrows to the left, and you can see if I scroll over to the right we'll find the column that represents the state and it's still going to be pretty wide. Here at about 364 characters is the end of the state column.
Let's just go ahead and drag this one to the far left. If you drag it over in small chunks it makes it a little bit easier to manage and see what's going on. I'm just going to move this line so it's tight against the two letter state abbreviations. Once everything is lined up, press the Next button. Then, we get to finalize by specifying the name of the file one more time. I'll go ahead and choose that file and press Finish. I'm not going to save the export steps so I'll go ahead and press Close, and now it's time to take a look at the results. Once again we'll open up the same file in the text editor.
In the text file we can see that each column is separated by a specific number of characters. That way the first names all start in the same character location. Last names all start in a column, phone number here. Here's the street address, and if I scroll over to the right we could see the city, state, Zip and email addresses all starting in the same character location within the file. This might be a little bit easier to read because it doesn't include all those extra hyphens and pipe characters that we saw with the formatted example. So even with a simple text file, the data exports can occur in a highly legible way.
These two examples might be harder for a computer to read and process, but if your goals are to share data in a human-readable format in the absolutely most compatible file format on the planet, the column and row layout provided by the formatted and fixed width text files might be the option for you.
In this course, Adam Wilbert shows you how, by exploring options for getting your data out of Microsoft Access, making it easier to share with others. He covers Access data export options, and demonstrates how to modify exported files with a text editor. He also explains how to link Access to an Azure SQL database to provide a cloud storage solution for your Access tables.
- Why not use web apps?
- Exporting data to HTML
- Formatting HTML with table CSS
- Working with data exports
- Converting data to JSON
- Building a webpage to parse exports
- Linking to an Azure SQL database
- Linking Access to Azure tables
- Interacting with data on the web