Downloads

Excel Import Wizard

Search

Excel Table of Contents

 Online Consulting

VBA Table of Contents

Excel Lesson 3A of 29: Import Wizard

Data can be extracted from 99% of the databases. The same is possible from specialized programs (financial, accounting, manufacturing, etc.) because they store the data in a database. These extracts can be made available to analysts in the form of a TXT, CSV, XLS file or any other type of files readable with Excel.

The set of data delivered can be a simple extract from the database, a set of subtotals and can even include some calculated fields. When you negotiate with the IT people see if they can do as much crunching of the data as possible. Generating data files is very easy and the task can be scheduled so that the data file is created daily, weekly or for whatever time interval automatically.

So if the data has been entered manually somewhere you should not need to re-enter it manually in Excel.

You can open directly the XLS, CSV, HTM or HTML (WWW) files. For TXT files and some other formats you need to use the Import Wizard.

To open these document with Excel you need to open Excel FIRST. From the menu bar you select "File/Open" and the usual dialog window appears.

At the bottom of this window you can choose to view "All Files" in "Files of type:". Once you see all the files in the directory you select the file that you want to open. The following window opens:

Delimited

In this first step you tell Excel if the document that your trying to open has delimited fields (columns) or not. Most of the files that you will try to open have delimited fields where date, product, account, amount, etc. are separated by either a comma, a semi-colon, a space or any other character.  So in step 1 you choose "Delimited" and click on OK.

In step 2 you need to tell Excel what the delimiter is either comma, space tab or other. The "Data preview " of the dialog window turns to this:

You see the column lines. Generally after step 2 you can click on "Finish" but sometimes you need to go to step 3 where you can tell Excel that certain fields should be text, number, date or other.

You can now save the file as an Excel file or copy/paste all the data in another Excel workbook. To do so adopt this approach: Select cell A1 go to "Edit/Go to/Specials/Current Region", back to the menu click "Edit/Copy", select cell A1 of the destination sheet and do "Edit/Paste Special/Values". Doing it this way instead of selecting the whole sheet you make sure that you are not importing 65,000 lines but just the cells containing data.

Fixed Width

If you are trying to open a file that doesn't have delimited fields or if you want to merge certain columns you will choose "Fixed width" in step 1. The "Step 2 of 3" looks like this:

Move the column lines as you wish then click "Finish" unless you need to go to step 3 to format certain columns (numbers, text, data, etc.).

Step 3

Sometimes certain numbers in the original file are text. To fix this problem you need to go to step 3 of the import process. The dialog window looks like this:

Select a column in the "Data preview" window and select a format in "Column data format".

Back to Excel Lesson 10 of 30: External Data

 

Click here to send your comments

  

Downloads

Excel Import Wizard

Search

Excel Table of Contents

 Online Consulting

VBA Table of Contents