Skip to content

Latest commit

 

History

History
138 lines (102 loc) · 3.2 KB

README.md

File metadata and controls

138 lines (102 loc) · 3.2 KB

CSVtoSQL (from 0.4 EXCEL XLSX support)

Description

This little dirty application will help you to convert CSV file into set of SQL commands. To do that you can

  • provide input file in csv format (with any separator) or EXCEL file (XLSX format)
  • define template file
  • CSVtoSQL will process each line and generate ouput file where script will be saved
  • later you can use for example osql to execute this statements

Options

Options:
  -i, --input           (Default: input.csv) CSV or EXCEL file to be processed 

  -o, --output          (Default: output.sql) FILE with Output

  -t, --template        Required. (Default: template.txt) Template file where
                        data will be replaced form input file . Sample Template
                        text  insert into mytable (column1,column2) values
                        ({0},{1})

  -s, --csvseparator    (Default: ,) CSV Separator any char or \t for tabulator

  -l, --line            (Default: 2) Start form Line

  --help                Display this help screen.

Error Levels

You can catch error level in batch file, error levels: 10 - ERROR 0 - SUCCESS

SAMPLES

Use tab character as column separator

CSVtoSQL.exe -t template.txt -i input.csv -s \t

All parameters provided

CSVtoSQL.exe -t mytemplate.txt -i data.csv -s \t -o myoutputfile.sql -l 4

Use in batch file

Steps

  1. delete output file
  2. run cnverter CSVtoSQL
  3. handle error if occure or run command on sql server
@echo off
echo Delete file
del output.sql /q

echo run CSVtoSQL
CSVtoSQL.exe -t template.txt -i input.csv  -o output.sql

if errorlevel 10 (
   echo Failure Reason Given is %errorlevel%
   echo Handle Errors Here
) 
else
(   
   osql.exe -S %SERVERNAME% -U %USERNAME% -P %PASSWORD% -d %DBNAME%  -i output.sql
)

SAMPLE FLOW

Each row and column in csv is processed using temaplte file

Sample data file data.csv

header,header
column1row1,column2row1
column1row2,column2row2
column1row3,column2row3

sample template mytemplate.txt

insert into mytable (column1,column2) values ('{0}','{1}');

###Command

CSVtoSQL.exe -t mytemplate.txt -i data.csv

###RESULT output.sql

insert into mytable (column1,column2) values ('column1row1','column2row1');
insert into mytable (column1,column2) values ('column1row2','column2row2');
insert into mytable (column1,column2) values ('column1row3','column2row3');

Requirements

.NET Framework 4

Known Issues and nice features

  1. Note about parsing CSV This is dirty solution so do not expect complex parsing of CSV file for example if your column separator is "," this sample below will not work:
header,header
column1row1,column2row1
column1row2,"column2, row2"
column1row3,column2row3

  1. EXCEL Procesor get VALUES not CELL TEXT This is feature it means that if you have function for example a1+a2 = 4 CSVtoSQL will take VALUE = 4 not formula

  2. Creative usage CSVtoSQL could be used to generate any content for example simple web pages

Template like ths will generate table body on output TEMPLATE

<tr><td>{0}</td><td>{1}</td></tr>

Copyright and License

Copyright 2016 Marcin Kotynia Licensed under the MIT License

Contact: marcin@kotynia.com