Consiliumsoft

Config.xml

This file contains a list of configuration files, each points to how ExcelImport should import that data.

ConfigFiles Attributes

Automated : true/false

If set to true, the application will exit when it is done with the imports. Otherwise it will wait for a keypress.

"ConfigurationFile".xml

Lets look at the first import, SimpleData.xls

A small subset of the data is to the right. We will use the configuration file to tell ExcelImport what to expect in the XLS file.

Example of SimpleData.xml

WorkSheets Attributes

ExcelFile: Path to your Excel file

ErrorLog: Path to an error file it will create if there are errors

ResultLog: Path to an results file when it is done

HeaderAtRow: Start the import at Row X

Database Attributes

Name: Your database name

ConnectionString: The configuration string to connect to you instance of SQL

WorkSheet Attributes

Name: Name of the workSheet

DBTableName: Name of the table (optional) - if optional, it will try to use Name as the table name

Identity: Creates an identity column (optional)

Column Attributes

Name: Name of the column in the spreadsheet

Type: Data type (String, Int, Double, Date are valid types)

DBColumnName: Name of the column (optional) - if optional, it will try to use Name as the column name

DBType: Data type created in the database (optional) - if optional, it will try to use Type to determine the database type. If type is String, then by default the database type will be [nvarchar](max)

Nullable: true/false - allows or disallows null

Note: Type and DBType must match

Type: String - DBType: [nvarchar](max) on some database type that represents a string.

Type: Int - DBType: int

Type: Double - DBType: float

Type: Date - DBType: date

Calc: You are able to calculate new values. For instance if you had [VALUE] * 10, it would the value it gets from the cell and multiply it by 10. Warning, I do not know how complex you an make this. Use at your own risk. Defining this option will slow down the import, I have yet had the time to optimize this.

Note: It is not mandatory to define all the columns, if you wish to import only the first 2, then define those. One is the minimum up to the number of columns.

"Script".txt files

Excel Import uses script files to create the database, insert data, etc. Since you may want to change the details of this, these are all defined in script files so you can modify them to your liking.

Script Files:

CheckDBExistance.txt - Check the existence of the database

CreateDataBase.txt - Creates the database

CreateTable.txt - Create the table

DropDataBase.txt - Drops database

Insert.txt - Inserts data

The application will replace key words patterns surrounded with {{X}} with appropriate values.

{{DATABASENAME}} is replaced with the database name

{{TABLENAME}} is replaced with the table name

There are a few more so be careful how you modify these, if at all. I would make backups of them before changing them.

Download

If you have SQL admin rights, you should be able to run this application with no changes. Be warned, poverty.xls has 14668 rows so it will take a while.

I tried to shake things up a bit so each xml configuration file has some different options.

Thanks to Paul Yoder for the LinqToExcel assembly

Written in C#

Requires .NET Framework 4.0

1.2 MB

ExcelImport.zip