Importing data to an existing spread sheet.
What happens where you have an existing temporary Master Log in an Excel spread sheet and you want to import more data. Well if it matches the existing structure it is easy, simply go to the first cell on the first blank row and select import then follow the import guide.
But what do you do if the structure doesn’t match or there are more columns than you want. Well, there are two methods available to you to resolve this issue. The first, not necessarily the easiest. Is to open a second sheet, import the data there and then format the data to suit before moving to the primary sheet using copy and paste. Once you are happy with it you can delete the second sheet, straight forward but can be time consuming.
Take the example below, it does not match our existing spread sheet master log. So the objective is to append only the bits that we want to the log we have created, now this is test data but from memory it was downloaded from one of the on-line log sites.
The Import Process.
This is a simple if somewhat long winded process, listed below are the high level steps. There is nothing overly complex, just a simple bring it in and reformat it.
- Open a second sheet.
- Go to the first blank cell, this should be A1.
- Select the import icon, this will open the impot dialog.
- By default CSV file should be checked, if not check it.
- Click the import button.
This gets the contents of the directory, allowing you to select the file.
Once you click the import button, you will be taken to the dialogue that will allow you to actually import the file to the new work sheet.
To move onto the next step, we just have to select the file and click get data – this will then bring up the actual import data wizard.
This gives you a preview of what the import wizard will do with the data, for the purposes of this exercise we just want the raw data and we will get it into the order we want. As you can see, a sample of the data is shown in the small window – just for validation purposes. Clicking next will display the data in a clean format, this indicates the structure that Excel will apply.
The next part of the import wizard will give a number of options, for our purposes we just want to import the data. You can omit columns for import here and you can assign data types as well, however you may have to go through a number of itterations before you are happy with with outcome. So at this point we are just going to accept the defaults and go with what Excel decides.
Clicking the finish button on the screen above will take you to the final stage of the process, which is a check that the chosen cell is the correct one.
When you are satisfied that all is as you expect, simply click the OK button and allow Excel to do it’s thing – it should look something like the screen shot below.
Make the changes you need, which in this case is delete the columns not required and copy and paste to the master log sheet. When you paste into the master log spread sheet, don’t forget to choose match destination formatting. This will centre the columns or justify them the way the master log spread sheet looks just now.
In Part 5
I will cover bringing the CSV directly into an existing temporary master log, using the import wizard. It will probably be the final part of this series, although I may look at the possibility of creating a spotting dashboard later with more functionality.
All Photographs Copyright © Dave Munro
Post Author – Dave Munro