Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
in Module five, which is named important data from other programs. You will be able to learn
00:08
about comporting, spreadsheet files, data files,
00:12
X files and manipulating and cleaning data. So basically, we're going to talk about importing data from
00:23
other Excel programs or other programs or files generated by other programs. Or maybe XL. But in different form of that, Stan will excel, worksheet
00:38
or workbook. So here we can see that when you go to the open ah, functional command in Excel.
00:48
And if you go to the folder and next to the file name, you have the drop down list called All A LL Files,
00:58
which is the default one, and you click on it. You see that all types of files that excel can open,
01:04
and we're talking about opening. So if you look at this list you have, let's go to excel because it's going to be a little bit more.
01:14
Ah,
01:15
visible
01:18
so
01:19
recently can file with Quicken open. We find the folder,
01:26
it's opened. And then here we get all files.
01:30
So all ex selfies list all excel files with regardless off their version. So when you when you get this Excel files is You hear Farr's like that Excel That excel is X, which is X l s
01:49
X is the standard Excel file.
01:53
Then you have all the other ones like, for example, x L s M. Which is filed with Mac Rose and so on its own so that despite the lot of them, then you have all the pages which are in HTML, uh,
02:07
which is short for hypertext markup
02:10
language. And you have HD imaged ml image dian imaged email.
02:17
Then you can have XML files and then, uh which is something that is going to interest us. You have text files which can be in print, former text format or CS three, former C three shortcut for a comma separated values.
02:37
Then you have the list of all data sources and let's look at them in detail. So you have access databases files, so you have M D B and the A, C, C D B and A C C. D.
02:52
Let's not go into details about what these extensions mean.
02:58
Then you have query files. You have debates files. Then you have ah Meck rose from previous versions of X l X Element X L a
03:09
and then you have old workbooks off XL XL W and the Old Excel work sheets, which are excellence on and
03:21
excellence be.
03:23
You have work spaces templates ad in still bars. You have silk files so far Czar
03:30
Microsoft format for files that are meant to be exchanged between programs and what is also interesting ins. You can open back up files, which are created if you have the auto backup function,
03:47
these files can be interesting toe open. If, for example, you have been working
03:53
with the file
03:55
and then your PC crashed, and then you can restore the last saved version from the file.
04:03
So these are the types of files that they're able to be opened in Excel.
04:11
And, uh, let's open one of them. So let's go to this stuff.
04:16
So we have here, for example, comma separated value file,
04:23
and we can open it
04:26
and you get texting Port Wizard because text files
04:30
cannot be opened and excel without excel asking use and questions.
04:34
So, for example, CS Reza filed with the comma separated values. So every
04:42
time you want to skip from one cell to the other, you have comma.
04:46
That means that this file is delimited. You can start import a throw and you get the
04:55
codes page off the document you're going to import. So if you're importing something from I don't know Turkish or maybe
05:05
some serially click Russian or Serbian or whatever, you can chose this. But this one is plain,
05:13
so we're going to be important with standard unique codes. Page 65 double 01
05:19
on. And you can also say that your data has Heather's or not. So in this case, I know it has Heather's. So,
05:28
uh,
05:29
let's go to the next step.
05:31
So what are the deal? Emitters in Comma Separated filed? The limiters are comments.
05:39
Um,
05:41
deception means that if you have two commas next to each other, it will treat it as one. You don't want that because if you have an empty cell, you will have two commas and as one, and then actually is not going to import that cell. Or actually, it's goingto important Excel and move it to the left to the one
06:00
that should have bean
06:02
empty off course. If you have something else instead of commerce,
06:09
you can specify that
06:11
character
06:13
and let's go to the next step,
06:16
and then it gives you the data format off file you're goingto get. So is it the columns are going to be in general for Mitt or text or date or
06:30
whatever. Leave it to General. In general, this is the best approach. So if you want to do something else,
06:40
you should do it later.
06:43
Um,
06:44
so basically general commenced in America. Where does the numbers Day trail use? Two dates in all remaining values to text. So you just say finish
06:54
and you get
06:57
this file this file Don't worry about this. You just double click here.
07:01
It opens the
07:04
while, and what you get is a
07:08
my old mileage report for my business car.
07:13
So
07:14
here is
07:16
something that
07:18
get here. You get a total mileage. So it's an excel file.
07:25
It gets opened a year. You can just choose in which calm to start, and that's pretty much it. So it's
07:33
normal. Excel file. Now, let's just look
07:39
at that file
07:43
on dhe. See how it looks
07:46
as, uh,
07:50
it's a text. So this is comma separated values file in its native form. It so have just text and it separated by commas and That's it. And here you see here this
08:09
at the end of first line, you have two commas.
08:11
If we chose an option toe, ignore multiple the limiters. It would miss one cell. So that is the reason why. And this is how we open sea is free file in Excel.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor