Import Series - Import CSV TXT File into Excel

here I'm going to show you guys how to import CSV and txt files into Microsoft Excel and this tutorial has two main functions I'm going to show you a couple different ways to actually import the data into Excel and then I'm going to explain how the import works how it functions because it's not just a straight copy-paste there are data connections that are formed almost always when you import the data into Excel and if you don't understand that it can create problems later down the road before we start check the video description and click the link to teach excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials now before we get started let's take a look at the data that we're going to import so I just downloaded a very simple CSV file from the geonames website and I revised it a tiny bit so it's country info revised right here let's open that up this is notepad plus plus by the way way better than regular notepad go ahead and download it and this is our CSV file so we have headings and line one and then a bunch of other data here and we'll go ahead and see how it is to import this into Excel so pretty much everything we're gonna do here is on the data tab but the first one that I want to show you is the simple the easy the old one that I'm sure most of you have used at some point and wondered where it is and the modern versions of Excel so if we go over here to get data you have from workbook from text CSV but it doesn't allow you to use the import wizard let's go get that import wizard back in Excel and use that first it's going to allow you to create the simplest easiest CSV txt file imports with the least amount of work in my opinion so let's go to the file menu then go down here to where are you options go to data and here under show legacy data import wizards we have all the old ones that we may already be used to the one that we want is from text so check that hit okay now let's go back to the data tab get data legacy Wizards click from text legacy select the file that you want to import and if you don't see the file here go ahead and click the text files down here this little drop down menu and you could go ahead and select all files sometimes you may have one option selected that doesn't allow you to see the specific file that you need then go to country info revised I'll include that in the downloadable files double click and we get our text import wizard it's gonna walk us through so the first thing is we want to choose if our columns are delimited or fixed width so when you import the data the data it has to know what is a column so in a CSV or a comma separated file you could have commas that do that or semicolons where oftentimes a tab will create the column so we choose what we have here delimited or fixed width fixed width means that you're going to choose the size of the columns so you're going to choose it by hand and usually the data the import will not be set up that way that's not a terrifically good structure for most data so we can choose delimited then we can choose at which row we'd like to start importing our data so if the data you want doesn't start until row ten you could put wrote in here we're gonna keep it at one choose if your data has headers or not you can play around with these options to see which are gonna work for you but for the most part the default options will work just fine usually you just want to make sure delimited is clicked then we can hit next and at any moment you can click finish and have it go ahead and import the data into Excel if you don't want to go through these steps go ahead and click Next to go to the next step here is where you choose what the delimiter is Excel will kind of try and choose the right one but if they made a mistake go up here and choose the right one or go down to other and you input the delimiter there so we will hit tab you can see the data preview change then go to next here you can choose into which format you want each column to be imported so you can choose two columns change the format it's pretty simple text import stuff there's nothing really too fancy that you can do here we could go ahead and click advanced and you get a slightly more advanced options here but nothing too advanced I think this is kind of why Excel tried to retire this guy or hide it away because it's not terrifically advanced but it makes our imports pretty easy if we just need simple stuff so this is the last screen we can hit finish and here you can choose where you want the data to go into Excel on the existing worksheet here you can click the cell where you want to start let's click a one or hit down here to go to a new worksheet but here's one thing I recommend this is what I like to do before you hit OK go ahead and click properties and in this window at the very top uncheck save query definition this is what will get you the simplest import no links to external data nothing the closest thing to a copy paste you're going to get so we can hit OK okay the data pops in perfectly nice neat and simple and if we want to do it quickly so if I do it full speed you say wow that took a long time I just hit the plus arrow open up a new worksheet let's do that again one more time get data legacy wizards from text country info revised delimited next everything looks good finish existing worksheet properties save query definition ok ok good to go it's my favorite way to import a very simple simple CSV or text data set most of the time your data sets are going to be pretty simple if you're familiar with it you know how it's going to be laid out you know there's not going to be any weird stuff in there just do that and able to legacy won't do it next we're going to use the default import from text in CSV method with the new versions of Excel and you will see it has the ability to be a lot more powerful but also a bit more annoying so let's open up a new worksheet and get started there before we do that though I want to show you one thing just to prove that nothing happened with the current to imports as it relates to connections on the data tablets click queries and connections nothing down here for queries nothing down here for connections then we can click existing connections no connections tables no tables nothing nothing now let's go up here data tab you can click right here from text CSV or get data from file from text CSV same thing navigate to where the data is located we're gonna do country info revised double click OK now this is what it looks like when we import it using the new method so it looks newer cooler all nice we can change the character formatting here just like in the wizard although I didn't cover that option you can change the delimiter here so you've got all these options and then custom and fixed width go down to custom and put the custom delimiter fixed width and you can determine the width here so pretty similar where were we tab Excel is gonna try and figure out what's going on before and do it for you and it's going to use over here by default the first 200 rows to figure out what it should be doing either tab for a delimiter or different kind of delimiter if you want to use a larger data set you can go down here based on entire data set or do not detect data types be careful if you choose based on entire data set the mother file from which I made this little tiny baby file for country info revised has over a million rose so that's not even going to import correctly into Excel so based on the entire data set for that would probably cause issues on your computer so it's best to leave it at based on first 200 rows for the most part and the simplest way to do it is to go ahead and click the load button so once we click the load button it seems much easier than before right look it's in here it's so pretty it's so nice we have it on a new sheet sheet for look at this it's just boring text but what do we have over here by default the queries and connection windows had popped up and we have a lovely query oh I just hovered over that what's this look at all this other crap now if I go over here to the data tab we can go to existing connections oh now we have a connection in the workbook some of you or many of you may have encountered connection related errors or security warnings when you've opened workbooks before now we have all that stuff attached to this table instead of just the raw data this can be good this can be bad it's bad if you don't want all this stuff because now I have to go through and remove it that's why if it's a simple data set you don't want any of these connections the first methods better however what does this allow you to do make incredibly powerful queries and incredibly powerful data imports and it allows you by default to link this data up with a data the source data so if I add something to the source data this one's going to be updated now you can still have the data connections when we do the simple legacy data import that I showed you at first it was just easier to turn off because I click the little properties button and switched it off for this one when I import the data you have kin edit load or load to notice that when I click load to the properties button down here is grayed out and there is no way for me to get it back so excel is really trying to force me to have this data connection the alternative is to use power query but that's another step so it's just more steps and more steps and I'll tell you a little bit more about power query towards the end of this tutorial though I'm not going to cover how to use it now once we have the data like this if we want to go ahead and kill the connection you can go to the queries in connections window if you don't see that just go to the work or go to any worksheet doesn't matter go to the data tab click queries and connections you can go over here you can click it and hit the delete button it'll give you a warning are you sure you want to delete this if you delete it the query and any data that was loaded by the query will not be refreshable so you're gonna kill the connection between the source data which was our CSV file or our text file and this workbook you can do that if you want to or hit cancel if you highlight over this you can go down here and hit delete confirm the delete and you can delete it that way as well let's now go to existing connections on the data tab you'll notice that in this case everything was deleted however depending on the way that you delete the connection it may not all come out of here so let's go ahead and import it once more and I'll show you another way to do this it's gonna get a little bit confusing but bear with me import import load sheet five on the design tab where we have this here just like the previous one on sheet 4 you have an option under external table data called unlink disconnect this table from the server it will no longer be kept up to date okay so we break the connection once more between source data in here right but now watch this little guy over here in the queries in connections window on link this will permanently remove it from the sheet the query definition do you want to continue yes now over here it says connection only okay well I thought I deleted that did I did I not I don't know over here I can no longer refresh the data which I'll show you how to do in a moment show you what that does I can no longer click properties if I go to the data tab queries in connections window yes that's still there but now existing connections we still have a connection what the hell is going on so this can lead to warnings in your workbook you think everything is unlinked disconnected no more connections not entirely true and this can be a bit confusing so if you want to go ahead and make sure you delete it completely so it's not going to appear here and you need to go to the queries and connections window and delete it from here then we click delete are you sure I'm pretty darn sure now existing connections no more no mas so you can see all we wanted to do was a simple freaking import and we got to go through all these stupid steps for data connectivity if you don't want data connectivity legacy import man legacy import I don't know why it's not clickable right now but legacy import is all we want close this guy new sheet now we can do it legacy wizard from txt okay so I've pushed that enough now let's look at some of the cool things that when your data is connected I told you with the legacy 1 you can connect it if you want to but you can also easily disconnect it by clicking the properties button yes this way automatically connects everything so let's go ahead and import it one more time and I'll show you how it can be beneficial by well how it could be beneficial depending on what you want let's go with that so good good load ok now let's go down to the bottom of our data set control arrow key down so we have 253 rows now let's go to our that set where are you sample data country info revised okay let's go down here let's add a new row right under a in so you can see a n right here a in T 530 a in a and T right here so let's say X X I need to use tabs here tab stuff tab more stuff and I hit ctrl s to save that now let's go back here everything still looks the same let's bring in the new data from the source file very simple lots of different ways to do it mainly the easiest way is go to the data tab and hit refresh all it's gonna refresh all connections in the workbook so that may slow things down or it may not be what you want so you can go down here and you could simply hit refresh we need to first click within the data sets so click within the data set and then we can click refresh and it's just it's going to refresh just to that data set or you can see they're all f5 so once we do that now you can see X X and stuff I must have messed it up a little bit and this is a good example of if the import contains errors it'll show you right over here so we have 250 few rows loaded and one error let's click the error and see what happens okay so here we have what I was gonna wait a little bit longer to show you I didn't mean to cause that error though is the power power power query editor and you can do lots of things in this which I'm not gonna cover right now but what it's doing here is it's showing us the error so this is the row row 253 we got xx we got stuff but we have an error here now if I click that it's going to show us let's see data format error we couldn't convert it to a number details more stuff so it shows us the specific data that could not be converted or that caused the error and then it shows us with what the error was couldn't convert it to a number because Excel is automatically I'm gonna go ahead and close this window now that it shows you what it is you can go through steps to fix it and tell it what to do with data like that which we're not going to cover here now let's close this window do you want to keep your changes just discard everything all the other things in this column are numbers all the other values and that one was not a value so that's what caused the error once again it's a good thing and a bad thing Excel is so smart it's telling me it doesn't like my data Excel so smart it's not letting me put the data I want in there by default but the important part of this the point of me doing that was just to show you that the data that you add to your source file gets updated and pulls in here now what if we delete two rows let's sleep the X X row and the a n row let's go back here delete this row goodbye and let's delete this row I'm gonna hit shift end to select everything and deletes save back here let's click refresh all this time and the data is gone notice over here no longer says error because the data causing the error is also gone now let's take a look at the other imports of this data because our source data file no longer has the row with a in sheet five let's go down a n is still there sheet for a n will also still be here so because we killed the data connections to these other data imports the data was not updated no data was added but also no data was removed that's very important because if you import data into Excel and you are unaware of the meaning of these data connections over here and then you go and change your source data file because you think okay I imported that the data into a CEL I'm all good to go everything is safe I can go ahead and change the source data file or I can delete all the old data and pop in some new data then you go back to excel you refresh your connections and all of your data is gone and that's really the problem with having these data connections like this so you need to be aware of what the data connections do they can help you but getting the most recent and updated data yes but they can hurt you if you don't realize that the data will also be deleted from Excel when it's deleted from the source file and this is why I've spent so much time talking about something that might not seem terribly important and why I showed you a very simple way to import simple data when you don't want to have to go through all these steps of remembering to delete the connections and do all of that stuff so what I want to do now I've shown you how to import the data and how to kill all the links is I want to quickly show you how to get rid of this table format and then I will recover how to do the simple data import using the legacy import wizard and this data import as well as killing the connections once and for all using the new and default data import so here we have our data table let's go to a sheet 5 where we do not have a data connection let's say you don't want this format it's just a simple data table right now with the table format in Excel so what we can do once we click anywhere in it you will see a design tab that pops up under table tools click that hit convert to range do you want to convert the table to a normal range very much so yes now if you want to get rid of all this formatting just hit ctrl a and go to the Home tab go down here to the right it's currently a little pink eraser and other versions it wasn't this particular pink or purple color go down and click clear formats now we have our nice basic data set with no weird table formatting and no data connections okay so I'm going to quickly delete these guys we'll keep sheet five sheet six let's go to sheet three okay so I'm gonna recap the legacy import right now then I'm gonna recap the new default import and show you how to kill connections let's close this guy legacy import first thing you have to do go to the file menu scroll down here to options go to data down here show legacy data import Wizards you get access to a bunch of them we want the one that says from txt remember text in CSV are pretty much the same thing so check mark next to that hit okay now go to a blank worksheet go to the data tab get data legacy Wizards it should now be visible go to from text select your data double click now fill out all the options as you want I already know the options are going to be okay for my data set if you've worked with the same data set a lot you can be certain that it will work or pretty certain so we hit finish I'm gonna go to properties to kill my link uncheck save query definition hit OK choose a cell where I want it hit okay and there my data is just a couple clicks got the data here go to the data tab queries and connections verify no additional queries have been added here go down here a n will not be here because I edited the actual source file and we just imported it from the source file again so that guys gone now when I show you the legacy import I make certain to show you how to kill the data connection and the reason for that is because if you want to keep the connection go ahead and use the new import so let's use the new import one the new import feature standard one go up to the data tab from text or CSV choose the file double click Excel is now scanning my file trying to figure out what everything should be everything looks good hit load quick note if you want to get to the power query editor if you want to play around with that before that tutorial comes out go ahead and hit the edit button right here and it'll send you to the power query editor where you can do so many cool things let's discard that so from Tech's CSV down here we like everything so we hit load now by default it's on a new worksheet all of our data is completely connected to our source file any changes made there will copy over here so that's exactly how that's going to work and if you want to kill the connection are a couple different ways to do it even one I didn't show you if you have clicked here in the table go to the design tab you could convert it to a range here and it would remove the query definition yada yada as well as kill the table you could click the unlink button here but honestly I think the best way there's so many ways to do it just remember one the best and most certain way go to the data tab if you don't see the queries in connections window go to the data tab click queries and connections and then in the pane over here click the correct one let it hover over it go to delete and delete or just hit the Delete key now it's gone it's not over here it's not going to be in existing connections this is the original query we did not delete now we've got our data over here that stays nice and safe in Excel so that's it for this tutorial I hope you're starting to get an understanding of how importing data into Excel works and how to use the legacy as well as the new standard import features for excel imma cover a lot more topics related to this in the premium course that I didn't have time to cover here and I just can't fit here so we'll cover lots of things of how to change the data when you import it a couple different ways to pull CSV data into Excel without having to go through all this import process and lots of other cool things so make sure to check that out when it's made available I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 64,998

Rating: 4.8521008 out of 5

Keywords: excel training, excel course, import data into excel, excel help, csv import, txt import, microsoft excel, microsoft office training, free excel help, teachexcel, teachexcel.com, csv/txt import, excel import wizard, data connections in excel, delete connections in excel

Id: wZX_5oXd540

Length: 26min 53sec (1613 seconds)

Published: Tue Jan 15 2019