{*****************************************************************************
* InsertSQLWriter                                                            *
* (c) 2006 Daniel Chambers                                                   *
*                                                                            *
* Version 1.0.1                                                              *
*                                                                            *
* Don't rip off my code. Its here for your education and interest only.      *
* If you want to use it please give me credit and don't claim it as your     *
* own.                                                                       *
*****************************************************************************}

program InsertSQLWriter;
uses strutils, sysutils, CRT;


type ArrayOfString = Array of String;


const
	STRINGTYPE = '1';
	NUMBERTYPE = '2';


function GetStrArrayFromUser(): ArrayOfString;
var
	strInput : String;
	strArray : ArrayOfString;
	nIndex   : Integer;
begin
	nIndex := 0;
	
	repeat
		//Increment the array size counter
		nIndex += 1;
	
		//Read their input
		ReadLn(strInput);
		
		if (strInput <> '') then
		begin
			//Resize the array
			SetLength(strArray, nIndex);
			
			//Add it to the array
			strArray[nIndex - 1] := strInput;
		end;
	until strInput = '';
	
	result := strArray;
end;


function SplitStringIntoWordArray(strString : String; WordDelims : TSysCharSet): ArrayOfString;
var
	strArray : ArrayOfString;
	nLength  : Integer;
	nIndex   : Integer;
	strTemp  : String;
begin	
	//Get the number of words in the string
	nLength := WordCount(strString, WordDelims);
	
	//Loop through the string adding each word to the array
	for nIndex := 1 to nLength do
	begin
		//Set the array size
		SetLength(strArray, nIndex);
		
		//Extract word
		strTemp := ExtractWord(nIndex, strString, WordDelims);
		
		//Add the word to the array
		strArray[nIndex - 1] := strTemp;
	end;
	
	result := strArray;
end;


function WriteSQLInsert(strFieldNamesArray, strTypesArray, strValuesArray : ArrayOfString; strTableName : String): String;
var
	nIndex : Integer;
begin
	result := 'INSERT INTO ' + strTableName + ' (';
	
	//Loop and add each field name to the string
	for nIndex := Low(strFieldNamesArray) to High(strFieldNamesArray) do
	begin
		result += strFieldNamesArray[nIndex] + ', ';
	end;
	
	//Remove the trailing ', '
	result := AnsiLeftStr(result, Length(result) - 2);
	
	result += ') VALUES (';
	
	//Loop and add each value to the string
	for nIndex := Low(strValuesArray) to High(strValuesArray) do
	begin
	
		if strTypesArray[nIndex] = STRINGTYPE then
		begin
			result += '"' + strValuesArray[nIndex] + '", ';
		end
		else if strTypesArray[nIndex] = NUMBERTYPE then
		begin
			result += strValuesArray[nIndex] + ', ';
		end;
			
	end;
	
	//Remove the trailing ', '
	result := AnsiLeftStr(result, Length(result) - 2);
	
	result += ');';
end;


function IsolatePath(strFilePath : String): String;
var
	strExtract : String;
	nIndex     : Integer;
begin

	for nIndex := Length(strFilePath) downto 1 do
	begin
		strExtract := strFilePath[nIndex];
		
		if strExtract = '\' then
		begin
			result := AnsiLeftStr(strFilePath, nIndex);
			exit;
		end;
	end;
	
	result := '';
end;


procedure Main();
var
	strFieldNameArray : ArrayOfString;
	strTypesArray     : ArrayOfString;
	strValuesArray    : ArrayOfString;
	strTableName      : String;
	InputFile         : Text;
	OutputFile        : Text;
	strOutputFilePath : String;
	strInput          : String;
	strTemp           : String;
	strInputFilePath  : String;
	nIndex            : Integer;
begin

	//Write the welcome
	WriteLn('**********************');
	WriteLn('* InsertSQLWriter    *');
	WriteLn('**********************');
	WriteLn('* By Daniel Chambers *');
	WriteLn('* Version 1.0.1      *');
	WriteLn('**********************');
	WriteLn();
	
	strInputFilePath := '';	

	//Check to make sure paramstr(1) is not empty
	if ParamStr(1) = '' then
	begin
		WriteLn('You have not specified a file to work on.');
		WriteLn('Remember, a path to a file must be provided on the command line. (Drag and drop a file onto the executable in Windows Explorer)');
		exit;
	end;
	
	//Set the working directory to the program's current directory
	chdir(IsolatePath(ParamStr(0)));
	
	//Construct the full input file path from all the params
	for nIndex := 1 to ParamCount() do
	begin
		strInputFilePath += ' ' + ParamStr(nIndex);
	end;
	//Remove the beginning space
	strInputFilePath := AnsiRightStr(strInputFilePath, Length(strInputFilePath) - 1);
	
	WriteLn('You are working on: ', strInputFilePath);
	
	//Get the table name
	WriteLn();
	WriteLn('Please enter the table name.');
	ReadLn(strTableName);
	
	//Get the field names
	WriteLn('');
	WriteLn('Please enter the names of the fields in order. Enter a blank line to continue.');
	strFieldNameArray := GetStrArrayFromUser();
	
	//Get the type names
	WriteLn('');
	WriteLn('Please enter the number of the type of the fields in order.');
	WriteLn('The types can be:');
	WriteLn(STRINGTYPE, '. String');
	WriteLn(NUMBERTYPE, '. Double (Number/Integer)');
	WriteLn('Enter a blank line to continue.');
	strTypesArray := GetStrArrayFromUser();
	
	//Get the table name
	WriteLn('');
	WriteLn('Please enter the filename to output to.');
	ReadLn(strOutputFilePath);
	
	//Open the input file for reading
	Assign(InputFile, strInputFilePath);
	Reset(InputFile);
	
	//Open the output file from writing
	Assign(OutputFile, strOutputFilePath);
	Rewrite(OutputFile);
	
	//Loop for each line until EOF
	while not EOF(InputFile) do
	begin
		ReadLn(InputFile, strInput);
		
		//Get the word array (words are separated by tabs)
		strValuesArray := SplitStringIntoWordArray(strInput, ['	']);
		
		//Write SQL Insert Stmt
		strTemp := WriteSQLInsert(strFieldNameArray, strTypesArray, strValuesArray, strTableName);
		
		//Write the Insert stmt to the file
		WriteLn(OutputFile, strTemp);
	end;
	
	//Close files
	Flush(OutputFile);
	Close(OutputFile);
	Close(InputFile);
	
	WriteLn('');
	WriteLn('Done!');

end;

begin
	Main();
	Write('Press any key to exit');
	ReadKey();
	WriteLn('');
end.