Productivity Tools

Tools written by an IT business manager, a passionate developer.

Import Excel to SQL

The module takes Excel files from the given directory and imports it to SQL Server database.

I like to have data in the database. I like to make joins on views on it. For example, I have a table of my team members and different tables which represents their attendance at conferences. On those data, I create views for each year.

Having data in the database is great, but inserting data into it is not so simple task. So I am using excel files for it. I store a bunch of excel files that contain raw data and I am importing them to the database.

This module gets all excel files from the given directory. For each excel it creates table and import data with a similar structure as in excel.

It exposes only one command Import-ExcelToSql.

Examples

Import-ExcelToSql -SqlInstance ".\sql2019"  -DatabaseName "EcoVadisDT" Import-ExcelToSql -SqlInstance ".\sql2019"  -DatabaseName "EcoVadisDT" -SchemaName "work" -DropDatabase -Verbose

Below you can find a couple of the diagrams which represent steps performed.

Diagrams

General Diagram

The module performs three steps: – Creates structure in the database – Performs data import from excels to SQL Server – Invokes additional scripts

CreateStructure

This part creates SQL server schema – It creates the database, if Drop database switch will be used, the database first will be dropped – It takes all excels from the given directory – For each excel it creates the same table as the excel filename – For each column in excel it creates the column in the table

ImportData

Imports data opens again those excel files and imports data to SQL data table to previously created schema

Additional SQL Scripts

Module doesn’t recognize all data types, so sometimes after the import, I need to make data operations. For example, I am changing VARCHAR type to decimal for chosen columns

Close Bitnami banner
Bitnami