Home

SQLImport is a flexible template-based tool to import/export CSV files to/from a variety of databases. The general idea behind it is as follows:

A programmer, such as myself, writes a template for importing/updating a list of products into the MSSQL back-end of a program that doesn't natively support this type of action. The script might look a little something like the following.

/*
* { Product import/update
* { Crucial information:
* { - Catalog and Product-Number
* { - Alternatively Product-ID
* {
* { Comments:
* { - Product-ID is automatically set if available
*
* @col000 = PRODUCTNR
* @col001 = CATALOG
* @col002 = PRODUCTID
* @col003 = ATTR1
* @col004 = ATTR2
* @col005 = ATTR3
*/

DECLARE @PRODUCTNR VARCHAR(20)
DECLARE @CATALOG VARCHAR(3)
DECLARE @PRODUCTID INT
DECLARE @ATTR1 VARCHAR(50)
DECLARE @ATTR2 VARCHAR(100)
DECLARE @ATTR3 VARCHAR(MAX)

SET @PRODUCTNR = :col000
SET @CATALOG = :col001
SET @PRODUCTID = :col002
SET @ATTR1 = :col003
SET @ATTR2 = :col004
SET @ATTR3 = :col005

IF @PRODUCTID IS NULL
BEGIN
    @PRODUCTID = (
        SELECT        PRODUCTID 
        FROM          PRODUCTS 
        WHERE         PRODUCTNR = @PRODUCTNR
            AND       CATALOG = @CATALOG
    )
END

IF @PRODUCTID IS NULL
BEGIN
    IF @PRODUCTNR IS NULL OR @CATALOG IS NULL
    BEGIN
        RAISERROR('Both product number and catalog are mandatory',16,0)
    END
    ELSE
    BEGIN
        INSERT INTO    PRODUCTS
        (CATALOG, PRODUCTNR, ATTR1, ATTR2, ATTR3)
        VALUES
        (@CATALOG,@PRODUCTNR,@ATTR1,@ATTR2,@ATTR3)
    END
END
ELSE
BEGIN
    IF @PRODUCTNR IS NOT NULL
    BEGIN
        UPDATE     PRODUCTS
        SET        PRODUCTNR = @PRODUCTNR
        WHERE      PRODUCTID = @PRODUCTID   
    END

    IF @CATALOG IS NOT NULL
    BEGIN
        UPDATE     PRODUCTS
        SET        CATALOG = @CATALOG
        WHERE      PRODUCTID = @PRODUCTID   
    END

    IF @ATTR1 IS NOT NULL
    BEGIN
        UPDATE     PRODUCTS
        SET        ATTR1 = @ATTR1
        WHERE      PRODUCTID = @PRODUCTID   
    END

    IF @ATTR2 IS NOT NULL
    BEGIN
        UPDATE     PRODUCTS
        SET        ATTR2 = @ATTR2
        WHERE      PRODUCTID = @PRODUCTID   
    END

    IF @ATTR3 IS NOT NULL
    BEGIN
        UPDATE     PRODUCTS
        SET        ATTR3 = @ATTR3
        WHERE      PRODUCTID = @PRODUCTID   
    END
END

Of course you can wrap all your statements into a TRY-CATCH block; but that is entirely up to the user. All the program does for you is prepare all the statements (i.e. map all the columns to the query) and execute it for every row.

Currently I'm using this in the following setup:
- Being asked to write an import to proprietary software with a MSSQL back-end, I write the template in MSSQL Server Management Studio
- Set up the mappings for SQLImport
- Test on our locally available server
- Put the script into our globally accessible template folder
- The end-user (still IT-personal; but not a programmer) imports the CSV into SQLImport
- Connects over OpenVPN to our client-network
- Connects to the client MSSQL Server
- And handily executes the queries.

Before we used to have this setup:
- Being asked to write an import to proprietary software with a MSSQL back-end, I write a PHP-script
- Quickly hand-write a PHP CSV-interpreter to the type of information I get
- Test-read it out to the console
- Sloppily write the SQL-Statement (because... who has the time... right?)
- Test on our locally available server
- Connect over OpenVPN to our client-network
- Connect to the client MSSQL Server
- Cross my fingers and close my eyes... And execute the queries.

Let me quickly point out the advantages:
- Reusability; I can put in more work for a more general purpose in less time
- Less sloppy
- Source out work (if I'm not available; the templates do still work the same way) whereas the PHP script were burried somewhere in my documents folder; not really reusable because of the arbitrary CSV format
- Clean CSV-parsing
- Built-in SQL Keyword highlighting

All these advantages, plus, I get to keep the full flexibility of (in this specific case) T-SQL.

Once compiled, this application can run on any Qt-Compatible System, including but not limited to, MS Windows, Linux, OSX etc. As of right now, I didn't include any builds in my git repos yet, since I'm not really familiar with what dll/so I'd have to include here. As far as I've tested, it successfully compiles under Qt4.X and Qt5.X Linux/Windows (32/64bit)