Importing Data to an existing spread sheet.
So you’ve got a master log, it doesn’t really matter where. Either on-line or off-line, for on-line – you can read “in the cloud”. And for off-line, well that is on your local device and given the power of hand held devices that could actually be a tablet or even a smart phone.
So this post is a quick insight into importing data into an existing spread sheet. For this series of posts I have used Excel, but in truth it could technically be any format. This post is a bit different, it will need an application with some specific functionality around the import mechanism.
What is needed?
For this post we need the ability to import data whilst simultaneously changing column order and applying formatting. While most spread sheets have this capability, many other applications do not.
The example spread sheet.
The example spread sheet that we have used is basic, but it is enough for a master log. We have already covered the import and manual fix of data, but this next part covers using the wizard to do the grunt work for us.
So our basic spread sheet has only a few entries, we want to import more – but there are differences in the layout and that is what we will address in this example.
Starting the import.
Before we start the import process, remember to backup your existing master log. This is good practice anytime that you are about to do something unfamiliar, it is easy to go back to a good copy if you do.
To start the import, we have to open the master log spread sheet, then open the import data dialogue. We set focus on the first cell in the first free row and pick the CSV file, then begin the import process. It is straight forward, but does require a little thought and care.
This will take you into the import Wizard, where we can make our selections.
Selecting what you want.
This section is about the file you are importing, it does alow you to change some of the import parameters. In this case we would accept the defaults, but if the import file had a header row we would start the import at row 2. You can import what are known as fixed width files, these files have the data aranged in columns – normally with tab or space separation.
Selecting next will take us to the next step of the wizard, where we can alter things like the delimiter and if required input a text qualifier. These steps are not normally required, but as a check you have the Data Preview window. This is what Excel intends to do with the data, what you are looking for here is that the data layout is as you would expect. You can use the sliders at the side and bottom to examine the data in more detail.
If the data looks OK.
If the data looks OK, we can move to the next stage – just click next. This takes you to the next step of the import wizard, where you can select what columns you wish to import. It is possible to leave columns out, but you should be aware that the import will just move everything one column left if you select a column to be omitted.
To compensate for this “feature” in the older version of Excel that I have, I will leave a column in place and simply delete the contents when I have completed the import. It is straight forward, I this case I will leave the column where the contents are “gull04” – this will fill the location column as that data wasn’t in the download from the on-line master log that I tested.
To select the columns, in this case we wish to omit column 4 and column 6. Simply click on the column data in the Data Preview box, then check the do not import radio button – repeat for each column.
Everything selected?
If you are happy with your selections, off we go and get the data – for reference I have completed imports of hundreds of thousands of records using this method. Below is a check list that you should quicky run through, to ensure the best chance of getting it right first go.
- You have selected the first cell of the first blank row – in this case A28.
- You have an understanding of how the file is constructed and what the separator is.
- You know what columns you don’t want – the columns to be omitted.
- If you have to omit columns, make sure that the columns that move left are correct and required.
If all is good, then click the finish button and bring the data in. This takes you to the final check, where you can just verify that all is OK.
If it looks right!
So the final import data has one check that you must verify, that the target for the first cell of data is correct. In this case it is A28, which is where we want the data to start. If that is correct, we simply click OK to bring the data in.
As you will see in the example below, we have imported the data and it all looks good – with the exception of the entries in the location column. In my case sorting that out isn’t that much of a problem, you can use “Find and Replace” (Ctrl + f) to correct it. Where we find “gull04” and relace it with nothing.
A short explanation.
Further back during the import process, I could have picked column F – where all the entries were “unknown”. But I noticed that there were entries in the last column which also contained that data, which I wanted to keep. A global search and replace would have deleted these entries.
Happy with the look?
So if you are happy with what you see, then you can click on replace all. You can of course click on replace and do the actions one at a time if required. Now you should see that the whole column has been cleared, from row 28 onwards.
What Excel did.
As you can see, Excel has made a number of replacements. Just click OK to close the dialogue box and the results should be what you wanted, a cleanly formatted copy of your master log. What can you do with it, well should you decide to put your master log somewhere else. This temporary master log can be easilly exported as a CSV and imported into where ever you want.
What benefit is there?
Why would you want to do this whole Import/Export thing, well I can detail a couple of good reasons – but there are probably others.
I have application backups.
Having applicatiom backups is good, any backups are good in my opinion. But, few people and this includes professionals. Will actually restore a backup to ensure that it is valid and to understand and be familliar with the process. In my opinion, an untested backup is little better than no backup.
Some things that can go wrong!
Understanding where things can go wrong and catch you out. This is important, below is a list – it is just as an example and I’m not saying it will happen. But experience has taught me that it can happen, however for your information.
- Web sites can and do just vanish into the ether, it is why sites like archive.org are around.
- Web sites can fall prey to bad actors, or be targeted for ransomware and all can seem OK for a long time.
- An application on your PC can stop working for a variety of reasons, like patching changes something.
- An application may not run on a later version of the operating system.
- The company that created the application or web site may vanish, be bought out or just stop supporting the application.
- At an individual level, key people can leave, retire or just stop supporting things.
The list goes on, without some kind of backup – preferably more than one copy. You are exposed to some or all of the above, the web site or the application may be someone elses – the data is yours. So plan accordingly and take care of it!
All Photographs Copyright © Dave Munro
Post Author – Dave Munro