How To Import CSV and Other Character Delimited Files Into Access 2013

hello everyone my name is Steve bishop from programming made easy calm and today we're going to be talking about how to import character delimited text files so this could be anything from a comma separated value to a tab delimited value or even a pipe delimited value which is the example that I'm going to show you in this video now access has some very robust importing functionality that comes right out of the box and we're going to be utilizing it but we're also going to be doing some VBA code behind some forms that we're gonna build to make a nice user experience for how they can import these text files all by themselves that being said since we are going to be using VBA code if you do not know VBA you're going to get lost while you watch this video so I would highly recommend that if you do not know VBA code did you go watch my programming in Access 2013 video series so you can get caught up with VBA code the last thing I want to mention here before we jump into the demonstration is that I'm gonna do this how-to video a little differently than my others in previous how-to videos I really just kind of showed you a straight and narrow path of how to create something step-by-step in this video I kind of want to show you a little bit about some of the decision-making that goes behind some of the choices that I will make so let's go ahead and hop into our access application and let's get started creating our import processes so to just give you a quick little layout of the land here we've got a very simple database with a customers table and our customer table has an ID field a customer name address line 1 and line 2 city state zip code and phone number then we have two different text files one of them is a comma separated value file and another one is a pipe delimited value so the comma separated value file looks very familiar I'm sure it has the first row is the actual column name each column name is separated with a comma and then each row after that or each line after that contains the next record of data down here we have essentially the same thing but we're using pipes we're using these street button-down lines which are called pipes to delimit each one of the values in our record now something else I want to point out about this about these two files is that you'll notice that we're using line 1 to denote that this is the first line of our address whereas in our table we're saying address line 1 same thing for line 2 we say line 2 versus address line 2 same thing with zip code right it says zip and then down here it says zip code and this is a very common thing that you'll run into perhaps the format of the file that you get will have a different structure a different column names for the data that's in them and you're going to need to create some sort of import process that knows how to map these column names to the column names within your table now all this is actually built right in to access this ability to import a text file a comma separated value file and then realign or remap these column names so let's go ahead and get started with that I need to close out of the customers table to get to do this though so we'll do that and then we'll go to external data and you'll see that there is this text file button and now from here I'm gonna click on the Browse button and I'm gonna select a customer batch one and we're gonna select open and then we're gonna say append a copy of the records to the table and the table that we want to append to is just our customers table that's fine let's go to click OK and we're gonna use the delimited we're not using fixed-width we're using delimited so a character such as a comma separates each field we'll go ahead and click on next and we could see that the default is comma and our file our customer batch one is in fact a comma separated value list so that's perfectly fine but you'll notice that we have in the data here we have quotation marks as part of the data that wants to import and also it's wanting to import that first line as data as well so we need to resolve that by clicking on this first row contains field names and you'll see that that moves that first line up to be the actual names of our fields now as for these quotation marks inside of our data we can select this text qualifier and that means that the text inside of those is wrapped inside of quotation marks so we could use a simple apostrophe or we can use quotation marks to indicate in those CSV files that the values any text values are actually enclose enclosed with some quotation marks so that's going to fix that and now we can go ahead and click on next and we can go ahead and finish this said field line one doesn't exist in destination table customers uh-oh let's fix this okay so I'm gonna go back here and I'm gonna select on the advanced button down here where we have the field information this is where we can actually change the field names to match what we have in our table so I'm gonna say instead of line one we're gonna say address line one and that is the name of the column in our table so let's do that for address line to city state and this was zip code instead of zip and this was phone number instead of just a phone let's go ahead and click OK and finish and we didn't get any sort of errors so it looks like that data imported perfectly now what about our pipe delimited values so let's try that one we'll go to text file will do browse customer batch to open append a copy of the records to the customers table looks like I've got my table open I need to cancel this here and close oh it did it for me automatically so let's start over again here text file customer batch to append to customers ok we're gonna do delimited okay so we could see the pipes down here it's still a delimited file because we are delimiting it with some sort of character I'm gonna select next and here we can say other and in the other field we can put the pipe and we still have the first row contains field names and the text qualifier is the quotation marks will click on next and finish and uh-oh we forgot to do that let's go ahead and fix our mappings so address line 1 ok now let's see what happens when we click on finish unable to append the data uh-oh he violations Oh No we don't want to do that what's going on there well if we look at the ID field we can see we have an ID of one and two and that's because we had an ID field of one and two here for our customer name for our customer batch 1 dot txt but then when we tried to import the pipe delimited values we had an ID of one and two hmm so we should probably fix this now I would suggest that since this is an auto numbered field we probably don't need to be specifying what the ID is or import that ID value from here so we probably don't even need it right we can just skip over it so what I'm gonna do then is for this importing process of the second batch file we will append to the customers will select customer batch - ok delimited next use the other pipe first row contains field names quotation marks let's go to the Advanced section here and we need to fix these field names again and I don't need to import the IDS because it's gonna be Auto numbered so I'm just gonna skip that click ok next finish voila no errors now if we open up our customers table perfect so now we're getting all four records for our customers table so that's great I don't know that necessarily you want each one of your users to have to go through this text file import process because they're not going to necessarily understand all this so maybe we want to create some programmability write some sort of functionality that automatically does this for us and I'm gonna go ahead and delete all the records that we have in the table currently so that we can redo this process but we're gonna do it through code so let's go ahead and create a new form and I like to start with the form because it gives me kind of a blank whiteboard of what I want the user experience to look like and then from the user experience I can draw out how the code is supposed to work and I'm gonna kind of go through that process with you kind of show you some of the techniques that I use in order to kind of divine out what it is that I need to do for the code and so we're gonna kind of go through this whole experience together the first thing I want to do is create a user interface that I like so we'll do that we'll do file to import because this is going to be a text box that's going to contain the full path to the file that I want to import and I also like to use black text here so I'm going to change the font color to black and sure the user could probably just type in the full path in the file name but that's probably not the best experience so we probably want to go ahead and add a button here that's gonna allow the user to open a file dialog window that the user can select a file so I'm gonna go ahead and change this to browse that's a pretty common word for what it is we're talking about where the user can browse their hard drive and find a file and let's go and change this to BTN browse so that's the new name of the button and for a text box will do txt file to import actually what should you file me yeah file name that's good and then that okay that's gonna allow the user to browse and select a file and put it in there but now we need to actually add some sort of button that's going to do the actual importing process so let's go and add this now import the file and we'll just call this button BTN import file alright so let's see how this looks let's save the form we'll call this import customer file and let's see what this looks like okay well I don't like the record selector on the Left I don't like the navigation buttons and I don't like the title import customer file that's probably not good so let's fix all this up let's go to the form here change the format record selectors let's get rid of those navigation buttons get rid of those and the caption should probably read import customer file save that click run that's okay import customer file looks good file the import browse button import file button obviously nothing works yet but this is looking a little bit better maybe something else to do is make it a pop-up and even make it modal and of course if I do that we get this silly little gray bar down here I don't know why they haven't fixed that yet oh well you fix that though is you can go down here to the scroll bars there we go change this from both to neither and now voila import customer file this looks pretty good no this this may change over time depending upon the needs that we encounter but this looks pretty good I think I think we could work with this so let's go back to the design view now I haven't coded anything yet so let's go ahead and click on the Browse button and go to the event tab on click code builder okay now rather than just jumping right into creating some code what I typically like to do is just kind of think about what I want this function to be what do I want to have happen after the the Browse button has been clicked so I'm gonna do a little comment here and say to do and I'm gonna say Open File dialog window and set the txt file name text-box to the value so I'm gonna open up a file dialog window and then set the value that's been selected from that file dialog window to the text file name so that's what I've got going on behind that browse button what about the import file button well let's go to the on click event for that code builder and to do let's see well we could say sure just you know import file entered in txt file name text-box but if we're gonna look a little bit more into the future of what's gonna need to be what what's gonna be required we could probably discern that there needs to be some sort of selection process of which one of those processes right we went through two different processes to import these text files one that imported the comma separated values and one that imported the pipe delimited values so we actually have two separate processes that we're gonna have to go through so let's do select import process then import the file entered into the text file name now we don't really know how this selection process is going to work we don't know how we're gonna select the import process we just know that we need to at some point and I think that fairly well covers it maybe we could say then import file entered into text box to customers table that's probably good to be more specific there in our to do and this would be good just in case that you know maybe we're not the ones that actually code this out maybe we hand this off to some other coder and they're gonna see this to do and then they're gonna know what it is that they need to do behind this on click event as I'm reading this to do I can see that this is actually two different functions one that opens the file dialog window and we're setting the text file name text-box to the value so really there's two things going on here behind that click event and even when we read down here it's two separate events here as well right because open the file dialog window and set the TEC the the text file name text-box to that value and then here we have select import process then import file entered into text file name text-box to customers table so there's two things that this button is going to do and there's two things that this button is going to do as a rule you don't really want your subroutines or functions to be concerned with more with doing more than just one thing so we really want to split each one of these things up into separate functions so I'm gonna go ahead and create a new public function down here below our you know in our import character delimited files form and this function is going to actually open a file dialog window so we're gonna say open file dialog now I don't need to add the window that's a little extra so we'll say public function Open File dialog okay and we know that a file dialog is going to return back some sort of string which is the full path to the file name that's been selected so I could do another to do here as well but you know I'm just gonna go ahead and code it out now what's this open file dialog functions supposed to do well it's supposed to open up file dialog so let's go ahead and dim a diag as a new file dialog now I got the intellisense on that file dialog because I already previously went ahead and with the tools and references and selected the Microsoft Office 15 point Oh object library if I didn't have this selected and I just went dim diag as a file dialog you'll see that it's missing so to get that once again tools references scroll down here to Microsoft Office 15 point Oh object library now your version number may not be 15 it may be 14 or 16 or something else depending upon which version of access or an office you have installed in your app on your computer but my version is 15 point a so we'll just go ahead and leave that will say dim diag as file dialog now dim diag as file dialog we should probably go ahead and set diag equal to and where we open a file dialog is from application dot file dialog and I don't know why this probably needs to be fixed you can see the file dialog looks like a property based upon this you know that this icon here it says that it's a property but it's really not a property it's actually a method it should have one of these green icons next to it and I don't know if they're gonna ever fix that but it is a method and this method you need to pass into it some sort of MSO file dialog type and that's going to be one of these four different options for a new MSO file dialog file picker and you can see that the file dialog method returns a type of file dialogue back so this file dialog type that it returns matches the file dialog type that we set for our Dayak object now we want to set some properties of this diag we want to say allow multi-select is probably going to be false because we really want them to be importing one file at a time and then diag title let's change the title and we could do import or select file to import but you know what one of the things I really don't like doing is setting a bunch of properties on things because this is just keep getting longer right diag dots you know I'd rather actually in order to make this a bit more modular I mean setting it hard-coded here is probably not a good idea and sitting at hard-coded value there is not good I should probably be passing these in as parameters to the Open File dialog method here or function so I'm gonna say allow multi-select as bullion and then we'll just say L a multi-select and whatever gets passed into the function it's gonna be set there for that and then title you know what I'm gonna put title in front of the allow multi-select so we'll do title as string and now we could just do title so it's gonna be up to whoever calls the foie dialogue method to determine whether or not it's allowing multi-select and what the title is going to be I let's see what else do we need to put in here so diag filters we need to work with the filters because we actually want to clear out the defaults we don't we don't want the user to be able to select just any old file we want them to select either CSV or txt files so we're gonna clear out the defaults and then we're gonna say diag filters add and okay so we need to add a description and an extensions as string but we really kind of want this to also be some parameters that get passed in but it doesn't really make sense to do you know description as string and the filters or the you know the extensions as string up here because really we're starting to get a lot of parameters that need to be passed into this thing in order to make it work so as a typical rule of thumb you should never have more than three parameters in a function call and really three is pushing the limits but I'm good with working with three to do this what I'm going to do is since really both of these two things are maybe passed along to the same ad method I'm gonna create my own class that's gonna contain these two values so I'm gonna do right click insert class module and this is going to be a public and description all right that was one of the strings that we needed to do was as a string and then public and if we look back here was extensions as string and in fact I'm just gonna copy that yes yes okay so I'm gonna save this and I'm gonna say I say dialog filters or filter okay all right I like that so dialog filter now I have a dialog filter class that is really up to the user to create and pass along so I'm gonna say let's just get rid of both of these and we'll do dialog filter has dialog filter okay and now what we can do is whatever the user passes along into this dialog filter we can just grab the various properties of it right it's a dialog filter dot description that fulfills the need for the description parameter and this add method and then dialog filter extensions so now we're just leaving it up to whoever is making the call to this file dialog method to fill in the title the allow multi-select and whatever the dialog filter is gonna be let's go ahead and if diag Chau and the show method will return either a 0 or a negative 1 and a negative 1 is actually a true result 0 is false negative 1 is true and the show method is going to return true or false based upon whether or not the user selected any files at all so if the user did select a file then diag chose should return true so if true then and we'll do our end if if the diag show is true then we want to go ahead and return back the value that's been selected now to get the value that's been selected we need to actually do a for each loop for each item in in diag dot selected items and even though there's only one of them we have to do this iteration so that's okay now I haven't declared item yet so let's go ahead and do that now we'll dim it up here dim item as variant and now I'm going to set open file dialog equal to and I'm just gonna be very explicit about this I like to do C string I'm gonna convert this item to a string so that's great if the user does select something what about the sad path they call this the sad path what if the user doesn't do what you want them to well this is gonna return false so we need some sort of else statement what do we want to do if it's else do we just want to return a blank string well we could do that but that doesn't seem quite right either maybe what we want to do is pass back something a little bit more elaborate than just a string maybe something that tells the whoever called it whether or not the file dialog was successfully open and returned a file and then if there was a problem maybe we should probably tell them what the problem was so all that of course along with the file name if there was some sort of success so to do that let's go ahead and create a new class module so we'll do insert class module public I'm going to call this success are successful there we go as boolean and this is a true or false boolean value that we can set to tell you know whatever the caller was whether or not we successfully got them a file then we can say public error message in case this was not successful and then we'll say public file name as string so if it was successful we'll pass in the file name as a string okay great let's save this now um the name of this class what I like to do is I like to follow the same function name that we you know since this is gonna be the result of the function I'm gonna actually just call this open file dialog results okay so it's basically the name of the function with result at the end okay now let's close that an open file dialog is now going to return instead of a string we're gonna say it's going to return an Open File dialog result and now we can do is if we had a successful attempt here let's go back to this part here so if it was successful it's going to return well you know what I need to create a new instance of so we'll say dim result as new instance of Open File dialog result there we go we're gonna create a new instance of that open file dialog result to class and then what we're going to do is we're going to set result dot file name equal to C string item okay and then since it was successful we're also going to need to set the successful property to true and then down here if we did not get a successful result then we're going to say result dot error message or actually we'll just do successful first and say this is false results dot error message equals no file selected okay so that's gonna be the error message that gets returned back to whoever calls it now one thing I don't like though is having this very generic function that's supposed to be available to everything inside of my form module code I don't really like that so I'm gonna take this whole thing I'm gonna cut it out of there and I'm gonna create a new module and I'm just gonna paste it in here and let's go ahead and save this module we'll just call this file utilities maybe we'll come up with another name somewhere down the road but that's perfectly fine and you'll also notice that it really kind of cleans up what we're doing here instead of having our form being cluttered with that extra function that you know was really kind of noise as to what the form functionality is supposed to be it's been extracted away it's been peeled away from this form module and put into its own module and I really like that I like the separation of things I like to keep things as separate and nice and neat and compartmentalized as possible so now we need to go ahead and call this new function and I'm just gonna go to file utilities dot Open File dialog and you don't need to use the file utilities but I just like to do that so I get the intellisense and I need to pass along the title as a string so this is going to be select a file to import allow multi-select no we don't want the user to select multiple files and the dialog filter hmm so I need to create a new dialog filter to do that I need to dim a let's just call this filter as new file whoops this is actually a dialog filter let's set some properties on this filter so we'll say filter dot description equals I'll just say de-limb dot files and then for the actual filter extensions we're going to say we wanna do star dot CSV so we want the user to be able to select a CSV file or a star dot txt file okay and now we can pass our filter variable on to our open file dialog method now this open file dialog method is actually going to return an open file dialog result so let's go ahead and create one of those dim file our open file actually we'll just call this result as open file dialog result and now we're going to set it set the result equal to whatever gets returned back from our method so now this open file dialog function or our method is going to return back a type of open file dialog result we're setting it to our result variable now we can look inside of this result variable and see if result successful then we want to put me txt file name set it equal to result dot file name okay great and then what about the sad path what if there is a problem and it's unsuccessful well what we should probably do is tell the user some sort of warning so let's give this error message back to the user so to do that I'm gonna add another text box here get rid of the label because we don't really need that I'm just gonna put this here at the bottom and I'm gonna make it nice and big but I am going to call this txt error message and get rid of the border style make it transparent and make the back style transparent as well and we should probably also make the text red so that it's pretty clear that there's an error and let's go and save that we can say me dot txt error message equals result dot error message perfect now if it was successful then we should probably go ahead and say me dot txt error message set it to null so just in case the first time somebody gets an error then the second time it doesn't keep that air up there it gets rid of it let's go ahead and try this so let's go ahead and save this debugging compile everything looks good let's test it out here so we'll do home view browse and what happens if I cancel out object variable or variable block not set debug uh-oh so it looks like results is set to nothing hmm did I goof up somewhere why is this nothing dying show then let's just see what happens there maybe that's maybe that'll give us a clue so we'll do browse diag show okay and I'm gonna cancel it said successful is false oh you know what I didn't do I didn't return result so at the very end after all this I should probably go ahead and set Open File dialog equal to the results okay cuz I forgot to do that right so now we just need to set our results ok this results variable that we created we need to set it for that open file dialog and that should fix our problem so that's even the best decoders have problems right even the best of coders mess up while they're coding so let's do brows cancel and it looks like we still don't like this open file dialog do we have our results oh I gotta use the set key word there we go now we're happy there we go no files selected so just to show you guys this again if I cancel no file selected I'll just exit out reopen this just so you can definitely see there's nothing there right now I click browse' cancel no files selected okay perfect so that's working now if I click on browse nicely look at the customer batch one away goes the error message and now we have the file name listed here in our text box that is done and it's even been split up into multiple functions one that goes and does the actual function call and then sets the result from that function call to our text box whereas the function call itself the Open File dialog has been extracted away into this file utilities module and we can reuse this Open File dialog method wherever we want now let's take a look at this select import process and then import files entered into the text file name text-box now I I have a pretty good idea of how we're gonna do this import process because we've already seen it at work we've already used some sort of import process to get these customers in and that was back here when we did the external data and text file we can select browse select the file we want to browse and then we want to append a copy of the records to the customer table ok and do our delimited it's a comma separated value so we got that text qualifiers quotation marks first row contains field names and of course we also need to do the Advanced tab so that we get the correct field names right so we'll do address line one address line two zip code phone number and remember we want to skip the ID field and if you look here we see this save as this import specification we can save it so let's go ahead and click on that save as customer batch import specification let's do customer CSV import specification let's call it that or just a customer CSV import okay and let's go and click OK on that and next let's just make sure it works ok it sure does there's our customers and it's got different IDs but that's ok so that worked let me get rid of the data here now what we can do is for our import file we want to be able to select that import process and to do that that imports specification ok so maybe a better word instead of process is select import specification alright now import specifications are stored in a custom table back here called M sis MMX specs so if we open this up and this is a hidden table by default if you don't see it you're going to need to right click up here then you're going to go to navigation options and you're going to need to select show hidden objects and show system objects ok so make sure these two checkboxes are turned on and now you can see here's a spec name customer CSV import and it's got all of our information and it spec type file type so we can actually select the spec we want to import so since we can select it maybe we need to come up with some sort of user interface to allow the user to select which type of file it is that they're importing because I don't really want to go through the hassle of coding that I suppose we probably could but you know I'm gonna make it so that the user does this so I'm gonna bring this down here and I'm gonna say create a new let's see design combo box and now to fill in this combo box will call this CBO import type I could say file type I suppose file type all right now the data is gonna be coming from or actually it's gonna be the row source it's gonna be coming from our mmm Isis IMAX specs all right what we were looking for was the where to go spec name there you go and that we just need the one column and if we view this customer CSV import hmm alright so we could select this and then we can select a file type but now when the click user clicks on import file we should probably do some sort of file checking right to make sure that are some sort of validation that makes sure that the user has in fact you know selected a file and has chosen a file type so let's go ahead and do that and really this should be a fail fast okay this should be some sort of failing before we proceed with any other code so to do this I'm gonna do if me dot txt file name and if it's null so if it is null so if there's nothing in that text box then let's just set the error message right so me dot txt error message equals no file selected or we could even say please select a file okay just to be a little different than the other error message that we were getting back okay so that's if that text box is null what about if is null V dot CBO file type then and if me dot txt error message please select a file type alright so that works let's try it let's see if I leave that empty import please select a file type if I leave the file to import empty import please select a file type okay so we've got some problems there right we don't if I don't select anything please select a file type you know what did I give the same error message for both I might have denied I know please select the file if the text file name is empty I wonder why that is I wonder why it's getting that far you should probably debug that just find out why is it getting that far please select a file oh well of course because it's still gonna continue even if I do so we need to fail this fast and to fail it we need to exit the subroutine right because once we know that we can't continue on with a code we should probably exit out of the subroutine so let's do that exit sub okay and that's why we did bug write so import file now we just see select a file so if I select a file ok import file but I don't select the file type then it tells me please select file type perfect ok let's take a break oh that's sweet oh look at the look she's given him now if everything was successful though all right there has been a file selected and a file type is selected then we're just gonna set the default to me dot txt error message equals null and we could really put this at the top so that we start with a clean slate regardless okay and if neither of these two conditions get hit then it won't change that status of that text box so that's perfect okay then we need to import the file entered into the text file text box to the customers table so import the file entered in text file name text-box to the customers table well again really I'm kind of doing a lot of stuff here aren't I am the validation is good to do inside of the click before you go and do some other function so I'm good with this and we've even got the selection process right the select import specification that's really done by the interface now right we don't have it as a function so we don't need to split that up into its own function but this part here the import file entered in the txt file name text-box to the customers table well that should probably be extracted to its own function so let's go ahead and create one here a public it's gonna be a function import file let's call it import text file to customers table ok very specific for now and we should probably learn a lesson from the last time we created that that other function in the file utilities module and say that we're gonna return not some sort of simple little string or a bullion but some other new class so let's call this import text file to customers table result and really as I think about that that seems pretty specific doesn't it and if we're gonna want to reuse this elsewhere maybe we don't want to be that specific so import text file or import text file to table import maybe we wanna yeah import text file to table I like that so import text file to table result alright that's a bit more generic isn't it right it's not being very specific about it being a customer file now we do need to be specific that it's a text file because all of the files that were gonna pass into it are gonna be a text file of some sorts maybe we could expand this out to something like an Excel spreadsheet or some other type of file type but I'm good with just working with text files with this function now the function that acts he does do the importing process is do command so you do CMD and it doesn't like this why doesn't it like it it's not giving me any sort of intellisense so that means that tells me that there's something wrong here let's try debugging and compiling yeah user-defined type not fine that's because I haven't created this type yet so let's go ahead and do that I'm gonna create a new class insert class module and this is just for right now public successful as true boolean right and we'll see what else we want to add to this a little bit later but we do know that we want to name this import text file to table results okay now let's see if the compiler is happy all right now maybe we can get some intellisense from do command there we go transfer text this do command transfer text accepts ac import delimited that is the one that we want right there ac import delimited now look at that specification name hmm guess what that is ladies and gentlemen we can pass in the name of the specification that the user selected so why don't we just pass that along as a parameter so we'll do specification name as a string so we'll do specification name now the table name well once again we probably don't want to be very specific about this because we got rid of this specificity in the name of the function so let's just go ahead and say table name and as strengths so I'll do table name and file name so we need to pass in a file name as string so file name and has field names is true hmm that might be false though wouldn't it hmm hmm so this could be has field names as boolean and this is violating that rule that we talked about before but there isn't really any good way to group just one or two of these because they're all being passed in to the same function the same transfer text function so what I'm gonna do is I'm gonna copy all of these different you know parameters that we've got here and I'm gonna create another class and I'm just gonna create each one of these so so now we have a class but this class is very specific right it's all of the parameters that are supposed to be going into our our import function right and what was the name of our let's just call this class one for now because I forgot what we named our function here import text to text file to table so any time that I'm going to take a bunch of these and that are all going to be passed in as parameters to a function I like to name it this way so I'm going to do I'm going to select class 1 and we can go right here and change this to import text file to table params okay so we're just appending params to the name of that class and now we can say let's just take this and we're just gonna say params as import text file to table params and now we can do and we're good with the AC import delimited that's gonna be pretty consistent their specification name is going to come from params dot specification named params dot table named params dot file name and params has field name okay there really wasn't anything else that we want to add here we could do the HTML table name and add that to the parameters and the code page but I don't think that's necessary I think we're good with just doing that now there is a result expected right we're expecting to have some sort of result returns so we should probably dim up a new result as new import text file to table result so if everything was successful then we're gonna set results dot successful equal to true but what if what if there's a problem hmm well there isn't anything that gets returned back from transferred text method to tell us that there was a problem so what we're gonna need to do is we're gonna need to do some default go to hang here and I really don't like doing it but we're just say on error go to and week you need to say something specific we need to either a line number or a label so we're gonna jump down here I'm gonna create a new problem with import okay and this is going to set results dot successful to false and now we don't really have an error message being passed along so we could probably go ahead and say if on error go-to problem with import though and now just say results dot error message unable to import file okay just a generic message we could get more specific with that error message if we wanted to and we do need to add the error message property to our import text file to table result class so let's go ahead and do that now and before we get to this in problem with import if we had a successful import then we want to go ahead set successful to true and then we want to go and exit out of the function so that it doesn't end up calling these this down here right we don't really want that to happen okay so now that we've got that in place if there was a successful result or there was an unsuccessful result we should probably go back to where our import file button is ran and based upon the results of this we should probably go ahead and display something to the user to indicate what happened let's first dim an instance of import text results so that we can store that in a in a variable so dim results as import text file and you know what I just realized I almost did it's exactly the same problem here but I did with my earlier function I forgot to return a new instance of my result denied so I need to set import text file to table equal to our results and then I'm just gonna do the same thing down here all right that way both ways return back some sort of results so good to go okay so now that we know that's going to happen we're going to set our results equal to whatever we get back from our import text file to table method and again we should probably extract this away so I'm gonna actually before I get too far along I'm gonna take this out here it's still kind of a file utility I could probably put this into my own into a some other module but I'm good with moving it over here to the file utilities module that's good okay now let's go back and let's just be real explicit here we're gonna call file utilities dot import text file to table all right this method takes params as import text file to table params so okay dim params as table params a new params and before we get too far I'm gonna say params dot file name equals me dot txt file name params dot has field names is set to true right they all have field names of params dot specification name this is going to be passed in or passed to it as me dot TX actually it's the CBO file type there we go and params dot table name is equal to this is gonna be the customers table isn't it alright so now we could pass in params now some of you may be thinking whoa wait a minute he didn't like setting all those properties in his method you know when we were talking about the earlier on but he's perfectly fine with setting for properties here well I'll tell you I don't really like doing this I like to be passing things in as parameters as much as possible but there really isn't much I can do about it in this case because we have so many different things and VBA unfortunately is very limited if it had constructors I would be very very happy if I had constructors that I could pass parameters in I would be a very happy camper but since I can't do that I'm gonna have to do this and there's also with you know the dotnet languages you can set your values immediately upon instantiating an object but you can't do that in a VBA so unfortunately that's just the case here now I could create some other method on this params class that you know allowed me to enter in each one of these as a parameter but then you know why not just pass them individually into the import text file to table method so I understand it's kind of violating one of those principles that I was stating a little early but this really does add some modularity to this import text file to table method all I have to do is pass in those parameters and I can set them or not set them right and maybe I want to add more to the parameter to the import text file to table parameters maybe I want to add more properties that need to be used in that function later so it's very extensible it's very malleable I can change it over time and it's not going to cause too much of an issue so anyway so we're going to set result to file utilities import text files to table params let's go ahead and save this and now I should probably check the results right so we're gonna say if results dot successful then we're just gonna say me dot txt error message I'm gonna set this to or you know what rather than do that I'm gonna just give an alert box so we're gonna do message box import successful and vbe mmm okay only and the title just say success but if it wasn't oops and if we want to do else there we go but if it wasn't then we want to go to me dot txt error message we're gonna say results dot error message now this could actually probably be split up into some other function you know where this params creation like this but I'm okay with this as long as your functions aren't bigger than the size of your screen you're okay and really a lot of people go by the rule of thumb that your classes shouldn't be any bigger than your screen otherwise you're too big but VBA again doesn't really have a lot of flexibility in its language so we can't really do a whole lot of things till it really down our code but for the most part this flows pretty well and we've extracted these two functions the import file or the Open File dialog and the import text file to table those have been extracted a waste that we could reuse them potentially somewhere else and I really like that I like to be able to have those things extracted away so okay let's go ahead and give this a rip and I have no guarantees that this will work maybe I should probably compile it yeah that's a good reason to compile has field name yeah should be had field names yeah okay that was it there do you bug compile all right let's see what we've got here let's see if our first draft here is working shall we so what is one of the import specifications that we got we have a just a customer CSV import okay and the text one the customer batch text one is that Ashley let's close out of this just so we start with a clean slate we don't have that error message okay so browse customer batch one it is a customer CSV import import file import successful look at that let's check it out let's see what we got for customers by golly there they are let's okay we like that I'm not gonna delete those instead what I'm going to do is let's create another import process so let's do text file append to the customers table let's grab this one here so we're just creating our specification that we're gonna use right this is just to create the specification so next other this is a pipe delimited value with quotation marks first row contains field names we need to go to our Advanced tab okay and let's save this I'm gonna say customer piped limited import okay okay next let's just make sure this works so finish yes I'm okay with that close that and we could just double check here inside our specs and see yep there's that new customer pipe too limited spec name that's good our customers table looks like it imported the data let's just verify this I'm going to clear out all the data make sure our form works now with this new import process we'll do import customer file ste browse get the first batch customer CSV import look at that we got both of them selected import file import successful okay now let's do the pipe delimited import batch - okay import file import successful look at that and there's all of our data fantastic now what about the sad path what if what if things don't work out so I'm just gonna clear all this data out again what about if we okay let's start with forgetting to import no please select a file so we didn't select a file let's go ahead and oh we may have another problem that I just realized cuz what about that right if I do that unable to import file well that works doesn't it I guess maybe we don't need to select it okay unable to import file that's pretty good so let's select one their customer CSV import import file bingo what about if I try my batch - but I leave it customer CSV import import successful import errors though we got blank records hmm we should probably do something to check for this shouldn't we well that's kind of inconvenient that we didn't really get any errors when it tried to import isn't it so we could probably I guess maybe just make sure that when the user selects that particular type that it's the right type I don't know it's kind of tough isn't it let's go to the import file and see what we can possibly do about this to fail the application if the user selects the wrong import type so after thinking it over I do think that there is a way that we can check to make sure that the that the import type is going to work with the file that we've selected and they didn't really make this very easy and access that I wish that they would give you some sort of error because you shouldn't be able to successfully import you know uh records in here if it doesn't actually work there should be some sort of error message maybe they'll fix that in the future I don't really know but what we can do and you can even see that if there were errors that there's this whatever the file name is we can see import errors and rows etc etc so I don't need this table but what I'm gonna do is let's go back to our import file button we're gonna go to the import text file to table method let's go to that definition here and what I'm gonna do is before I even start with the import process I'm going to look at the specification that was handed to me and I can do that by looking at the table right I can do that by opening up the MS SMX specs table and in here we have the field separator so we can check to see if the text of the file that we've been handed has any of these things in it and if it does then we know that it should be a valid file that we can use with that specification so what we're gonna do is we're gonna dim a file system object first so that we can get to the text file that's been passed along so we're going to dim FSO as new file system object and you can see once again we don't have it as intellisense so we need to bring that in I'm gonna do office or I'm going to do in the references we're looking for a Microsoft Script scripting runtime or do new file system object perfect okay so now what we're gonna do is we're going to check so dot file exists we're going to check to see if the file exists and the file that we're looking for is the one that came in the params dot filename we should wrap this in an if statement if it exists then we can continue on otherwise if not actually so we should probably check if not we want to fail fast and I fail it as quickly as possible in that case we will set result to successful to false and the result dot error message be something like file not found which should probably be done anyway right we should probably be checking that anyway so if and then we'll just go ahead and set import text file to table equal to results and then exit out of our function okay so that's if there was a problem finding the file if it was found then we can go ahead and set a file stream so I'm gonna show you FSO open text file and the open text file method returns a text stream so we need to create a variable that's going to contain that text stream so we'll do dim text as text stream okay we're gonna set this text equal to the open file text stream results file name is again per am stock file name and we want to do for reading because we just need to read the data and all the rest of it we could just leave as default that's fine now we're going to need to do something with this text we're gonna look we're gonna need to read everything right we're gonna need to read everything that's inside of it so read all and the read all method I know it doesn't say it but it actually returns back as a string so dim file data as a string so I'm gonna set the file data equal to read all and now that we have this as text we can look to see if there is any pipe or whatever the value is that was in that record right so we need to get the field separator value from our MSS IMX specs so I actually need to open that up as a record set so we're gonna do dim RS and I suppose I could do do a deal lookup right I could just do that as a D lookup so we're gonna set dim character as string and we'll do field actually we'll do field delimiter just to be more specific I need to get that field delimiter so we'll do a field delimiter equals do you look up and the expression as strength so the the a field separator is what we're looking for we're looking to get it from the M sis I'm ex specs so M sis I mix specs and the criteria is where we want to get spec name is equal to the spec that's been passed in so we'll do spec name equal to me our params sorry specification name all right so that should get us our field delimiter that's gonna be this thing here that tells us what it's being separated by a comma or a pipe now we're going to use the in string function i in STR we're going to start at one and see if in our file data we can find the file delimiter or field field delimiter I think I spelled that wrong down here so field filed field delimiter okay and the in string function just hit f1 here and show you guys the in string function is going to return back a variant long specifying the position of the first occurrence of one string within another so the string friction being searched string two is the expression being sought and if we don't get anything back string to 0 length is null is not found you can see it's going to return 0 if strain 2 is not found at all so all we're really looking to do is see if in string 1 is greater than 0 then we're good right but if it is equal to 0 then we have a problem so we're just going to do if it's equal to 0 then we're going to fail fast okay so we're gonna say results dot successful is false and the result dot error message is incorrect import or we'll say incorrect file type okay and then we need to set import text file to table equal to the result object and then we're going to exit the function okay so that should do all of that for us that should check and see and I don't like how big this function is we should probably extract it out and make it into another function but I think you guys get the idea of what we're trying to do here and I'm just going to compile make sure I didn't fat-finger anything close out of this and let's get rid of all of our customers that are currently in there and let's see if this at last finally works and fails where it should fail so I'm going to click on browse I'm gonna get the CSV file this time and let's do an import of a pipe delimited so this customer batch one is actually a CSV file right it's comma separated values but we're saying is pipe delimited so if we do import file unable to import file so there was a problem with that that's good so we caught that one it may not have been exactly the error message we were expecting but let's try this one so type two with a CSV import unable to import files so good we're definitely getting exactly what we're expecting aren't we so this is good let's try CSV I expected it to tell me that we had the wrong type but it seems to be working now why is it working did we do something we might have done something let's see what we got here unable to import file did I give something up I might have let's see import file this is why we always debug right so go to definition let's just set a breakpoint there and this should work so why is it not working let's see the file exists so that's good get everything in the file now looks like we have a problem with how I did the D lookup field separator I might have spelled that wrong let's check that out SEP our rater uh-huh uh-huh I think I spelled a separator yeah maybe that's all I had to do let's try that again debugging is always nice isn't it although there's some theory out there especially when you get into the dotnet languages you shouldn't be debugging you should be using unit testing which I kind of agree with all right so let's try this again so customer batch text CSV import let's step through this baby ok finds the file ok found the field delimiter that's good what is the field to learn common perfect and it does find something as a matter of fact to find a third third value so perfect ok so now we can transfer the text uh-oh what was the problem well did we have an issue with CSV import dilemma table name customers field name has field names it's true what's the problem well maybe we should do something like this instead of just giving unable to import let's do er R let's actually get the the error message so it's gonna be the description that's right ok so let's do that that way we know exactly what happened Maira soft data is you cannot open right to the file like it's already open oh well duh it's already open I can't reopen it that's because I opened it here and I forgot to close it so close there we go that's always gonna be a problem okay all right let's see if this works now I'm hoping fingers crossed we've done a lot of debugging we've done a lot of thinking through things import file success if I go look at the customers table whoops looks successful to me all right let's go back to here and this time I'm gonna try to do the pipe delimited file right customer batch two is pipe delimited but I'm gonna select the CSV import again and select file import in current file type perfect what if I tried to do batch one and said pipe delimited incorrect file type perfect I'm gonna go back and select to import pipe delimited import file success look at that we have exactly what we're looking for it took us a while to get there right we did a lot of extraction we did a lot of things there's probably even some more extraction that we could do with some of these error messages that we got maybe some of these the button click events we could maybe extract these out to other things too but I'm pretty satisfied with this I think this is a good result we're on our way to being a bit more object oriented we've got all these different classes that contain data they contain parameters they contain filter information they contain results so we're really becoming object-oriented right in our VBA code even though VBA is not a typical object-oriented language it does give us a bit of flexibility with these classes to do some nice little things so anyway I know that this was probably a very long and surreptitious way to get to our end result it wasn't very direct but I really wanted to give you guys an insight into how to solve problems how to extract away the different functions that you might have how to plan out your your functions and if eventually you'll get there eventually you'll get to the result that you want and maybe this should be like I said refactor is a little bit more so that maybe this especially this import text file to table process I really think this could be you know extract it out into some maybe two or three other functions instead but I'll let you guys do that I'll let you guys kind of think about that in ponder how can I maybe you know extract some of these things and really a good key of that would a good key to that would be some of these blocks of code right so we have a block of code here maybe that should be a function instead of a block of code inside of this function kind of a similar thing down here with the do transfer text that's actually what we should be doing so maybe we need to do some of that extraction anyway I'll let you guys work on that and I hope to see you guys at the next video I hope you guys enjoyed this access video if you did please don't forget to Like favorite and subscribe to my channel also you can hit the little alarm bell on my channel so that you can get alerted whenever these new videos get released so if you guys have any questions if you have any suggestions for a video you'd like to see on a general topic such as this go ahead and give me a your ideas in the comment section below and I hope to see you guys at the next video and have a great time a great day and until then thanks

Channel: Programming Made EZ

Views: 24,866

Rating: 4.9478259 out of 5

Keywords: microsoft, access, 2013, programming, develop, developer, developing, development, software, application, app, database, csv, comma separated values, tab, delimited, values, comma, separated, pipe, plan, architecture, feature, process, how, to, import, importing

Id: 9BeuzRYdw1I

Length: 80min 13sec (4813 seconds)

Published: Mon Nov 28 2016