Automating Importing Multiple CSV Text Files - VBA Excel 2010

hello everybody in this feed I'm going to give a demonstration on how on how you can automate importing CSV files into Microsoft Excel now this topic can be pretty lengthy so I'm going to try to keep it as minimal and as brief as possible so I have already done a lot of the lightboard for this application so to begin I already have three files set up CSV located in my root directory Alex and then data feed load additionally I've already created an admin tab where I have the files that I want to pull now in this case you want to list out the files that you're going to want to be pulling and just so we can make sure that you're actually watching the video that you want in this case importing CSV files let me show you what this application is going to do that way we can see this video is right for you I'm gonna go to my data feeds tab and I'm going to run my macro - import all files all I want this macro to do is load up these three files into my workbook so I'm going to run that file so you can see it in action it's a pretty quick task and all my macro did was it stored these three files to my data feeds tab based on the row number that I gave it to output to so we have one of the files starting an a1 one of them starting in a 10,000 and then starting in a 20,000 so to give the demonstration if I go to a 10,000 we'll see that there's one another import right here and if we go to a 20,000 we'll see this is where the other import is so this is what you're looking for then we've come across the right video so you can get a demonstration on how to achieve this so the first thing that you're going to want to do is create an admin tab such as the one I've done right here list out the files that you want imported and also lists the output sheet name that you want to go to in this case my sheet name is data feeds which is what the name is right here and then the row number I'm going to show you the code that it took to import all three of those files the code that you're going to want to pay most attention to is this specific portion this was done though by the VBA recorder and I'm going to show you how you can how you can get that code pretty quickly I'm going to create a new sheet real quick I'm just going to call it testing I'm going to record macro in my Developer tab and I'm going to type call this import one file so it's recording right now we're to go to data from text we're going to import this file I'm going to mine is a delimited file it's tab delimited so I'm going to going to hit next and then finish you want to select your output range mine is going to be a 1 and select ok and then now I'm going to start recording so now I'm go to my macros and do import one file which is the macro I just created and the macro recorder was nice enough to create this wonderful piece of code for us that saves a lot of time can tell you for the most part you want to keep everything the same and I don't want to give a lot of instruction here because your file is going to be different than the file that I chose the kind of CSV file that I chose but based on your file I mean you're welcome to tweak with these settings with the CSV file that I have used I know I can get rid of this attribute I can get rid of these this value we don't need the name everything else here I'm going to keep the same the one string I'm going to want to work with and modify is this piece right here this section tells us what file we're going to be loading and then our destination where do we want it to to populate so once you have your code what you want to do is create another macro and you can actually do it from here at the very end of this code after n sub just type in sub and then the name of your procedure in this case you can call it we're going to call it test underscore import all files and just do an open parentheses close parentheses and hit enter the moment you do that if you did it correctly you should get an N sub that was populated and you want to copy this code over and place it in here now for the sake of this video I am going to go ahead and go to my other module where I had already worked on this code this is pretty much what the macro recorder created but I modified it a little bit because this video can be very lengthy if we go step by step on how we wrote this code I'm just going to go in and copy and paste this code right here instead of recreating it again and I'm going to show you what we did I put four rep is equal to four to six if you're not familiar with a for statement all it is is a loop that goes from one range or one number then it counts up and I'm going to show you why I did that on my admin tab my information starts at row number four and then it ends at six however if I had three more files here I would want to go from four to nine I'm just going to kind of expand this down my 4 rep would change to be 4 rep is equal to 4 to 9 in other words it's going to be reading each row starting at row number 4 going down to 9 and we have to close out that 4 statement now you can ignore this for right now if let me just show you and get started with the code as mentioned when I did the macro recorder I explained that the main line that we're going to be working with in changing is the connection and that's this string right here what you want to do is modify your code so that you can assign some variables and make your life a little bit easier that way you have your files list it out on your workbook rather than then your source code because if you leave your file name in here you're going to have to be going back to the to the VBA code and have you want to make changes to your file name that to me can be a little bit time-consuming I would much rather go to an admin page where I can easily change the files if I'd like and then I don't have to go back and change the VBA code so that's what this does right here I set this up so that we're looking at the file name variable that I create up here so I have 3 variables and I'll explain what those variables are before I write those variables though I have to declare what how the data is going to be stored in this case I put dim file name as string row number as string and output sheet as string the file name is equal to sheets adamant the atom in sheet range B so it's already looking at column beam but and rep which is the row number in this case it starts at 4 because it's a for statement and it's going to loop it's going to the rep will be 4 and then 5 and then 6 since this will be the first time that the rep is executed it will be 4 so this is looking at B 4 before is the file name which will be right here then we have the output sheet that's c4 and then lastly the row number D and then rep again is four all this is is storing that information as variables file name is going to be placed right here output sheet is listed over here and then the row number listed right here we always store the data starting at column a we do not change this we keep it as a static value all of this code right here is going to import all of your CSV file or whatever file you are using that's all it does however there's one disadvantage here if you notice it's query tables dot add the moment you run this code in your Excel workbook you're going to build up a lot of connections and they'll all be listed in your workbook connections this list will grow and grow and grow as you continue to use this code I don't like my workbooks getting messy try to keep them as clean as possible so what I do once the data is imported I delete the connection so this portion I will discuss this in another video I won't get too into detail with it but all this does it loops through every workbook connection that's in our workbook if the name of the connection includes up the file name then it deletes it this way once this is completed it's going to create a connection this piece is going to delete it this code is entirely optional you can actually keep your code as is and not have this portion however I do this because I want my workbook connectionless to be empty and then we have the next rep the message box done that's not necessary this is just notifying the user that actions have been completed but this is pretty much all the code that that you'll need it's all listed out in front of your screen I'm just kind of delete here and then of course lastly message box done that is pretty much all it takes to automate importing CSV files all the code that you need is listed right here in front of you this portion again you want to use your VBA macro recorder to populate this code don't make any changes to it with the exception of your connection you want to change it out to the variables that I use right here and make sure you put your variables in your code if you're unable to read this code make sure you're watching the video in 720p anything less than that you won't be able to see this code properly on your screen that now concludes this video you should be able to import all of your CSV files in a much faster fashion if you have any questions feel free to post thank you for watching

Channel: Alex C

Views: 90,802

Rating: 4.8005247 out of 5

Keywords: Microsoft Excel (Software), Text File (File Format Genre), Comma-Separated Values (File Format), Visual Basic For Applications, Management, Technology, Software (album)

Id: sQIImQbEO_Q

Length: 11min 57sec (717 seconds)

Published: Fri Feb 06 2015