How to Import Data from Excel How to import data into JANUS from an Excel file using SQLExec 1. Table of Contents 1. TABLE OF CONTENTS 2 2. DOCUMENT REVISIONS 3 3. INTRODUCTION 4 4. METHOD 5 5. SUPPORT 18 2. Document Revisions Document History Version 1.0 Person RDP Date 12/07/2005 Remarks Initial Documentation 3. Introduction This document shows how to import data into a JANUS system from an Excel file using SQLExec. 4. Method To import data from an Excel file you will need to following; • • • • 4.1.1. Excel file (source data), A copy of MS Access, A copy of Database Explorer (used for testing query), Licenced SQLExec. Linking to an Excel file using Access As Excel is not the easiest file to interrogate using an SQL Statement I would suggest accessing the file via MS Access. This is done in the following way; • • • • Create a directory within your Winjan directory structure, I would suggest called ‘Import’. Copy the Excel file into this directory. Open MS Access and create a blank database within the same directory. Give it an appropriate name. Within MS Access make sure you are in the ‘Table’ tab on the left hand side. Then right click in the empty white space. This will being up a menu with the option to ‘Link Tables…’. Click on this option; When you select ’Link Tables…’, it will allow you to browse for a file/table. You need to change the ‘File Types’ option to ‘Microsoft Excel (*.xls)’ and then browse to the directory where your Excel source file is kept, typically ‘\Winjan\Import’. Once you have selected the Excel file, it will prompt you to set up the link so it knows what it is looking at. For this it is suggested that the first line of your Excel file contains headings that can link into MS Access; If you do not have headings like shown above, it will cause problems when you come to query the fields at a later date. For ease of set-up, if you do have control over the Excel data, then I would suggest using headings that match the headings in our cards table. Please view the SQLExec manual for field headings. Now you are happy with the file click ‘Next’; this will then prompt you for the name you would like to give the Link to the Excel table. I would strongly suggest using a name that is under eight characters and has no spaces. If you need to use more than eight characters, this is not a problem, but if you would like spaces, we strongly suggest you use an underscore. When you are happy with your name you can click ‘Finish’ and you should be presented with the following screen; To test the link has been successful; you can double click on the link file to see what data is available in the Excel Spreadsheet. If you get any fields with ‘Error of Num!!!’ being displayed, then there is a problem with the source data. This should be fixed before an import is attempted. 4.1.2. Creating a query Once happy with the data and the link, we need to create a basic query to select the fields that we require from the source data. The reason for doing this is mainly to get the headings correct so that when the data is imported everything links together correctly. You may also have source data that contains other fields that you do not want to be imported. The queries are different for each installation as the source data will never be the same (unless you are specifying the headings of the source data). If you select the ‘Queries’ button on the left hand side and then click new you should see a screen as follows; • • It is now down to you to create a query. The easiest way to create a query is to use the ‘Design View’ to create the columns you need. You must remember that when importing you MUST use the correct heading names for JANUS’ cards and/or Users table. If you do not the data will fail to import. To see the correct heading names please see the SQLExec manual available from our documents section from our web site or request it from a member of the technical team. Shown below is a quick query Designed in MS Access that is querying the source data and producing a result that SQLExec and JANUS can use; As can be seen from the above picture there are a number of fields that are not included in the source data. These include ‘Card Status’ and ‘Card Group’. The reason for including these is down to our database needing to know if the card is valid or not and where the card can go i.e. a card group that is present on the JANUS system. This can be checked if you go into the JANUS database and select ‘Card group’ from the Open menu and then view the Choices. It must also be noted that all JANUS fields are case sensitive and hence the Card group should be entered in the correct case. It should also be noted that if you are importing a Department into the cards table, the field MUST exist in the department table used by the database or else the import will fail. If it does fail, an explanation is given in the sqlexec.log file letting you know you need to add that Department to the JANUS Database. Now that you have a basic query you can save the query within access by simply closing the query design window. It will then prompt you for a name for the query. It is a good idea to name the query sensibly to reflect your source data and also attach ‘_q’ to the end of the name. This is personal preference but does help when you have hundreds of queries. Once the query is saved within Access it is considered a Stored Procedure. It can also be saved as a proper SQL Statement and saved in a text file. This is what MS Access will give you: SELECT test_data.Surname, test_data.Firstname AS [First Name], test_data.Title, test_data.EmpNumber AS [Emp Number], test_data.Issue, "Valid" AS [Card Status], test_data.CardNumber, "Standard" AS [Card Group] FROM test_data; This is not a true SQL Statement, more a variant created by Microsoft. To turn it into a true SQL Statement you need to change the []’s to “” etc. This is shown below; SELECT test_data.Surname, test_data.Firstname AS “First Name”, test_data.Title, test_data.EmpNumber AS “Emp Number”, test_data.Issue, ‘Valid’ AS “Card Status”, test_data.CardNumber, ‘Standard’ AS “Card Group” FROM test_data 4.1.3. Configuring ODBC Now that we are happy with the queries and understand the difference between Access queries (stored procedures) and a text SQL Statement, we can continue with the set-up for the databases within the windows environment. To see how SQLExec calls a stored Procedure or a SQL test query please refer to the SQLExec manual and refer to the cardimport.ini/sqlexec.ini settings page. To get SQLExec to access the source data we need to set-up an ODBC System DSN to access the MS Access file that we have just created. This is done by going into Control Panel, Administrative Tools and then ODBC Data Sources. You should end up with a screen like this one; You will not have all the links shown in my data sources so do not worry. You now need to click ‘Add’ and create a new link to a Microsoft Access Database: Once you have clicked ‘Finish’ you will get the following window; As can be seen, I am connecting to the Access table that I have created and given it a name. The description is not important but helpful when you have hundreds of ODBC links. Once all the fields are entered you can click ‘OK’ and it should now be set-up. Note that I put ‘_ODBC’ at the end of the link to show exactly what it is. This comes in handy when using Database Explorer and setting up the BDE links. 4.1.4. Configuring BDE As JANUS’s SQLExec uses BDE to access data we need to create a BDE link to link to the Import_Data_ODBE. This is done within the 32-bit DBE settings (Control Panel, BDE Administrator). You will first need to open the BDE and then make sure that it is in the ‘Databases’ window. Once there it should list all of your ODBC and BDE links that are known to Windows. To enter a new link you need to right click on the Databases and click ‘New’: When you have clicked ‘New’, you will first need to select the database type you are connecting to. In this case it is a Microsoft Access database. Once this has been selected you can enter the name for BDE aliases. I would advise using the same name but with _BDE on the end instead of _ODBC. Once you have chosen a name, right-click on the new entry and click ‘Apply’. Now on the right-hand side of the screen you have the option to select ‘ODBC DSN’. In there, please select the name of the System DSN that you set up in ODBC: Again, once the correct DSN is selected you can again right-click on ‘new BDE link’ and select ‘Apply’. This BDE link is now ready to use in SQLExec. At this point all applications/windows can be closed as we need to concentrate on setting up SQLExec to use the query we have made (be it in a stored Procedure or, as I will use, an SQL text Statement). It is assumed that SQLExec is installed and correctly talking to JANUS comms with the relevant SQLExec username and password set-up. If not, please contract Grosvenor Technology for more details. It is also assumed at this point that you know what you are going to import and if you are importing on a daily/hourly basis; hence your queries have been set-up and tested in MS Access to make sure you obtain the correct results. 4.1.5. Checking your queries work This can be done using database explorer and the BDE link that we set up a few moments ago in BDE administrator. Simply open Database Explorer, find the BDE link and double-click on. As you are using Excel it is assumed that no logon details are required and hence when prompted to connect to the device just click ‘OK’ to the logon box. If you were using another data source such as SQL Server or Oracle you would most likely require a logon to be entered. On the right-hand side you will see a number of tabs, one of which is ‘Enter SQL’. If you click on this field, the test query that was created earlier can be copied and pasted into the window and then executed by clicking the lightning bolt on the far right-hand side. If your query is correct, then a result should be returned from the Excel data. If not, then an error message will pop up stating what is wrong. Until you are able to obtain a result, the query can not be used by SQLExec. If you are lucky enough to get a result first time, then we can proceed to set-up SQLExec to use the query that you have created. 4.1.6. Setting up query for SQLExec to import data Once you have a query as mentioned above you can save the query as a .txt file or .sql file. This is then referenced by SQLExec when it is started. To tell SQLExec where to find the query you need to make changes to the file cardimport.ini (sqlexec.ini for older versions of SQLExec). The file looks something like this; [ExampleImport] Operation=V Alias=JanusData Dest=Cards SQL=C:\Winjan\TestQuery.sql The name stored in the square brackets is not imported, merely a reminder of what the query is doing. The Operation is what will be done to the resultant data that is returned by the query. The Alias is the BDE reference to where the data is. It will be the same as you used above to test the query in BDE Explorer. Dest refers to which table the data is being entered into and the SQL is the location of the file that contains the query that you tested above. Once you have amended the file as required SQLExec can be run. It is suggested that you open a command prompt, go into the winjan directory and type ‘sqlexec’. This way you can see what the application is doing. Each time SQLExec runs it adds information to a log file about what it has just done or any errors that it has found. This file is extremely important as it will state what any errors that you have while importing. If you are importing lots of data and not having much success, I suggest that you modify the SQLRUN.ini to generate a different file each time the application runs. Please see the SQLExec manual for details on how to change this. You should now have SQLExec set-up correctly to import data from an Excel spreadsheet using maximum error checking via MS Access. If you are still having problems and unable to import any data, I would advise re-reading this and the SQLExec manual so you are clear to what the application does. If you are getting errors from an import and have read the above and clearly think there is a problem with the system, please document exactly what you are doing and mail [email protected] including your source data, cardimport.ini (Sqlexec.ini in older versions), the log file and the query you are using. It is worth while noting that Grosvenor offers a commissioning service to set-up SQLExec on site at a standard daily rate + expenses. Please contact [email protected] for more information or a quote. 4.1.7. How to interpret your log file and what to do about it The log file is one of the most important diagnostic feature of SQLExec. It tells you exactly what the software did when it ran last. I have included a few examples on what you might get when running SQLExec. Error 1 – No Logon When running SQLExec you get the following; JANUS SQLExec - Copyright Grosvenor Technology 1999-2002 Version: 1.8a (IL 0.6100) Login Error - unable to login (user name or password error). An entry in the log file will not be created as the appropriate SQLExec logon has not been created in the JANUS database. Solution - Create a logon with the username and password of SQLEXEC. Error 2 – Comms not running The following error is gained if the comms program is not running JANUS SQLExec - Copyright Grosvenor Technology 1999-2002 Version: 1.8a (IL 0.6100) Error(s) encountered: Unable to obtain a licence. An entry will not be written to the log file as the application can not start. Solution – Start the comms program Error 3 – Fail to execute SQL Statment 16\05\103 15:53 - ///////////////////////// 16\05\103 15:53 - Starting 16\05\103 15:53 - ///////////////////////// 16\05\103 15:53 - Starting Query : ExampleImport 16\05\103 15:53 - ExampleImport: Error(s) encountered: Failed to execute supplied SQL statement. 16\05\103 15:53 - Statistics for complete run : OK/Failed 16\05\103 15:53 - Inserted Records: 0/0 16\05\103 15:53 - Updated Records : 0/0 16\05\103 15:53 - Deleted Records : 0/0 The SQL query that is being executed is written badly/does not make sense. Solution – Copy and paste the SQL Statement into Database explorer, as described earlier, and execute. You should get an error that will help you understand why the query is in correctly written. There are thousands of reasons why the statement will not execute, however, it is usually down to bad formatting or wrong source field names. I would suggest opening the file “C:\Program Files\Borland\Common Files\Bde\LOCALSQL.HLP” as it lists all the functions including examples of how to use them. Error 4 – Validation Failure 16\05\103 15:59 - ///////////////////////// 16\05\103 15:59 - Starting 16\05\103 15:59 - ///////////////////////// 16\05\103 15:59 - Starting Query : ExampleImport 16\05\103 15:59 - Error(s) encountered: Card validation failed. Error(s) encountered while checking data fields: [Card group] - 'DefaultCG' - Invalid [department] - 'IT' Invalid : Detail [surname=Pearson first name=Richard Title=Mr Emp Number=123 issue=1 Card number=999001234 Card group=DefaultCG Card Status=Valid Start date=#NULL# Expiry Date=#NULL# Expiry Time=#NULL# department=IT comments=] 16\05\103 15:59 - Statistics for complete run : OK/Failed 16\05\103 15:59 - Inserted Records: 0/0 16\05\103 15:59 - Updated Records : 0/0 16\05\103 15:59 - Deleted Records : 0/0 16\05\103 15:59 - Error(s) encountered: Validation failed on 1 records This happens when you attempt to import a record that contains a value that must already exist in the database. In the above case the database does not contain a Card Group of ‘DefaultCG’ and a department of ‘IT’. As mentioned earlier, these values are case sensitive and hence must be checked to make sure you are importing in the correct case. Where you see the value #NULL# in the log file this means that SQLExec is passing through an empty value. For any fields that you require to be blank, you should use #NULL# in your query. Solution – Add records to the database and re-import data. Error 5 – Record already exists 21\10\102 17:20 - Error(s) encountered: Card to be inserted exists. (-2) [Surname=Pearson First Name=Richard Title=Mr Emp Number=123 Issue=1 Card Number=999001234 Card Status=Valid Department=IT Card Group=DefaultCG] The above error message states that the record already exists in the database. This means that you are inserting data where the primary index is already in use. Solution – This will depend if the above is classified as an error, which is not really the case, it is just warning you the record already exists. If you would like to update the record, you will need to change the operation being used in the SQLExec.ini. Error 6 – Missing Data 24\03\103 07:58 - Card validation failed. The supplied key is invalid. [FIRST NAME]: '#NULL#' - Invalid : Detail [Title=Mr First Name=#NULL# Surname=Pearson Emp Number=#NULL# Card Number=999001234 Start Date=24/03/03 Card Group=DefaultCG Issue=1 Card Status=Valid] The above is gained if any fields of the primary key do not contain any data and hence are passed through as a #NULL#. Due to the nature of the primary key, when importing data values must be given to allow the record to be saved. This includes (for the cards table): • • • • • Surname First Name Title Emp Number Issue For the users table: • • • • Surname First Name Title Emp Number Solution – If there is no data for these fields, you are either best to pass though a ‘.’ or create a condition that, if a field is NULL, then use the value from another field. I have given an example; If(IsNull([Source_Data.surname]),[ Source_Data.card number],[ Source_Data.surname]) AS Surname, If the ‘Surname’ field is empty, then it will take the data from the field ‘Card Number’ from within a table that we are querying called ‘Source_Data’. If you see any other messages within your log file, there is a strong chance that they will be self-explanatory and easy to rectify. 5. Support Support Options Technical Support can be obtained from Grosvenor Technology from the following points of contact: E-mail : [email protected] Web : www.grosvenortechnology.com
© Copyright 2024