Parse Lines
By Richard Rost
3 years ago
Parse Lines in a Textbox, Create Individual Records
In this Microsoft Access tutorial, I'm going to teach you how to copy a block of text from something like a document or an email, paste it into your Access database in a text box, and then parse it out line by line and save those as individual records. This is a great technique for pulling email addresses, for example, out of a list of emails you were sent.
Aisha from Evanston, Illinois (a Platinum Member) asks: A couple of times a week, I get an email containing a list of other email addresses from my sales reps that we need to add to our lead generation database. Right now, I'm copying and pasting them individually into their own records, but is there a way I can just select them all and add them all with one shot?
Members
Members will learn how to parse a line that includes more information, for example, first name and last name. Then, we will add a record to the contact table for each new record, indicating a follow-up is needed.
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
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, split text into multiple records, multiple rows, parsing text in Access with VBA, Microsoft Access, parse lines, text box, create individual records, copy text, block of text, paste, database, email, save records, technique, email addresses, list of emails, lead generation, sales reps, one shot, contact table, follow-up, Access database, tutorial
Subscribe to Parse Lines
Get notifications when this page is updated
Intro In this video, I will show you how to quickly copy a list of email addresses from an email or document, paste them into a Microsoft Access form, and use a button to automatically parse the lines and create a new record for each email in your customer table. We will cover how to split multi-line text input using VBA, handle duplicate records, and use an SQL append query to add the emails to your database efficiently. This is a great way to save time by importing multiple email addresses at once.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to teach you how to copy a block of text from something like a document you get or an email. We're going to paste it into our Access database into a text box just like this. Then we're going to click a button, and it's going to parse those emails out line by line and create a record for each of them in your Customer table.
Here they are, and you're probably wondering why there are four here, but there are five emails. Well, one of them is a duplicate: [email protected] is already in there. We're going to talk about all this in today's video.
Today's question comes from Aisha in Evanston, Illinois, one of my Platinum members. Aisha says: A couple of times a week, I get an email containing a list of other email addresses from my sales reps that we need to add to our lead generation database. Right now, I'm copying and pasting them individually into their own records. But is there a way I can just select them all and add them all in one shot? Looking for a time saver, just like we did in yesterday's video.
Yesterday we covered the split function by pulling part numbers out. We got a list of jobs, and each job had multiple part numbers that were in a single field. We split those up based on that comma and then we added those into a separate part table.
Today we're going to do something very similar, but instead of parsing it based on commas, we're going to split it based on lines. Each line will be a separate record.
In fact, today's lesson is actually going to be a little easier. It's a slightly different technique, but it's a little easier because we don't really need a recordset for this. But go watch this video if you haven't yet already, and also watch this one too, because instead of a recordset, we're going to use a little SQL. So it's good if you know how SQL works before watching this video.
These are both free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Here I am in my TechHelp free template and just like we did yesterday, let's - we don't need - yeah, we can keep you around.
This, we need a box that we're going to copy and paste our list of records into. We'll just do this. That's our box. And let's call this guy the email box. Get rid of the control source, get rid of the format. We'll just copy and paste our records right into here.
Our Hello World button will be our do stuff. Then, for now, we'll put a status down here just to see what's up. All right, that's our status box.
Let's go into our VB code.
So we got the stuff in our email box.
Now, the email box is going to be an email address followed by a VB NewLine character, followed by another email address, followed by a VB NewLine character, and so on. We're going to use the split function again and split this text up into an array. But we're going to use VB NewLine instead of a comma.
So we still need an array just like yesterday. Right: Dim - let's call it the email array as String.
Let's blank the status box. I like to blank the status box when we start an operation.
First thing, if the user hasn't put anything in there: If IsNull(email box) Then Exit Sub. You can give them a warning message, say "Hey, nothing in there," whatever you want to do there. Your Legos, put them together how you like.
Now we're going to fill the email array with data from the email box. We're going to split it using the VB NewLine character at the end of each line of the text. You get VB NewLine, which is actually comprised of two characters: character 13 and character 10. It goes back - well, that's carriage return, new line for you. Don't worry about it. It's just called VB NewLine in VB.
So: email array = Split(email box, VB NewLine).
So now, if there were five lines of text in there, you've got an array with five elements in it, zero to four.
Now we can see that in our status, there are -
All right, how do we figure out how many elements are in an array? Remember from yesterday: UBound(email array) plus one = emails.
Yes, I know you're supposed to take UBound minus LBound. A couple people have commented on that. I get it. But if you're creating the array yourself, you can pretty much be guaranteed that it's starting at zero because you could have - if you have an array that you're doing stuff with, you could remove elements from the bottom of the array, like remove zero and two, whatever, and then your LBound is now two.
If this is an array you control completely, and you don't have to worry about other processes changing it, it's almost guaranteed to be zero-based. Of course, different languages handle arrays differently. C is a bit of a pain, but that's why I like Visual Basic. It's just easier.
You don't get as much control as you do with some other languages like C, but it's a more friendly programming language, I think.
Let's see where we're at right now.
Okay, we got our array filled; we got our items. Save that.
Run a quick debug compile. Always make sure it's good. Close this, close it, close it, close it, open it.
If I just do stuff now, nothing happens because it's not - let me copy my list from my email and paste it in there and hit do stuff, and there's five emails. That's perfect.
Now, we need a little loop and we can take those email addresses and throw them into the customer table.
If I come in here and scroll down, all right -
Get over there, scroll down. All right, we got up to the last guy, customer ID 33, and notice [email protected] is on there already. I have the email address set to indexed, no duplicates, so if there is a duplicate, it won't be added.
If you want to change that, change that if you want to. I like email to be indexed so that you don't get the same person there twice and you don't send them 15 emails with the same message.
How do we go about adding each one of these lines into the customer table? That's just an INSERT INTO - an append query. We need a loop counter variable, so: X as Long.
For X = 0 To UBound(email array)
Next
What are we putting here? This is where we're going to put our INSERT INTO. Some people like DoCmd.RunSQL. I like CurrentDB.Execute.
It's on my list: I'm going to make a video one of these days on the differences between RunSQL and Execute. There are some differences. I prefer Execute.
INSERT INTO (this is where the SQL comes in handy): CustomerT (the table), one field, it: Email.
Values - here's where your double double quotes come into play, because the email address has to be enclosed in quotes. So it's going to look like this: double double quote, quote, and email array whatever one you're on (X), and quote, quote, parentheses, quote.
It's going to look like this.
Sorry about those notification sounds. I just got a new phone and I set up some stuff on it, and I forgot to turn off the notification. That's my bad. It's not you, it's me. And no, I'm not going back in and rerecording the video because it's a long take.
There you go. It's going to end up looking like Values and then inside quotes you have whatever your email array for that one is - that's the element you're on.
That should be it. Save it.
Again, quick debug compile - and you missed it if I debug. I covered this in so many videos. I know those of you who follow me daily or weekly see a lot of the same stuff over and over again, but for the newbies, if you're new, if you're writing code, once in a while throw in a debug compile just to make sure all your stuff's good.
Ready - do stuff - boom. Nothing appears to have changed because it just happened so fast, but if you go to your customer table now, scroll down, look at that, look at that right there: there's your records. That added each one of those items.
If you have some extra characters and stuff in there, you might want to throw a Trim in here too, maybe like right here. Add Trim in there. That will trim any leading spaces or trailing spaces.
That's it. In the extended cut for the members, we're going to take it one step further. We're going to assume that your line of text that you get is not just the email address. We're going to throw in first name and last name as well. So we're going to parse each line out, then we're going to parse the elements of each line and put those into the table correctly like that.
We'll also add a contact - a follow-up. We'll add a follow-up to the contact table.
Remember, in our TechHelp database, each customer has contacts and you can mark these as follow-ups, then create a follow-up list so all the new people that you add to your database will show up on your follow-ups list.
The follow-ups are not really implemented in the stock TechHelp database, but I did put a video together where I cover using those follow-ups because that is in there - the field is in there. I try to keep the TechHelp database as slimmed down as possible for the purposes of demonstrating stuff. It's enough of a database that I have enough stuff to show you, but there's not too much stuff crammed in there to make it too complicated.
Sometimes when you get a database to you, you look through this list and there's so much stuff, and you're like, "What am I doing?" I try to keep this nice and simple.
That's why all the features that I cover in all these different videos are not in the sample database. So that's covered in the extended cut for the members. Silver members and up get access to all my extended cut videos.
If you want to learn more about working with arrays, I cover them in more detail in my Access Developer 21 class. I will put a link to this down below as well.
That is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I'll see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To format emails in HTML
B. To parse a list of email addresses and add them as individual records into a Customer table
C. To sort emails alphabetically in a document
D. To export data from Access to Excel
Q2. How does the video suggest splitting the block of email addresses?
A. By using commas as the delimiter
B. By recognizing tab spaces in the text
C. By using the line breaks (VB NewLine) as the delimiter
D. By character count for each line
Q3. What control in the Access form is used for pasting the list of email addresses?
A. Combo box
B. Checkbox
C. Text box (email box)
D. List box
Q4. In the VBA code, which function is used to split the pasted text into an array?
A. Split
B. Join
C. Replace
D. Left
Q5. What is used to determine the number of elements in the email array?
A. LBound only
B. UBound plus one
C. Count function
D. Length of the text box value
Q6. Why might an email address not be added to the Customer table during this process?
A. If the email contains a space
B. If it has already been indexed in the table (duplicate)
C. If the email is longer than 20 characters
D. If the user has not confirmed submission
Q7. How are email addresses added to the Customer table in VBA?
A. Using a SELECT query
B. By exporting the table as CSV
C. Using an INSERT INTO SQL statement within a loop
D. By importing from Excel
Q8. Why is the Trim function optionally added when processing the email addresses?
A. To convert email addresses to uppercase
B. To replace special symbols in emails
C. To remove leading and trailing spaces from the email address
D. To duplicate the email address
Q9. What is the role of the debug compile stage before running the code?
A. To change all code to uppercase
B. To verify that the VBA code contains no syntax errors
C. To add new fields to the table
D. To automatically run the program
Q10. Which VBA function is preferred by the instructor for executing SQL statements to update records?
A. DoCmd.TransferText
B. DoCmd.RunSQL
C. CurrentDB.Execute
D. DoCmd.OpenQuery
Q11. If you want to include additional information like first and last names along with the email addresses, what does the extended cut video suggest?
A. Parsing each line further and splitting the elements before inserting them into the table
B. Merging all data into a single field
C. Ignoring the extra information
D. Only taking the first name
Q12. The tutorial mentions setting the email address field to "Indexed, No Duplicates". What is the main purpose of this setting?
A. To speed up data entry
B. To prevent the same email from being entered multiple times
C. To allow for sorting by email
D. To import emails faster
Q13. Why might learning about arrays be useful for the techniques shown in the video?
A. Arrays help in creating random numbers
B. Arrays allow you to modify forms at runtime
C. Arrays enable easy handling and manipulation of multiple data items, like email addresses, at once
D. Arrays are only used for mathematical calculations
Q14. According to the video, what should you do if there is nothing in the email box before running the import code?
A. Continue with the code anyway
B. Show a warning message or exit the subroutine
C. Replace it with dummy data
D. Add a default email address
Answers: 1-B; 2-C; 3-C; 4-A; 5-B; 6-B; 7-C; 8-C; 9-B; 10-C; 11-A; 12-B; 13-C; 14-B
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 focuses on an efficient way to add multiple email addresses to your Microsoft Access Customer table in one step, rather than entering them individually.
Picture the scenario: you receive a document or an email containing a list of email addresses that you need to input into your Access database. Instead of laboriously copying and pasting each address into its own record, it is possible to take the entire list, paste it into a single text box, and then with the click of a button, have each address automatically added to the Customer table as its own record.
Let me explain how this works. One of the key points here is that if a duplicate email address is found, the table ignores it so you do not end up storing the same address more than once. For example, if [email protected] already exists in the table, it will not be added again. This is due to having the email field configured with a unique index.
This question was inspired by a Platinum member who explained that she regularly receives lists of emails from sales representatives and wants a quicker way to import them than pasting them in one by one.
The technique is somewhat similar to splitting a list of items separated by commas, which I demonstrated in a previous lesson. That lesson covered how to break part numbers out of a single field, split them by commas, and add each as a separate record. In today's tutorial, the process is slightly different and even simpler since we can use the Split function on lines, with each line representing a separate email address.
You do not need to use a recordset for this process; instead, you can rely on a bit of SQL, so it helps if you are comfortable with how SQL works in Access.
Let's discuss the approach step by step. In your Access form, start with a large text box where you can paste your list of email addresses. Remove any control source or formatting from this box. This will serve as your input area. Add a button that you will use for importing, and optionally a text box where you can display a status message.
Once the form is ready, write the VBA code needed to process the list. Here is the approach: the contents of the box are a series of email addresses, each separated by a line break. The VB NewLine character (carriage return and line feed, or character 13 and 10) is what divides these addresses.
The Split function lets you break up that large block of text into an array, where each element is one email address from the list. For example, if there are five lines, the result is an array of five items, indexed from 0 to 4.
You should always clear the status message before beginning the operation and check whether anything was pasted into the box. If it is empty, exit without processing.
Next, use a loop to move through the array of email addresses. For each address, an SQL statement is used to append that address to the Customer table. I prefer using the Execute method of the CurrentDB object in VBA for this, rather than DoCmd.RunSQL, but either would work. The code constructs an INSERT INTO statement, inserting the current email address.
Thanks to the unique index on the email field, duplicates are automatically rejected and not added to the table again. If you do not wish to enforce this, the table settings can be changed, but avoiding duplicates is generally a good idea in a lead database.
If you are concerned about extra spaces before or after email addresses, adding the Trim function to your process will take care of this.
After running the code, you will find that each email address from your pasted list is now a new record in your Customer table – except for any duplicates, which are skipped by design. The process is fast and reliable.
In the Extended Cut of today's lesson, we take this one step further. Instead of only processing email addresses, we will learn how to also handle lines that include first and last names along with the email. We will parse each line to extract those details and insert them into the appropriate fields in the table. Additionally, you will see how to add a follow-up record for each new contact, integrating this with the contact tracking feature in the TechHelp database. While the basic database template does not fully implement follow-up functionality by default, there is a separate lesson covering that topic.
If you would like to deepen your understanding of arrays and working with them in Access, I provide a more detailed explanation in my Access Developer 21 class.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Copying a block of email addresses into a form text box
Splitting pasted text into separate lines using VB NewLine
Parsing an array of email addresses from multiline input
Checking for empty input before processing
Inserting unique email addresses into a table using SQL
Looping through array elements to add records
Handling duplicate email addresses with indexed field
Trimming whitespace from input data
Using CurrentDB.Execute to run SQL queries in VBA |
