Wednesday, September 30, 2009

HEAT: Import Excel to HEAT tables

A client wants to know: We have alot of data for our new HEAT system in Excel worksheets used to export from our previous help desk software. How do we get it into HEAT?

As an example, let's use Call Type as an example. There are a number of ways to move data from Excel to SQL tables; 1) HEAT's import/export function; 2) SQL DTS function and 3) an Access database. We will cover the Access database method:

First, as an backup of the Call Type table, open Administrator
Select Database > Export > Select Call Type > Browse to a folder
Save as Call_Type_ Export > the Call Type table is now saved *********************************************************
Open Access > Create a blank database
Select Tables > File > Get External Data > Import
Browse to your Excel worksheet > Select the Call Type worksheet
Click OK > Your Excel worksheet will become a table in Access
Select Tables > File > Get External Data > Link Tables
Change the File Type to ODBC Databases() > Select HEAT DSN
Select the Call Type table > this becomes a linked table in HEAT

Click Query in Access > Select New Query > Design View
Add the CallType worksheet > close table window
Select Query > Change to Append query > Apppend to dbo.CallType
Match the Excel Fields to the Call Type fields below
Close and save the query >Select the query
Click Open > it will tell you that is going to append x records
Close the Access database after the append is complete


Open Call Logging and select Category. The contents from the Excel worksheet should now be in the Call Type drop down.