How to Import Text (.txt) Files Into Excel - Excel VBA Tutorial - Awesome!!

hey there youtubers is Dan strong with Excel VBA it's fun thanks for tuning back in today we have a question from Anupam on down and he says that he works for maybe I better blur that out and he works for a certain company and for the Accounts Payable team and they come across lots of payment transactions they come in the form of an email and a text formed a dark txt file like from notepad as an attachment and currently they open the emails manually they copy and paste the data into Excel is there any way to import these transaction details directly from my inbox into Excel so on a pump I want to address that that's an excellent question now as for taking it directly from I don't know if you're using Outlook or what kind of email you use as for that that would depend on if your Excel can actually access that temporary directory or wherever that actual attachment is located deep down very deep down in your Outlook dot something rather you know an OST or a PST or one of those weird files that may be the that file lives who knows in Outlook I know those attachments are buried in some temporary directory or something but um but you know you can you can do some things with text files so we're gonna address that right now in fact I'm gonna go to my desktop and I'm going to open up a notepad and OGE there we go here's no-pet so let's create a text mess that message so let's say Dan comma 100% Calma and we'll say blue okay and then hit enter so that creates a new row and this is a in the event that you have a comma separated text file you might have a tab separated text file which is separated by tab keys and you can save them this way either way from Excel as well we'll do that in another video so the next one is let's see let me go up a little bit let's say let's do the column headers name comma score comma fav space color okay and then we have this next line we'll just do one more we'll say Helen and she got a 28% Oh Helen you need to study more my dear but her favorite color is green okay so now we have a text file we're gonna go to file will save as and I'm just gonna save it on my desktop make everything simpler save it on the desktop we're gonna give it a name the name of the file shall be test file come out test file dot txt hit him great save okay so we have saved a comma-separated txt file in fact I'm gonna place it right here test file dot txt if you open it up there it is again but let's go to excel so I'm opening up Excel and here we are beautiful isn't it alright so we're going to do F ah let's let's record this okay go to your developer tab if you don't have that open you don't have a Developer tab check out my basic series will show you right off the bat how to get that on there alright we're gonna record a macro why because we need to know the exact code on kind of what we're doing and how to tweak that code we're gonna go to file we're gonna go to open ok and go to the directory where you just saved your file I will go to my desktop and I look for that little text file oh no it's not there why because it says all Excel files we're gonna change we want to change that to all files or perhaps you just want to look at text files but let's just let's just to all files just for good measure and we'll fish for it it is test file dot txt here it is so when you click open if Excel is doing its job it's going to bring up this text import wizard it's freaking awesome so and that's lovely because you'll need to set your settings and assuming that you're always going to be importing the same type of text file this will be handy because this what we do here will tell Excel and in the VBA that we were recording right now it'll tell it exactly what we want it to do so we're gonna choose excuse me we're going to choose delimited meaning every single column is separated delimited and in this case it there's limited or separated by commas which makes it easy for Excel to divyam up just like in the text to columns feature that Excel has in the data ribbon so they're not a fixed-width they're delimited hit next they're not tabata limited but if they were that would be also very easy so Duke comma how about common dilemma and looky there you got name column score column in the favorite color column hit next okay so here's where we divvy up they basically tell you it's either a generic a generic or a general formatting a text or a date okay or you can skip that particular column so the first one I really don't care I could just say text but you could just leave it generic is unless it's numerals like if it's percentages or if it's numerals you want to leave it as general the first type so and also remember this okay if you can or just remember basically that general is one text is two and date is three there's a reason there yeah I'm mentioning that there in that order because whenever you look at the import features it's not gonna say general it's not gonna say text and that's a date he's gonna have like Oh a a two for this column and for this column let's say general because it's not a date and it's not text so this one would be a 1 so we got 2 comma 1 comma and that's also text so 2 comma 1 comma 2 and you'll see that in just a second let's go ahead and go to finish and if you look there what do you know we've just imported a text file and it's beautifully done here so what does that mean to you that means we're going to stop recording by clicking the stop recording in the Developer tab stop recording and let's take a look at what that just did to our text file because I think you're gonna find this pretty sweet hit alt f11 please we are now in the visual basic editor and we want to see what was just recorded and where's our crap is it did I record it in my personal one I did how bizarre well whatever I'll go ahead and see if I can cut I can I'm gonna copy that module over here and then I'll remove that module from my personal workbook no I don't want to save aka export it so no okay now that module is right here in our handy-dandy oh well we actually have the test file dot txt open as if it were a workbook so what I have to save it in a minute as a I don't know as a as an excel file but that's no problem so here we are we had first of all I I click around a lot so forget the range h13 basically selecting cell age 13 don't worry about that then we had ACH der ER the CH dir is basically changing the directory or the file path to right there to my desktop and that's okay if it's always going to be in your desktop otherwise you may have to put some variables in there and and wiggle that around but if you have an exact spot you want it to go to look for the files or if you want to do one of those get file name things that you that we can address in another video but basically once you have the file path set up whether it's dynamic or if it's static then you see this code this is the kicker right here workbooks dot open text so not workbooks not open it's a special thing here workbooks open text and then has the file name colon equals and this is the file name desktop slash test file dot txt and then the origin is 437 whatever they have heck that means just leave it I think that's like maybe a NSI standard text or something don't worry about that just leave that if you're gonna copy and paste this just leave it starting well equals one leave that data type equals Excel delimited yes it's limited and the text qualifier equals Excel double quote so if it's if it's got double quotes then it's text and you don't see it but when they're comma separated it does put quotes around the text anyway don't worry about that stuff what am i why am i rambling consecutive delimiter equals fall so basically any check marks that you checked are gonna be true and anything that you didn't check was gonna be false and you notice the only one that we did check says true which is the comma equals and that little thing just means follow on to the next line so comma equals true so we see that it's comma delimited very simple their field info equals and here's your array pattern now here remember when we were talking about this we were talking about column 1 and column two and column 3 and that one was general our excuse me one was general two was text so we see that here the array and then inside the array we have the first array for so basically the first column follows this pattern it is column 1 and here is the we want it to be a text-based remember number two is text and then basically this array thing just means the column two is column two is general and not a date which is three and not text which is two and then finally the third column as you can see right there was a text and column three it was number two which is text so that's all this stuff means so that's basically yes you can you can have a bunch of text files and if you know how to loop through files on a directory or figure out certain files that you want to get you can make that as elaborate as you want with your coding but yes you can actually open these and then you know if you watch my other videos you learn how to copy data from one workbook to another or save a new workbook when fact we just we worked on that in just a previous video or two ago for a gentleman we actually had data generate and save all these files thank you let's go ahead and do that we could have this code run in a workbook and then end with active workbook dot save or dot save as and then let's do Don save as and then we'll say file name colon equals and let's just save this directory I like that it's gonna get mad at me but I'm just gonna copy that and we'll save it as I like an excel file name how about test file dot xlsx and then we'll say and to work polka dot clothes space and it will use the Save Changes feature here : equals and we're just gonna say true that way we don't want the prompt to come up and say do you want to save changes no we want it to be automatic so let's actually just copy this macro since we have the text file open right now we kind of want to do that fresh and new let's do a file close don't save okay now let's go to file new let's do a blank workbook create okay so now let's go ahead alt F 11 we're going to in this new shiny workbook we're going to go into a new module and we're gonna paste that macro that we had just copied to the clipboard let's run through it step by step shall we so I hit f8 so now we are changing to directory to desktop that doesn't matter relate right now and then we're opening that test file dot txt and now this you might want to make dynamic take away the quotes and just put some variables in there or however you want to however you'll address put in the file name in there and then all this is already set up exactly the way we want to come separated and here's this text and general and text for column three so hit f8 it just opened up our text file right and then we do it active work to save as this might you might want to use some variables instead of hard-coding this as well but I'm going to hard-code it for now so look up here I'm gonna hit f8 and it's gonna save it as test file dot txt if you don't believe me let's just change that to super awesome text import xlsx so now I'm going to hit f8 and look up here hit f8 okay we've just saved what looked to be a text file as an excel file and it looks just like that with our columns right so active workbook close Save Changes equals true so it's not even gonna bother it's just gonna close Excel so watch this I'm gonna hit f8 and shows Lam it just closed that particular workbook and of course book two is still open but the one that we just created is saved to my desktop and hit f8 and so let's uh let's minimize all that let's look on my Oh oh look here on my desktop super awesome text import XLS X let's double click on that yeah blah blah blah well they didn't like that okay fine they're gonna be mean about it let's um okay we better we better uh figure out what happened let's troubleshoot put any thinking caps you probably are you probably already see what happened but I don't what I'm going to propose that we do then is that we run this code again and when we get right here I'm gonna go ahead and stop I'm going to record on this particular test file I'm gonna call this save as excel file because I want to know what went wrong so we have a text file open properly and now I want to save as and I want to save it on the desktop and I want to see what went wrong so I'm gonna give it that name that I wanted it to be I want it to be in xlsx file and we'll just leave it add that no now we won't I want to name it that dang it we're gonna call it super awesome text import - I'm gonna compare see what happened so I hit save now they didn't give me crap about at that time today all right so let's let's try to open that again right open the one called super awesome text import - this is the one that we did manually and remember we recorded the code just now in fact let's hit stop recording I want to view that before I open this I have a recorded right here okay fine we can do that we needed to use this active workbook dot save as file name equals same crap that I did a minute ago but and then comma file format equals Excel open XML workbook comma create backup equals false fine if they want to be picky about it fine by me so I'm gonna go ahead and copy this good stuff here at least everything after what we already did and we'll take a look at the original save as file name equals blah blah blah copy all this stuff here and now we'll try what we originally tried to do so here we go let's see let's delete these two just for good measure delete these two the failed one and a good one here and let's do it ourselves hit f8f8f8 hey there's opening the text file there saving as that workbook with all the fancy new coding that we had to add and then there's closing it and saving the changes and ending it okay now let's open it and see if they liked that a little bit better double-click on it and magically there it is and they're okay with it now well a little quirky but whatever would you look at that test file is the name of that worksheet all of a sudden so that's kind of cool alright now the final test is of course erasing this or deleting that file and what we need to do you see it Altaf 11 we need to run this we need to just hit f8 and just run the dang thing are you ready I'm hitting f8 I can't watch no I'm just kidding hey that excuse me hit f5 ready BAM alright opened it saved it closed it let's check it out yeah there it is so to answer your question I hope that answers your question on a pump but basically you can have just set up the rules and the columns and everything that you want and then yes you can actually loop through all the files in a certain folder that you saved all your attachments to or whatever but it's advisable to use Excel to GN to do that you can do that in fact if you want to open the first text file and then you know you can have a loop through there and and have it basically copy or filter the information that you want from that into another master excel workbook and just keep adding rows and then automatically close that txt file you go to the next one that you want it to loop or whatever or you can just have a thing like a file open gosh dang it you know what I ought to show you all right if you're if you're bored turn off this video but basically I want to show you and right before anything else happens I need to do a file save as I need to save this workbook as an Excel macro enabled workbook and we're going to call this one import text files to excel so if you're looking for that workbook it is available for free in my Dropbox in my downloads section in my excel vba shared workbooks you'll see the link to that in my video description so I'm saving this that way we don't lose all our code that we've been working on you can download that right now and you can see all the code for yourself and you can of course maybe the name of your desktop would probably not be see users Daniel desktop you'd want to replace that with whatever your actual desktop directory is that being said and of course you'll have to put a test file dot txt on your desktop but anyway now I was gonna I wanted to show you how to get an Open File thingymajig err so gosh that sounds very technical doesn't it here real just check this out when I say application dot get open file name so I'm gonna hit tab and let's see I think I just need to I don't have any filters I don't want to put any particular title but you that we can go into more on that in another another video so just do it the quotes here you know why they gave me crap about that is because I need a variable so let's say F name equals so it's gonna be my file name variable F name equals application get open file name and then I'm gonna put a stop marker after that part wrote runs so I'm gonna hit f5 and you're gonna see what happens it's going to prompt me to select a file and when it does after I select that file it's gonna stop so that we can take a look at this macro the reason I'm showing this because we're gonna place replace this file with whatever F name is at the time that you select it and then we'll just shove F name right here and then the rest will open whatever random text file that you decide open or you can even do a loop with this or something so that's how we roll let's do it hit f5 okay so you see what happened there when the macro started running it opened up a prompt and it changed the directory to my desktop and it asked me for just select a file and I'm gonna say this one so I'm gonna either double click or I'm gonna click it and hit open so I will double click because I like to do that and you see if you hover over F name F name equals C users Daniel desktop slash test file dot txt that's the very one we've been playing with right so rather than using this hard-coded crap here let's just make that F name like we told it to be called and if you hover over that you see the file name that's going to open is that one so if he had f8 you see that it opened up F name which is the test file and so hit f5 it's gonna say there's already one called that do you want to replace it sure why not so anyway we just did the same same thing that we worked on but now it's a little more dynamic now we can make this and we can even you know you can have the name of this file that's saved be a dynamic as well of course anything that has a quote can also be replaced with the variable so you could have like the date and combine that with whatever a certain location or a certain place you know whatever anyway this I'm gonna save my what I just did as you can see right there it is updating your Dropbox file so check out the workbook if you want to download it it's in my Dropbox folder and it's called import text files to excel dot XLS M so be sure and check that out I'll be sure and hit subscribe if you'll want to get more awesome videos about excel vba and all the fun toys and all the fun tricks you can do and be sure and like this video by clicking the like button below if you liked it if you don't like it you sure don't have to and if you have any questions or comments please feel free to message me or type them in your comments below anyways staying strong with excel vba signing out on a Palma hope that helped you hope that helps a bunch of people so [Music] [Music] [Music] [Music]

Channel: ExcelVbaIsFun

Views: 196,752

Rating: 4.6352201 out of 5

Keywords: import text file, import text files into excel, excel text, excel txt, macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, free, tutorial, lesson, hd, training, how to, data, text, txt, message, txt file

Id: SwN5vtYzLg8

Length: 28min 43sec (1723 seconds)

Published: Sat Aug 31 2013