Import CSV Files into Access - pt1

do you want to know about importing text files into access watchon hi I'm Philip from code cabinet comm today I'm going to show you how to import CSV comma separated values in text files into a Microsoft Access table first we're doing it manually and then we are going to automate the process with VBA this video is the beginning of miniseries about our topic first today with the very basic import the next video about next week will be about importing with a more complicated import process expecting duplicate values and updating existing data after that I'm going to show you how to create a dialog in Access we're going to use that for having the user select or enter the file name and start the import process but the underlying technique to create the dialog can used for virtually any dialog in Access and the final part of the series as far as I see it for now is a little bit improvement of that dialog we are going to add a real file dialog component that will allow the user to select a file name from the file system so but let's get started now with a simple CSV import I prepared this very very simple Access 2013 database to show you how to actually do the import of the CSV file created this simple customer table it has a customer ID which is an auto number and identifies the customer inside this database and then a customer number which is more of a good-looking customer number that will be displayed to the outside then the rest is a walk standard its first name last name an address line one address line to a city postalcode country and date of birth pretty simple now I prepare as well a CSV file to import into this database and you see the file here it is automatically linked to Microsoft Excel but I would strongly encourage you not to use Excel to preview the file but I recommend to open it in a plain text editor just plain notepad will do and you should actually do that before you import any new type of file for the first time into your database because you will have an easier time spotting any problems with the file and the first thing you should do is compare the field names if there are field names in the file and you see there the first line is actually field names and you should compare them to the names inside your database in the table you want the data ending up in so we start here with a customer number we don't have that customer ID that is no big deal but you should notice it then we got first name last name that is the same but here it's abbreviated edirol line while we have the address line in in our database table that is a fun thing we should take a mental note off the same goes for address line 2 which is abbreviated as well then here is town and that column is called city in our database and we got a zip for the zip code but in our database table its postal code then he is the country that is the same and we got our DOB date of birth column and you should notice we've got a value here it's pretty obvious the year is the first part of that date and the separator is a - while here I've got the German date format in my database with day month year separated by dots now this is different here and if you just look at this state we cannot be sure if the sequence is year month day or year day month both could be possible so it should look for a date this one's nice there's no ambiguity here it is year month and day because this has to be a day so we know the date format inside that CSV file okay now we can close the tables and can actually start to import the data we do that in the tab external data and we choose text file because a CSV file is just a text file now use the Browse button select the file you want to import and click open and then say you want to append the data to records of the table TBL customer and hit OK and now you got a preview of the data inside the file it is delimited then is comma separated values they are delimited and we can go one step further and select first row contains field names but there are still the problems to resolved I spotted when viewing the text file here is the column header address line and town a zip that is not going to match our tables so we have to use the advanced button down here to put that right now let's start here yes that is what we selected it's delimited with a delimiter comma we have got text qualifiers the quotes inside the file the language and the code page that is correct now here for the date order we said it is year-month-day inside our file that which should we change and the datum delimiter is a - not a dot so we don't have time so we don't care about the time delimiter we've got four digit years we've got leading zeros and dates but there is not too much of an issue if there's a date delimiter and we need to take care of the decimal symbol there were no decimal numbers inside the file so it would not matter for the data but the decimal symbol cannot be the same as the file delimiter we would get an error then so I changed the decimal symbol - and aunt now say okay oh of course that is that it wasn't the main reason ayah I needed to open this dialog in the first place I need to change the names of those fields its address line in my database an address line 2 and town is called city and the zip is postal code so we need to change that here to translate the column names from our import file to the column names used in our table so now I confirm with ok and I can go here there's nothing more to select and with that I just say finish so no errors the import completed successfully and now I've got the option to save this import and I checked to to do that and now show that in a minute so I say save import oh I did that already so I just named that customer import evil customer too and save this import now back to our table and you see the data has been imported everything is fine the date that is an important thing to look for the date has been correctly recognized the data looks fine so far we had a good success here now what if we want to import that same type of file again and there is something that might happen quite a lot in your database that you get a new file every day or every week or every month and you always want to perform the same import did not prepare a new file so I just delete those imported rows from my table and I just pretend I got a new file with new data and there's one way I can fairly easily replicate the import I just did is the saved imports button and you see that is one I created earlier and that is the import I just saved import about customer two and you see this is the file name and I can just run this import close this and look at the data again and it has been imported again you see that the ultra number customer IDs they have been their new well you so the data has been imported again and you see that there and that is well an easy step to repeat a saved import but the saved imports they have a little bit of difficulties if you want to change the file name you could do that and and enter a new name here to change the file name but that is not a very user friendly and there's no option to to use a file dialog here to select a file name and so that is fairly limited you can only import the same file by name again and again and again if you've got an automatic process that creates that file every day with the same name and new data in it then this is fine you can can just use it but otherwise if there are multiple files you need to import with different names on different directories this is a bit of a pain to use so it's not the best option to include that in your user interface form for your users to use now let's try to automate our import process a little bit more I go to the VBA editor and database tools Visual Basic and hit the insert module button up here and got a new module now on my right public some import CSV file and now I can use the do command object with the run saved import export method to start that saved export saved import steps I prepared just a minute ago and it's called importable customer two and now I can just run that I can hit f5 or I can click the Run button up here you've seen the hourglass for a second and if I open the table now the data is in here again and it is imported it works I delete that right away these imported rows go back to visual basic but there is the same problem we had in the user interface in the dialog to run the saved import there is just the name of that importer can supplier cannot even change the file name here so this is very very limited I can just repeat the steps I previously used and that is well it will not be suitable for most requirements you encounter in a real world application so I'm going to delete that and show you another way to import that and that is much more versatile once again on the external data tab click text file select the CSV file select the table here hit OK and even though the advanced button is here already don't hit it just yet we go next and we absolutely need to check the first row contains field names first because otherwise there would be a problem now I go to advanced and now you see the file the column names from the file as I did before if I had hit the advanced button earlier in the first page of the dialog it would have read the field names from the table and that is not what we want so I just need to adjust for the names in the table note on the city and that is our postal code as we did before once again I changed the date format the date delimiter check the leading zeros in dates and change the decimal symbol to a dot and now instead of just okay I do something else I go to the save as button click that and now I can save the stuff from that dialog in an import specification and just choose the specification end customer import specification and click OK now I can confirm with ok once more and I could continue here I'm going to cancel to show you what that import specification dance I start the import new and now once again now I can click the advanced button and go to the specs here and open this one and now you see it has the field names from my file the customer ID which previously was here is removed it has the correct date order the data limiter everything I entered previously in the configuration is there at once I can hit OK and could hit the finish button here right away but I don't do that I just wanted to show you that you can simplify these steps a little bit but there is still much too complicated for your average user so you cannot include that in the user interface of your application so I'm going to cancel here and once again go to the visual basic editor hit the insert modules button and start with my public sub import CSV file once again and again I use the do command object but now I call the transfer text method that is for importing or exporting text files and that's what we want to do and now I need to define the type of transfer text operation I want to do and that is AC import delimited because a CSV file is delimited by the commas now that is the important part I can now name a specification that should be used for that import we saved our specification with the name customer import specification and that's what I am going to type here I can now enter the table name I want a data to go into I could vary values a table with the same structure but a different name here if I wanted to I don't have any so it's quite clear goes here in TBL customer next thing is the file name and that is not just the file name but the complete path so I've copied the path here and enter the actual file name and the next next thing is the field names yes our file has field names so we enter true we could now enter HTML table if we would be importing an HTML file but with our CSV file with that does not apply so we leave that and finally there's the code page my file is in the 1252 code page but that is windows default so I could very well admit the code page here just enter it for completeness but it doesn't do anything right now in my specific setup if you get a file name that a and file that is that have been saved with another code page with another character set then you could enter that right here instead of the 1252 now let's change a tiny bit I enter an underscore here and put that on the next line I'm at tiny underscore just tells VBA that the instruction continues on the next line so I can wrap that and have it in better view on one screen without needing to scroll to the right so that should do already now I hit the play button Run button up here and we switch to access and you see the data has been imported everything is fine our dates are imported that is basically how you can automate the text import from VBA and I'm just going to delete these records and I just show you one bit in VBA that is important now all that is hard-coded but I can very well change my my procedure and supply a file named here that is a string and now I cut out that hard-coded path to the file and put in my file named argument in here now I can't just write call import CSV file here in the media pane and I paste my path to the file and now I hit enter to run that and once again the data has been imported so that would be the approach to design your CSV file import much more flexible than that than before you can now supply the file name from the outside you can either have a routine that that calls that that has a set of predefined file names you could hard code that like I did in the immediate pain down here with a fixed filename as before but you could a very well design a form in the user interface of your application with a final dialog and let the user select the file name and then run that saved import with the user selected file name and I will show how that works in another video because it is not really part of the core import process so this is it for today I hope I was able to explain the basic options and mechanisms of importing text files and to access next week we are going to cover a little bit more complicated import if the video is released you're going to see it here or there or anywhere on the screen if it's not right yet released I suggest you subscribe to my channel and will be informed then about the release of that video and yeah that's it thank you for watching bye bye [Music]

Channel: codekabinett.com/en

Views: 18,996

Rating: 4.9166665 out of 5

Keywords: Access, CSV, Import, File Import, Textfile Import, VBA, Automated Import, Microsoft Access

Id: KLrm1ddyOOw

Length: 23min 9sec (1389 seconds)

Published: Sun Nov 12 2017