Jumbabox

Just like Access can create a linked Table to a text file, Excel can do the same using a very quick and simple method.

So lets get started!

Click on the Data > Import External Data > Import Data… option.

A Select Data Source dialog box will pop up. From the Files as type: drop down box select the Text Files (*.txt; *.prn; *.csv; *.tab) option, then browse to where your Text file is located and click the Open button.

You can also reference a Text file which is on a server so that anyone can access the Excel sheet and have the Text file still linked (assuming that they have permission to the server)

After clicking the Open button a Text Import Wizard will begin.

There is a few options available with Step 1 of the Text Import Wizard:

• Choose the file type that best describes your data:
  • Delimited : If you have data delimited in a certain way check this option (i.e. TAB, COMMA etc). In my example my data set is Tab Delimited so I have checked this option.
  • Fixed Width : If your data isn’t delimited you can use this option to specify where your columns start and end.
• Start import at row : You can choose which row you want your data import to start from. In this example I have it set at 1, as I want to include my headers.
• File origin : This is the encoding format of the file you are importing (Excel usually pre-populates this drop down box for you).

Click the Next > button when you are happy with these options.

The options available in Step 2 are:

• Delimiters : This is what character you have used to separate your fields.
• Treat consecutive delimiters as one : If your fields contain more than one delimiter, tick this option so that Excel picks up the one you have ticked in the above option.
• Text qualifier : If your values are enclosed by one of the available Text qualifiers Excel will take them as for exactly as they are, this is helpful if you want to include leading 0’s (usually Excel will remove leading 0’s).

As you can see Excel has recognised that my data set is Tab delimited and the Preview of the data represents that as well.

When you are ready click the Next > button again.

In this step of the wizard, the options available are

• Column data format : This will set the formatting of each column available, for example you can click on a column so that it is highlighted and then click on the data format option, you can also ignore columns which you do not want to import.
• Advanced… : Will pop up a dialog box asking how you want Excel to treat numbers in your data set.

You don’t need to worry about this step too much as you can perform all the formatting within the Excel worksheet

That’s it! Click the Finish button to complete the Text Import Wizard.

After clicking the Finish button a Import Data dialog box will pop up asking where you would like to import your data to, click the Properties… button before clicking OK.

After you have clicked the Properties… button the External Data Range Properties dialog box will pop up, this is where you can set up the properties of our range (linked Text file).

Here is a quick description of each option which is available under the External Data Range Properties dialog box:

• Name : The name of the range of the cells which the data from the Text file will fill
• Query Definition
  
  • Save query definition : This will remember the setup to query to the Text file, check this to avoid any future problems.
  • Save password : As the text file doesn’t need a password this option is greyed out. This is used when connecting to a database which has security, i.e. Access or MySQL
• Refresh control
  • Prompt for file name on refresh : Every time you choose to refresh the range a dialog box will pop up asking for the location of the Text file. Unclick this if your Text file will not stay in the same location.
  • Refresh every : While the Excel workbook is open you can have the range refresh at a defined interval.
  • Refresh data on file open : Every time the Excel workbook is opened the range will update.
    • Remove external data from worksheet before saving : This will remove the link between the Excel worksheet and the Text file once it is has been refreshed before saving.
• Data formatting and layout (For this method you will only have access to two of these options)
  • Adjust column width : When the range is refreshed the columns in the Excel sheet are automatically adjusted.
  • Preserve cell formatting : The formatting of the range will remain constant on refresh.
• If the number of rows in the data range changes upon refresh
  • Insert cells for new data, delete unused cells : This will insert cells and delete (keep formatting) anything that isn’t new from the refresh.
  • Insert cells for new data, clear unused cells : This will insert cells but only clear (removes formatting) anything that isn’t new from the refresh.
  • Overwrite existing cells with new data, clear unused cells : This will overwrite all exisiting data and clear (remove formatting) anything that isn’t new from the refresh.
• Fill down formulas in the columns adjacent to data : Any cells with formulas that are next to the range will be recalculated.

I decided to set up my range to these options.

After clicking OK, you will be brought back to the Import Data dialog box, if you are happy with where you want to place your import click the OK button again.

Now you should be able to see your Text file in the Excel worksheet!

You can also see that the External Data toolbar is now available. This is will become handy if you want to change the External Data Range Properties or Refresh your range.

Here is some information on each of the icons from left to right:

• Edit Text Import : This will ask for where the Text file is located
• Data Range Properties : This will open up the External Data Range Properties
• Query Parameters : This option is greyed out
• Refresh Data : This will refresh the range we just imported
• Cancel Refresh : This will cancel an active refresh
• Refresh All : This will refresh all imported ranges in the workbook
• Refresh Status : This will let you know the status of the refresh (good for large data sets)

Now every time your Text file has new data added or removed it should replicate in your Excel worksheet after a refresh.

That’s it! If you have any problems or suggestions please comment.

Enjoy!