Relational eXcel is a comprehensive data transformation and data loading tool to help you work with your Excel, Access, CSV, or text based data and load it directly into your database server, or create SQL bulk insert script files.

Although Relational eXcel can be used to directly import a table from a workbook into a database, what sets it apart from the competition is that it can also transform that data on the fly and even add columns you didn't have in the original workbook. If you use Microsoft Excel, flat files, or Access as a front-end to some of the data stored in your database, but your schemas do not match exactly - for example, if the data in your Excel file is denormalized, whereas in your relational database it is normalized with foreign-key references and so on - then Relational eXcel is the perfect tool for the job.

Traditionally, when data needs to be read into a database but the database schemas do not specifically match that of your input data, a two-step process is used. Data is first read into temporary tables in the database, and then transformed using custom SQL script files. Writing these script files is a resource-intensive and error-prone operation. Relational eXcel solves this problem by transforming the input eXcel data on the fly before writing it to the database, thus eliminating the need for intermediate tables and custom SQL transformation scripts.

• Read in data from spreadsheets in Excel 2008, 2005, or earlier formats; Microsoft Access Databases; delimited text files
• Output data to ANSI SQL script files or directly load it into an SQL Server or Oracle database
• Specifically define where the data is located in your workbooks: define the worksheet, the header row, the start and, optionally, the end row
• Define criteria to govern which of your input rows will be read in (only read a row in if a field is filled in, or a value in that field has changed, etc.)
• Only read in parts of the data in a cell using regular expressions. For example, if your cells contain the text "Mr. Neil Armstrong (age: 53)", then you could read the title, "Mr", into one column in your database, the first name into another, the last name into a third, and the age into a fourth
• Generate ID columns with auto-incremented values or GUID-s generated by the application
• Create foreign-key columns that reference data in other tables in your worksheet
• Map certain values to other values. For example, consider a column that can contain the values "Yes", "True", "Ok", "No", "False". You could create a map to read cast these values to the appropriate bit value.

Relational eXcel is built for performance. Provided you have decent hardware, rows can be read in at around 3000 rows/second and written to SQL files or the database at around 10,000 rows/second. Foreign-key type columns are heavily optimized with values looked up using dinamically generated hash tables. The Xlsx/CSV/Access DB reader module was written from scratch for speed. A workbook with several worksheets containing hundreds of thousands of rows can be read in under 10 seconds.

• Microsoft Windows XP, Vista, 7, 2008 Server
• Microsoft .Net Framework 3.5 SP1 OR the .Net Framework Client Profile (free download from Microsoft.com, or automatically installed during the installation of Relationel eXcel)

To illustrate the power and flexibility of Relational eXcel, consider this example. You have two tables on a worksheet, one for employees, and one for branches of your business.

You wish to import this data into your relational database so that your end result is the following.

The above transformation could be done on-the-fly by Relational eXcel. In particular, the following features would be put to use:

• ID columns
• Regular expressions to extract the title, first name, last name, address, state, and zip code into separate columns
• Mapped columns to read in values such as "Woman" or "F" to one output value of "F"
• Datetime column to interpret both date and text type excel cells as proper "datetimes".
• A foreign-key column to map the "manager" of the branch to the generated ID of the employee, matching on the LastName output column