Exercise: Use data conversions to import flat file data to Excel

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the Wise Owl course listed below!

Category ==> SSIS Integration Services  (18 exercises)
Topic ==> Expressions  (2 exercises)
Level ==> Harder than average
Course ==> Integration Services
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Open the text file in the above folder, and you'll see that it contains 20 people from the Sunday Times Rich List 2012 (can you spot the ringers?), with some errors:

Rich list 2012

Three of the records don't have any value filled in for the person's wealth in millions.

 

Your task is to import this data into the emptyish Excel workbook in the above folder:

Excel workbook output

The start of what the results should look like.

Whether you want to use the help overleaf is up to you! 

Here's a suggested data flow for this task:

Data flow for import

Be careful to test if LEN(Millions) equals 0, rather than whether ISNULL(Millions) equals true, since the millions column will be imported as an empty string, not a null.

 

Check your import worked.  Again, this doesn't claim to be the quickest method to solve this problem! 

This page has 0 threads Add post