In this lesson,
we'll be seeing some of the common methods of reading data into Excel.
Namely, when the data is presented as a regular Excel file or it is presented as
a text file, where the text is delimited either by a comma, a tab or a space.
Before we do any processing of data,
we need to properly bring it into the software.
We will look at two common input formats.
Namely, the regular Excel data file with a .xlsx or
.xls extension.
And text files with a .txt extension.
Within the text files, we'll further look at a comma separated data file,
a tab delimited data file and a fixed width data file.
The data that we'll be using comes from a store in Canada selling office supplies
online.
The data consists of orders received by the store over a period of time and
contains various variables associated with the order.
Let us start by reading the Excel format data file.
So let's open Excel and read this file.
So if you click on the file Store Sales,
which is an Excel file, Excel reads in the data file.
[COUGH] And notice the various variables are organized in these columns.
The very first column is an Order ID,
which is an identification mark given by the store to every order received.
The next column is the Order Date, the day on which the order was received.
The format of the date is month, day and then year.
The priority of the order.
The store attaches either a low priority, a medium priority, high priority or
a critical priority to every order.
And some of the orders do not have any priority attached to them.
The quantity of order, the number of units ordered,
the total sales in terms of dollars, the shipment mode for the order.
So the store ships the products, either by a delivery truck, regular air, or
express air.
The shipping costs involved in dollars, the province or
state within Canada to which the order is to be shipped.
The customer segment.
The store classifies its customers either as individual consumers,
small businesses, home offices or corporate clients.
The product category.
There are three broad product categories, office supplies,
technology products, and furniture products.
The sub category within the main product category.
The container in which the order is shipped.
It's either a small box, a medium box, a large box, a rab bag or a jumbo drum.
And finally the date of shipment of the product.
Next let us read the data into Excel when the data is presented in a text format.
And in particular a text format which is a comma separated format.
So such a data if you would read in any of the text editors,
this is how the file would look or this is how the data would look.
Notice it's the data is all arranged in one big row.
There's different rows of data there.
And every data is separated by the other data by way of commas.
Hence the name, comma separated text format.
So let's see how do we read this data into Excel.
So let's open Excel, and we'll be reading
the data in the text format, in particular a comma delimited format.
The store sales, comma delimited file.
So we click this file.
Excel guides us through its Text Import Wizard.
And notice we need to choose the delimited option,
because we are reading a delimited file.
So we choose delimited option, do a Next.
And in particular, among the options of delimiters, since this file is comma
separated or comma delimited, we choose the comma as a delimiter.
Then do a Next.
And then do a Finish.
So notice the file or the data has now been read into Excel.
But also notice that the column widths have been reduced.
So to get back our original column widths we select all the columns, do
a double-click on any of the column, and we get back our original column widths.
Next let us quickly see how do we read the data
when the delimiter is a tab rather than a comma.
So now the data is in a tab delimited format.
So once again the process is the same.
Let's open Excel spreadsheet.
Let's go to the file Store Sales, tab delimited text.
Click on it.
Again, the Text Import Wizard opens up, and since it's a delimited file,
we choose the delimited option, do a Next.
But now notice amongst the set of delimiters,
we choose the tab as a delimiter, because this is a tab delimited data file.
Do a Next, do a Finish.
And once again, the file is all read into Excel.
We'll rearrange the column widths by selecting all the columns,
doing a double click on any of the columns.
And we get back our column widths all properly arranged.
Finally, let's read the data when the data is presented in a text format.
But now, rather than being delimited by a comma or a tab,
the data is in a fixed width text format.
So, once again let's open Excel Spreadsheet.
And let's read the file, let's click on the file, Store Saves fixed width.
Now notice since the data is not delimited, but rather it's a fixed width,
hence we need to choose the fixed width option there.
So choose the fixed width option.
Do a Next.
And now what Excel has done is Excel has put in these column separators.
And we have an option to change or modify these column separators.
So let's quickly glance from left to right taking a look at these column separators.
Note there is the column separator for the shipping mode.
We do not need this particular column separator.
So we can remove it by double clicking on that.
Further, we also do not need the column separator that Excel has put in for
the product container.
So we can remove that too.
Do a Next, and then do a Finish.
So once again the data is read into excel and
we can change the column widths to get back the in its original format.