|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Import Multiline Cells By Richard Rost 
5 years ago
Import Data from Excel with Multiline Cells In this video, I will show you how to import data into Microsoft Access from Excel that contains multiline data (cells with line breaks in them). Dean from Gaylord, Michigan (a Platinum Member) asks: we've been storing our membership info in Excel for years. With your videos, I finally feel confident enough to bring this all into Access. Problem is, one of my sheets has the primary member's info (name, address, phone, etc.) and all of his family members are listed in a single cell with line breaks (ALT-ENTER). When I bring that into Access, the names are all smashed together. Is there any way to fix this? MembersMembers will learn how to take those multi-line cells and actually create separate related records in a different table for each of the items in that cell.
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!
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, import, excel, line breaks, alt-enter, How to import from Excel and keep the line breaks, Import with line breaks in Access, How to import from Excel and keep the line breaks, import multiline text from excel, excel multiple lines in one cell to multiple rows, chr(13), chr(10), vbNewLine, CR/LF
IntroIn this video, I will show you how to import data from Microsoft Excel into Microsoft Access when your Excel cells contain multiple lines separated by line breaks. We will look at the differences between how Excel and Access store these line breaks, and I will demonstrate how to use an update query with the Replace function in Access to correctly convert single line feed characters into the carriage return-line feed pairs that Access expects, so your multi-line data displays properly after import.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to import data into Microsoft Access from Excel that contains multi-line data in cells with line breaks in them. Today's question comes from Dean in Gaylord, Michigan, one of my platinum members. Dean says we have been storing our membership info in Excel for years. With your videos, I finally feel confident enough to bring all this into Access. Problem is, one of my sheets has the primary member's info - name, address, phone, etc. All of his family members are listed in a single cell with line breaks. That is when you press Alt-Enter in Excel to get a new line. When I bring that into Access, the names are all smashed together. Is there any way to fix this? Yes, Dean, I can teach you how to fix this, but first, let me explain how Excel and Access are different in the way they store this kind of data. So let's say you have a spreadsheet in Excel. You have your member ID, first name, last name, phone number, and then you have your family members over here. You get this by typing in that info and then pressing Alt-Enter, which gives you a blank new line. What happens is Excel puts a line feed character there. You cannot see it; it does not have a little indicator or anything, I just put "LF" there to show you. But there is a line feed character there (character ten, it is called). Everywhere you press Alt-Enter, there is a hidden line feed character. When you import this into Access, it is going to look like that. Access does not deal well with just that line feed character. In Access, you can press Ctrl-Enter to get a new line, but Access uses a line feed carriage return pair. It is called a new line character. Actually, it is two characters together: character 13 and character 10. Access needs that carriage return (CR) and then that line feed character as well. What we have to do is, once we import the data into Access, we then have to say everywhere you find just a line feed character, we are going to change that into a carriage return line feed. We will put both of them in there. It is a little complicated, but I am going to show you how to do it. First, some prerequisites. You should know how to import data, of course. You should know how to use an update query. We are going to have to use an update query to change the data in that field. We are going to use the Replace function to replace one character with something else. I have three videos on how to do all three of these things. If you are not sure about any of these, go watch those videos. They are free. The links are on my website. Go watch them and then come back here after you have watched those. Here is my sheet in Excel. It is real simple. If you want to add someone else, you just come down to the end here and you press Alt-Enter. You put somebody else in there like that, and now you have another entry. I am going to save this, and let's go over to Access and import the sheet. Here I am in my TechHelp free template. This is a free database. You can download it on my website if you want to. You will find the links in the link section down below. I am going to go to External Data - New Data Source - From File - Excel. If you watched my import video, you know how to do this. We are going to import the source data into a new table. Click Browse. I am going to browse to where my data is. It is on my Google Drive, in my spreadsheets folder, and there it is right there. Click Open, then OK. In this case, our first row does contain our column headings. Click Next. We are going to import our member ID from the spreadsheet. We are going to make that a long integer. You can see there are the first name, last name, phone number, family members fields. If you want to make that field Long Text (if you have more info, then just simple names like I have), if you have comments or a history of some kind, you might want to make that Long Text. It does not really matter for this example. As you can see right here, Access is already ignoring the basic line feed character. Click Next again. Now, in this case, Access is going to add an ID. That is fine. An AutoNumber should not be whatever this member ID is. You can keep your member ID as a separate field if you want, but let Access add the primary key for you. Next, what do you want to import this as? I will call this my member table. Click Finish. We do not need to save the import steps. All right, let's take a look at it. MemberT is right there. There we go. We do not need that blank record. Sometimes a blank record happens at the end. Here are my family members over here. If you come into one of these fields like that, you will see there are some extra characters on the end. If you zoom in with Shift+F2, look at that. The zoom window recognizes just that line feed character. If you use backspace and change this at all, then click OK, it fixes it for you. But you obviously do not want to have to go down through all of these records and change that manually. I am going to undo that and put it back the way it was. We are going to use an update query to fix this. We are going to say everywhere you find just a line feed character, we are going to replace it with a carriage return line feed. Let's make a query. Go to Create - Query Design. I will bring in that member table. Let's just bring in family members, and over here, let's make a temporary field. We will make a calculated query field just to show what it is going to look like when it is fixed. I am going to zoom in and make this a little bit smaller. We will call it X. We are going to use the Replace function, so we are going to replace inside the field family members. What are we looking for? We are looking for a line feed. Line feed is Chr(10). We are going to replace that with a carriage return line feed pair that looks like this: Chr(13) and Chr(10). That is, find all line feeds and replace them with carriage return line feeds. Click OK. I am going to save this now. We are going to go FixMemberT. Actually, let's call it FixMemberQ since this is a query. Now, if I run this, you can see what it is going to look like after you run the query. See that? That is the result we want. But I do not want this just in the query. I want to fix the table. So what we are going to do is go into MemberT. Let's close this. Let's go into MemberT - Design View. Let's make a Family2 field. I will make that Long Text as well. We are going to write that one into that one with an update query. Right now it is empty. Let's go back to our query - FixMember - Design View. Now I am going to come in here and cut this part right there. That is the part we want. Cut that out and get rid of that. We are going to change this to an update query. The field Family2: I want to update it to that calculated value that we created. Take that and put it in Family2. Let's save the query. Now I am going to run it. Nothing appears to happen. I have my warnings turned off. If you watched my Beginning TechHelp blank template videos, I show you how to turn all those warnings off. If not, you might see something like "You are about to update six records" or whatever. Now let's go take a look at our table. There we go. That Family2 field is now fixed. Now that is how you want it to look in Access. If you want to, you can go back and delete the original Family Members field. Go to Design View and delete Family Members, then, if you want, rename this one. That is up to you. If you have forms and reports and stuff based on that Family Members field, adjust those as needed. The reason why I use a separate field is because if I make some kind of mistake, I have still got the original data and I can just run the query again. I do not have to go back and import it from Excel all over again. So, I put it in a second temporary field and then, once everything looks good, I delete the original field. That is how you import multi-line data from Excel. Now, here is the next step. Here is the next question you have to ask yourself. What if you want to store this properly in your database so that each one of these family members is a related record? You have got the primary member here, and here are all their family members. The right way to store that in an Access database is to have a separate Family Members table. Each one of these people should get their own record in that related table. That is the proper way to do it in a relational database. How do you do that? I will show you in the Extended Cut for the members. In the Extended Cut for the members, I will teach you how to take all that data that is sitting in a notes field that should be individual, separate records in a related table. That is the whole reason you are using Access in the first place - it is a relational database. If you have a person with their five kids, each one of those kids should be in a separate, related record in a different related table. I will show you how to do that. In the Extended Cut for members, I will show you how to use a recordset to loop through the members, peel off each one of those records that should be in a separate table from that notes field. We will use a little SQL and a little recordset. The extended cut is 16 minutes long, available to Silver members and up. You get access to all of my Extended Cut videos. Gold members can download all my databases from these videos. Sign up today. How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my Extended Cut TechHelp videos, one free Beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you have finished the Beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you have finished the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free. QuizQ1. What is the main issue encountered when importing multi-line data from Excel into Access?A. Access adds extra rows for each line break B. Access cannot read any Excel files C. Line breaks entered in Excel are stored as only a line feed, which Access does not interpret correctly D. Access splits the data into multiple tables automatically Q2. In Excel, which keyboard shortcut is used to create a new line within a cell? A. Ctrl-Enter B. Shift-Enter C. Alt-Enter D. Tab Q3. What does Excel insert when you press Alt-Enter to create a new line in a cell? A. Carriage return character (Chr(13)) B. Line feed character (Chr(10)) C. Tab character D. Space character Q4. How does Access typically store a new line in a long text field? A. Only a line feed character (Chr(10)) B. Only a carriage return character (Chr(13)) C. Both carriage return (Chr(13)) and line feed (Chr(10)) characters together D. A tab and a line feed Q5. Which Access function is used to replace one character with another in a string? A. Concat B. Replace C. Find D. Substitute Q6. What does the update query in this tutorial do? A. Deletes all data after a line feed B. Replaces all line feed characters in a field with carriage return and line feed pairs C. Removes duplicate records D. Converts all text to uppercase Q7. Why does the instructor recommend creating a new field (e.g., Family2) before replacing the values? A. To keep the original data safe in case of mistakes B. Because Access requires two fields for text updates C. So that queries can run faster D. To avoid splitting the table Q8. After converting line feeds to carriage return + line feed in Access, what should you do if you are satisfied with the result? A. Delete all records and start over B. Leave both old and new fields in the table C. Delete the original field and optionally rename the new one D. Export the data back to Excel Q9. What is considered the best practice for storing related family members in Access? A. All family members in one text field with line breaks B. Each family member as a separate related record in a related table C. Each family member on a new worksheet in Excel D. Storing only the primary member, ignoring others Q10. What tool or feature would you use in Access to automate replacing characters in all rows of a table? A. Input Mask B. Validation Rule C. Update Query D. Combo Box Q11. What keyboard shortcut lets you zoom into a long text field in Access for easier editing? A. Ctrl+F2 B. Shift+F2 C. Alt+F2 D. Ctrl+Shift+Z Q12. Which function is specifically mentioned as being used in the query to replace characters? A. Substitute B. Concatenate C. Replace D. Mid Q13. According to the video, what is the ASCII code for the line feed character? A. 13 B. 9 C. 32 D. 10 Q14. Why does Access sometimes ignore the line feed character from Excel during import? A. Access reads only numbers in text fields B. Access only imports field names C. Access expects a carriage return and line feed pair, not just a line feed D. Access converts all special characters to spaces Q15. What type of field should the Family2 field be if you might need to store more than just names? A. Short Text B. Number C. Long Text D. Date/Time Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-B; 7-A; 8-C; 9-B; 10-C; 11-B; 12-C; 13-D; 14-C; 15-C 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. SummaryToday's video from Access Learning Zone covers how to import data from Excel into Microsoft Access when your Excel data contains multi-line entries within single cells, created using line breaks.A common issue arises when you've used Excel to store information like membership lists that include primary member details and also list several family members within one cell, separated by pressing Alt-Enter. When you bring this type of data into Access, all those names often end up appearing jumbled together, making it hard to differentiate or use that information effectively. This is due to the way Excel and Access interpret line breaks differently. In Excel, each time you use Alt-Enter to create a new line within a cell, you are actually inserting what's known as a line feed character (ASCII character 10). However, Access expects a pair of characters for a proper new line: a carriage return followed by a line feed (characters 13 and 10). When you simply import the Excel data as-is into Access, Access doesn't handle the single line feed properly, resulting in your data getting mashed together. Here's how to address that: After you import your data from Excel into Access, you need to convert every single line feed character in those cells into the correct combination of carriage return and line feed. This requires running an update query that locates each line feed (Chr(10)) and replaces it with the carriage return-line feed combination (Chr(13) & Chr(10)). Before you start, you should already be familiar with importing data into Access, running update queries, and using the Replace function in Access. If any of these areas are not clear, I recommend watching my foundational videos that walk you through each step; links to those videos can be found on my website. For the example in this video, I demonstrate using a simple Excel sheet. If you need to add additional entries to a cell, remember to use Alt-Enter in Excel, which adds a new line feed. Once the data is ready, you import it into Access using External Data options and follow the typical import steps, ensuring that your fields match up as needed. For the family members, if the information is more complex than just names, consider using the Long Text field type in Access. After importing, you may notice that when you examine the multiline field in Access, especially in the zoom window (Shift+F2), Access will show only the line feeds. While you could fix these manually, it's far more efficient to automate this with an update query. Here's the general process: - Create a new Long Text field in your table (for example, Family2) as a temporary storage area for the corrected data. - Use a query with the Replace function to convert each occurrence of the single line feed into a carriage return-line feed pair. - Run the update query to populate Family2 with the fixed data. - After verifying that everything looks correct, you can delete the original problematic field and, if you like, rename the new one as needed. Having a temporary field is helpful in case you make an error and need to rerun the update, so you can preserve the original data until everything is finalized. While this resolves how the data appears in Access, for better relational database design, you should consider splitting those multiline entries into individual records in a related family members table. This setup lets you store each family member as a separate record connected to the primary member, which aligns with best practices in Access database design. In the Extended Cut for members, I walk through the process of extracting each family member from a single multiline field and creating separate records in a related table. This involves using recordsets and a bit of SQL to scan through the data and insert each individual entry appropriately. If you're interested in accessing the Extended Cut, as well as other member benefits like downloadable databases, monthly lessons, and higher question priority, consider joining as a Silver, Gold, or Platinum member. Each membership level offers its own set of perks, detailed on my website. As always, these TechHelp videos will remain free, and I'll continue to produce more as long as viewers find them useful. 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 ListHow Excel stores multi-line data with line breaksHow Access handles line breaks differently from Excel Importing Excel data with multi-line cells into Access Identifying line feed characters in imported data Using Shift+F2 Zoom Window to view multi-line fields Creating a calculated field in a query with the Replace function Replacing line feed (Chr(10)) with carriage return-line feed (Chr(13) & Chr(10)) in a query Creating a new Long Text field to store the corrected data Building and running an update query to fix line breaks Verifying updated records in the Access table Maintaining original data by using a temporary field Deleting and renaming fields after confirming data conversion |
||||
|
| |||
| Keywords: TechHelp Access import, excel, line breaks, alt-enter, How to import from Excel and keep the line breaks, Import with line breaks in Access, How to import from Excel and keep the line breaks, import multiline text from excel, excel multiple lines in one c PermaLink Import Multiline Cells in Microsoft Access |
