Importing Data into SQL

 

If the data to be analyzed is not already in SQL, Cognalysis MultiRate's Import Tool may be used to import a variety of file formats into SQL. The accepted file formats are:

Flat File: Either a text file (.txt) that is delimited by a semicolon (;), colon (:), comma (,), tab (t), or vertical bar (|) or a CSV file (.csv). The accepted encoding types for each of these flat files include ANSI, UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, and UTF32-BE

Excel File: The acceptable Excel file extensions include .xls, .xlsx and .xlsm

Access Table: The acceptable Access database file extensions include .mdb and .accdb

 

Start the Import Process

To get started with the import process, select File -> Import Data, indicate the file type, and locate the file/database.

 

            

 

Once the data has been located, more information on how to access it is requested, depending on the file type. If it's a text file the delimiter of the file needs to be provided. For Excel files the user will be asked to select the name of the sheet in which the data is located. For Access tables the user will be asked to select the name of the table in which the data is located. For all data types but Access tables the user will be asked whether the first row contains column headers or not.

 

 

Describe the Data

The next window will give the user a preview of the first 15 rows of the data. Here he/she will update/provide column headers, indicate which columns to include in the import, and select a data type of either Text, Number, or Date for each column. The data type will intelligently fill in based on the content of the 15 rows that are shown, but it's a good idea to double-check that they're correct. For example, if one of the columns contains policy numbers most of which are made up of numbers, but of which some contain letters further down in the data, make sure that the selected data type is Text and not Number.

 

 

Note that in a column indicated as "Number" or "Date", all values that cannot be converted to a number or date respectively (like blanks and text) will be imported as NULL. Also, if there are any columns that contain the word NULL in text form, as circled in red in the image above (versus an actual NULL as circled in blue above), the user will have the option later on in the import process to either convert the string NULL values to actual SQL NULLs or leave them as strings. Only in the case where the analysis should treat string NULLs and SQL NULLs differently would the user choose not to convert them.

 

 

Import the Data

Once the user is comfortable with all the selections in the Describe your Data window and has pressed the "Continue" button, he/she will be asked to indicate the SQL Server and Database into which to import the data and the name for the new table. If he/she chooses to create a new database via the "<Create a Database>" selection in the database dropdown, he/she will be asked to name the database.

 

 

 

After completing all the inputs and pressing the "Import" button, there will be a notification of how many rows were successfully imported into SQL. If there are any NULL values in the dataset, the user will be given more information on the result of the data import. A column with mostly NULLs may indicate that the wrong data type was selected.

 

 

 

At the end of the Import process the user may choose to begin an analysis with the data that was just imported. If Yes is chosen, the analysis that was open prior to the import will clear out and the new dataset will be selected by default.