Making a Mapping File
Before you learn how to use
openpyxl, we should cover one more topic related to making mapping files. You may have noticed that indexing columns can get a bit confusing since you’re only using the integer index in a lot of cases. To make your code more readable, you can put together a mapping file, which contains variable names for each of the field indexes.
Go ahead and create a new file called
Let’s take a look at what this would be like. To start off, you can have your product fields, and this is going to take into account the data that’s in the
sample.xlsx file. So first, you’re going to have your
PRODUCT_ID which will equal
PRODUCT_PARENT, which will equal
PRODUCT_TITLE, which is going to be
5—and make sure that’s
PRODUCT_CATEGORY, which is going to equal
6. To understand what’s going on here, you can go back and take a look at the Excel file.
Here, starting with the zero index, you can see that you have zero, one, two—and three is where the
PRODUCT_ID is. That’s why
PRODUCT_ID is storing a
3. Back here, the parent is in four, the title is in five,
01:22 and the category is in six. Now, if you need to call any of those indexes or fields, you can just import these variables and use them in the code to show which column you mean. Let’s go ahead and finish this up with the review fields.
Over here you’re going to have the
REVIEW_ID, which is in
REVIEW_CUSTOMER, which is in
REVIEW_HEADLINE, which is in
13, and the
02:02 So, this takes a little bit of extra work to set up, but if you’re going to be using the same structure of data consistently, this is a good idea to make your life easier in the future, and it will limit the amount of times you’re flipping back and forth between Excel and your text editor, especially if you’re trying to convert between letters and integers on the fly.
02:22 Not using files like this in the past is why I’ll never forget that M is the 13th letter in the English language, and T is the 20th. Hah.
Okay! Now that this is all set, you’ll import this file in the next lesson, and you’ll see how it all works. You’re only going to use the
PRODUCT_ID field but being able to produce mapping files like this is a good habit to get into.
Become a Member to join the conversation.