tab2QL
======

===============================================
Current Status	:		Alpha Version. (1.0.21)
Build			:		1.0.3281.23040
Updated			:		25/12/2008
===============================================

History
=======

25/12/2008		1.0.21			Build. FIQ added.
23/12/2008		1.0.20			Build
01/08/2008						Project Started

So what's all this then?
========================

This is a Winforms utility for converting text data, separated by tabs, into sql statements.  It acts primarily as a quick way of importing small simple datatables into SQL Server without using the Import Data Wizard which in SQL Server 2000 & 2005 is fairly terrible and seems to fallover a lot.

The basic usage of this utility is as follows : 

1. User has Excel spreadsheet of x columns and y rows. User wishes to get this into a SQL table.
2. User loads tab2sql. 
3. User copies and pastes the data from Excel into the upper text box within tab2sql main form.
4. User presses "tab2sql" button. This should populate the lower text box with a set of SQL statements. 
5. User copies and pastes the resulting text into SQL Server Management Studio / WinSQL / Query Analyzer / SQL Anywhere iSQL and runs it.
6. User now has SQL table with data in it and can manipulate within the SQL environment. 
-- That's it.

FIQ (frequently imagined questions)
===================================

1.	Why the name tab2sql?

	Primarily this works with tab delimited data that one might find in an Excel file. The output is a series of sql statements, so it's tab to sql.  This name is inaccurate for a variety of reasons but there we go.

2.	What the hell? Why not just use the Import Wizard and import the excel file in?
	
	a) Not all SQL installations have the import wizard utility installed.
	b) The import wizard is overkill in a lot of cases. For smallish tables (e.g. 6-7 columns with 100 or so rows) the process involved is disproportionate when compared to the amount of information involved. 
	c) The import wizard has to access the data through the file system which isn't always convenient. Additionally, I found it annoying that on most installations of Office you can't import the file while it's still open due to Excel's file locking. 
	d) The import wizard isn't very good in my opinion. It fails far too often, even with quite simple/small datasets. 

3.	Why not just use some dynamic sql / a stored procedure / bulk insert?

	That assumes work being done on the server end which isn't intended here. But having said that, that approach can work and it's one I've used in a web applet in the past. It does tend to require some sort of access to the file system however whereas this project is primarily designed to work via the clipboard. 

4.	Christ, this is terrible!
	
	That's not a question. But yeah, I know.
	
	The low tech approach is 10% deliberate and 90% because I'm rubbish. This is my first effort in C# and only my second project in a C style language in the last 15 years. (The first attempt was a console utility which calculated how long warez downloads would take on my dialup connection). I'm conscious that there are numerous better ways of doing this but on the day to day examples tested thus far, it does seem to work most of the time. 
	
5.	Why is the output text? Why not just send the object to SQL Server?
	
	My best-case scenario for a mature version of this utility is that the sql generated won't work in 60% of instances. (By "won't work" I mean the statement simply won't parse or will fail during execution). Of the remainder I'm assuming that 25% will parse and run but won't do what you wanted with the data for some reason. 5% will be OK. 
	
	Starting with this assumption, it follows you need to be able to modify the code produced in the overwhelming majority of cases. It's easy to imagine a series of steps presented through a GUI where you could do this before submitting but in the end you'll end up with something like the Import Wizard or one of the third party utilities (like Synametrics excellent WinSQL (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp) which this isn't inteded to be.
	
	Secondly, working like this means you can more straight-forwardly use temporary tables. A colleague of mine was having to do a lot of data importing, with datatables from different people with different schemas involved. Because of import wizard's frequent failures, we were getting dozens of redundant tables being created which cluttered up the catalogs and were generally annoying. 
	
	Finally, there is no assumption made here that you'll have SQL access on the box you're running this on. I generally find that the box I generate my SQL statements on ends up being different from the one where I have SQL access.
	
6.	Does the utility generate ANSI compliant SQL? 

	No. At present, the output is SQL Server T-SQL only. For a variety of reasons will not run under anything but SQL Server 2000/2005/2008 (and even then, it will fail 70% of times as stated). 
	
	There is a infmx button which does some very crude regex replacements to remove some of the T-SQL specific stuff but you'll still need to heavily manually edit the outputs to get to work. 
	
	Some of the SQL produced will work with Sybase's SQL Anywhere (v9 & v11) but it's a bit hit and miss.
	
	On the TODO list is to adjust output to alternate sql implementations. The order I will be tackling these is as follows :
		Informix IDS v9.1
		Sybase SQL Anywhere v9
		MySQL v5
		SQLite
	
	This may seem like an odd list but it reflects the sql environments I work in.
	
7.	Beyond generally "not being very good" what are the known limitations of tab2sql?
	
	At present, there is a fixed limit of 300 columns in any table. There is no upper limit on rows that I am aware of, although memory usage can rocket if you're doing more than 50k rows or so. (The performance of the resulting SQL will also be terrible too). I have tested datatables upto    100,000 rows or so but I wouldn't advise more than 1k rows or so, even if you're only dealing with a slim tables with 3-4 columns. 
	
	If you've got to import large datasets then use bcp / bulk insert or your platforms equivalent. 
	
	Additionally, remember that in most cases you'll be transferring data via the Windows clipboard which has it's own limitation.
	
8.	What sort of imports shouldn't you use tab2sql for? 

	- Anything where column names are important (they'll be squished/mangled to make them import friendly)
	- Any table over 500 rows. 
	- Any table with over 10 columns.
	- Any tables containing numbers over 100 billion or more precise than 0.0001
	- Anything where character encoding matters even slightly. 
	  (for emphasis, this includes any table containing unicode data)
	- Any table containing any escape characters or special cahracters of any kind. This includes but is not limited to :
			CHAR(9)		Horizontal tab
			CHAR(10)	Line feed.
			CHAR(13)	Carriage return.
			CHAR(27)	Escape
			CHAR(47)	Slash or /
			CHAR(92)	Backslash or \
			CHAR(39)	Single quote or '
			CHAR(34)	Double quote or "
			CHAR(96)	`
			
	I am not stating categorically that it won't work if any of these characters are encountered but it probably won't work, at least not in line with expectations.		
		
9. Hang on, doesn't the list given above cover pretty much every single import scenario imaginable?

	No. Only 99.99% of them. For the 0.001% that remain, you may find tab2sql useful. Maybe.	

10. What other issues are you aware of?

	See the BUGS.txt file. 

Project Notes
=============

Keep in mind :
- This isn't very good.
- This isn't designed to be the most efficient way of doing things. 
- I am aware that the previous line is a bit of an understatement.
- I don't care. 

Copyright Notice
================

Where possible, I've retained/included any copyright notices that came with anything I've used. I can't guarantee this in all cases though as I'm lazy/sloppy/forgetful and will have deleted/removed some. 

Most of the code here was typed by myself. Since 99.9% of it is basically copy and pasted from a multitude of tutorials/guides/other projects it seems misleading to say I "created" any of it. 

Anyway, for the stuff I've done, I assert no false rights over materials in this project. Feel free to recompile the whole project with your name as author and then makes billions as the creator. (If you can manage this, you clearly deserve the money)

- darian.linehan@googlemail.com
25th December 2008 






