APPLICATION - Migrating Records from a Text File to an Excel Workbook

Python 3: Automating Your Job Tasks Superhero Level: Automate Excel Tasks with Python 3
18 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Hi again and welcome to this lecture. Throughout this lecture, we are going to build an application that will migrate all the data inside a text file as you can see right here to an Excel spreadsheet. Basically, the application will create the sheet itself the file, the table within the sheet, the columns and rows will split this data inside the text file using the semi colon as a delimiter. Then the application will apply some styles some visual styles on our sheet on our table. And we will have column headers, filters, various colors, row stripes and column stripes as well for better readability of the table. And at the end, the application will identify all the salaries inside the table that are greater than $55,000.

And for those particular cells, it will make the text within the cells red instead of the default black Also bold and italic, so we can have a better view over the employees that have the greatest salaries. Okay, so we will consider this text file right here, which has seven columns ID, first name, last name, department, phone address and salary for each employee. And then the values for each of these columns are separated by a semi colon that as I said, we will use as a delimiter when splitting each row of this file, okay, for now, keep in mind that immediately after this video ends in the next lecture, you will be able to download both the application code itself and this file so you can work on it and test it as well. So let's start working with this file. And let's start the migration process to an Excel workbook.

In order to do that, I'm going to open up the Python interpreter. And first of all, as always, we are going to import the necessary modules. So before anything else we have from open pi Excel, we are going to import a class name workbook next also from open pi Excel dot styles, import all in order to have available all the necessary styles that we will apply on each cell that meets the condition of having a value of over $55,000. Okay, and finally, we also need to other classes. So from open pi Excel dot worksheet dot table, we are going to import table and table style info. Okay, the next step is to open the text file that we've just seen for reading.

So I'm going to create a variable. txt underscore file equals open and in between the parentheses of open and also in between double quotes. I'm going to enter the full path to our file. So the backslash Excel app, backslash employees dot txt, this is a text file you've just seen. Okay, next we're going to create an empty list for storing the records as a list of lists. So we are going to store all the data inside the text file in the form of a list of lists.

So let me create an empty list records, let's say equals open close square brackets. And next we should make sure that we are reading the file from the very beginning. So txt file dot seek of zero as we already previously did inside this course. Okay, now it's time to have a look once again at our file. And as I said, we are going to split each line in the file by the semi colon delimiter. And then we will append each list generated by the read lines method to the new list called records.

So in order to do that, I'm going to write a for loop. So for record in text file, dot read lines, colon records, the name of the list dot append, we are using the append method and in between its parentheses. We are going to Enter record.or strip of backslash n. So we are going to strip away any newline characters at the end of each line in the file, and also dot split. And as I said, we're going to use the semi colon as a delimiter. Okay, let's hit Enter. And now let's check our list records.

And indeed, you can see that we now have a list of lists, where each element of the outer list is a list made up of the elements that were previously separated by a semi colon. Okay, so at this point, we've read the data from the file, we have populated this list called records. Now it's time to create our new workbook. Since as you can see right here in the Excel app folder that I have created, there is currently no Excel file in this folder. Okay, so I'm going to create the workbook right now. So workbook equals and now we are going to call the workbook class work book.

Now Next we are going to set the path or location where we want the new Excel file to be saved. So we are going to create a variable called file path equals. And now I'm going to paste in the path to the file, or actually the location where we are going to store this file. So the Excel app, my company stuff dot XLS x, this is the name of the file that we're going to create. Also note that we should escape the backslash characters inside the path using another backslash, so we would have a double backslash in order to avoid any conflicts with pythons special characters, like a backslash n, or a backslash T. Okay, so we have defined the file path the location of the file, the only thing we have to do in order to have our file created is workbook dot save of file path.

So we are going to create and save the file to the specified location. Now let's check the folder once again. And you can see that the Excel file has been created. So great job thus far. At this point, we have a new workbook on our file system. And each new workbook created by the open pi Excel module has by default, a single sheet called simply sheet.

In order to verify this, I'm going to use workbook dot sheet names. And as you can see, we have a list with a single element which is sheet. Okay, now let's assume that we want to rename the default sheet to let's say employees. In order to rename this default sheet, first, we have to reference it. So sheet equals workbook. And in between square brackets, we have the name of the default sheet which is sheet.

And now we can just do sheet dot title. And now we can enter the new name of the sheet, let's say employees. Okay, great. Now it's time to populate the sheet with the data we currently have inside the records list. Also note that the first Throw inside the text file. So this one right here will be considered the table header inside the Excel spreadsheet.

And that is just what we want because we want to have a properly formatted table with a header and then the rows containing the data. Okay, so now we can use this list of lists in order to populate our sheet. And we are going to do that using of course a for loop in order to iterate over the entire list. So for row in records, basically for each list inside the list of lists, colon, sheet, dot append of row, Okay, at this point, we should have the data migrated to the file. However, we want to use this data in order to create a table and then format it using different styles. So for that, we should create a table inside our sheet.

In order to create the table inside the sheet. We can just do table equals and now we use the table class. We import it previously. Inside the parentheses of this class, we enter two parameters, display name, which is a mandatory parameter equals table, let's say. And this is the most important parameter right here, ref equals. And now we will define the array inside the sheet that will actually contain the data itself.

Now looking at our text file, again, we know that we have seven columns, and also 11 rows. So that would be columns A to G, and rows one to 11. In order to define this array inside our code, we will use cell A one up to cell g 11. And that will basically be our table. Okay. Of course, if you would have had 100 rows inside your text file, then this array would have been a one to G 100.

But in our case, this is the array. So let's hit enter. The next thing we should to do is define a style for the table, meaning we can pick one of the default styles and specify the default style name. And also, we can specify if we want row and column stripes within our table in order to have everything look neat and pretty. There are multiple default styles for creating a table using the open pi Excel module. And in order to see all the styles, you can check out open bi Excel dot worksheet, dot table dot table styles.

Okay, we got an error because we haven't imported the entire open pi Excel module. And I'm going to do that right now. Since we are in a testing environment anyway, so let me try this again. Okay, and these are all the default table sheets inside the open Excel module. Of course, you can test which style suits your taste and your needs for your table. I'm going to choose one of these default styles, namely, table style, medium nine Because I like that one.

So let me show you how to define the style for the table. We have style equals, and now we use table style info that we previously imported. And in between these parentheses, first of all, we should specify the name of the default style that we want to use. So name equals, as I said, table style, medium, nine, comma, and we can also add additional options. For example, show row stripes equals true and also show column stripes equals true as well, in order to have a pretty and appealing table as a result. Okay, so this is the style we defined.

Now it's time to apply the style to our table. And for that, we are going to use table dot table style info equals style that we just defined. Okay. And of course, we should add the newly created table to our Because thus far, we have defined the table, we have defined the style for the table. And we have also applied the style to the table. But at this point, the table hasn't yet been added to the sheet.

So in order to do that sheet dot Add table, and in between the parentheses of our table with specify table, which is actually this instance right here. Okay, finally, we assumed that we want the salaries that are greater than 55,000 to be shown in red font instead of the default black font, and also as being bold and italic so we can see them better inside the table. For that, first we should define the font and also specify the bold and italic options. In order to do that, let's create font equals font color this attribute equals colors dot red, because I said that we want to have a red font for this particular cells, comma bald eagle Through comma, italic also equals true. And we want these font settings applied to the cells that meet the condition salary greater than 55,000. Now looking at our data again, you can notice that rows two to 11 are the rows containing the actual employee data.

And the salary will be the seventh column in this table, which is column G. Now since we want to take into consideration rows two to 11. Inside this table, we can use a trick by using the range function to iterate over the cells inside the table inside column G, which is associated with the salary. So in order to do that, I'm going to use a for loop for let's say cell number in a range of two up to but not including 12. So that would be 211. As I said, colon, now we should specify the condition if and now we are going to use The int function because we will have a string inside each of the cells on column G. And in order to evaluate if the value inside each cell is greater than 55,000 or not, we should have an integer. So int, and in between the parentheses of int, do you remember how we would reference a cell inside the sheet, it would be sheet of let's say in between square brackets, we would have b 10.

For example, well, in our case, since we are iterating, over a range, and we want to consider all the cells inside column G, we will have G, the column identifier. And now we will use a string format operator percent s, then we will use the percent operator and in between parentheses, we will use cell underscore number. So first of all, we will evaluate cell number g two, according to this range right here. That would be G and two maps to the value inside the parentheses. Then for the next iteration, we would have three extracted from this range, so we would have G three, and so on until this range is exhausted. Okay, so we have sheet of G to, for example, dot value, because we want to refer to the value inside that cell.

So if this value is greater than 55,000 colon, then we want to apply this font right here to each particular cell that meets the condition. So in order to apply a font to a cell, we would usually have a sheet of let's say, g 10 dot font equals this name right here, font. But since we have an iteration, and we are iterating, over all the rows, meaning to 11, then we will use the percent operator yet again. So percent s, and again, percent and cell number dot font equals font. So again, we are using this range right here two to 12, which actually means to up to and including 11, then we are evaluating the values inside each of these cells. So if the value is greater than 55,000, then we are applying the font that we have just defined up here to each of those cells meeting the condition.

Okay, let's hit Enter. And now the last thing we should do is, of course, save the workbook. So workbook dot save, file underscore path. Okay, of course, you should remember to also close the file that we used for reading the data, the txt file, so txt, file, dot close. And now let's go to the File and see if all of our changes all of our settings have been applied to the file. So first of all, you can notice that the file has been created my company staff dot XLS x.

Now let's open the file and let me zoom in a bit. First of all, you can see that all the data inside the text file right here has been successfully migrated to this file to this sheet and to this table, the first row inside the text file has been converted to the header of this table. We also have filters available if you want to filter our data. Next, let's notice the style applied on this table. So we have the column header with the color different than the rest of the table. We also have row stripes, you can see that we have alternating colors for the rows inside this table.

And also we have columns stripes. You can also see that the design of each column alternates as well. Finally, inside the G column, the column containing the salaries, you can see that all the salaries that are greater than 55,000 are now written in red, and the text has also been converted to a bold and italic version of the initial text. Okay, now let's close the file. And I'm going to delete this file. And I'm going to show you the application containing all the code that we discussed inside this lecture.

So this is the actual Excel application.py file, in which you will find the code that we just executed throughout this lecture. And as a side note, I have also added some comments in order to have everything clear. Of course, as always, I strongly recommend you use as many comments as necessary inside your code for later reference. Finally, in order to prove that this application works as a whole, I'm going to open the windows command line. And I'm going to run this application so Python and then the path to the file, Enter. And now if we check our folder, the file has been created.

Once again, opening the file, we see that we have the same result as we've seen before, so our application works correctly. Okay, having that said, I hope you enjoyed this lecture and migrating data from an ugly text file to a clean and pretty Excel workbook, and I hope you're going to find all the skills inside the section useful for your career. I'll see you soon.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.