Full program code
program csv2sql;
{$mode objfpc}{$H+}
uses
{$IFDEF UNIX}{$IFDEF UseCThreads}
cthreads,
{$ENDIF}{$ENDIF}
Classes, FileUtil, sysutils, Interfaces, Dialogs, RegExpr, strutils, sqldb, db, mysql55conn, windows
{ you can add units after this };
var
path, fileN, fno : String;
idx, idy, i : Integer;
connect : TMySQL55Connection;
trans : TSQLTransaction;
procedure CheckDuplicateStrings(StringL:TStringList);
var
i,j,k:integer;
begin
for i:=0 to StringL.Count-1 do
for j:=i+1 to StringL.Count-1 do begin
k := CompareText(StringL.Strings[i], StringL.Strings[j]);
if k = 0 then
// WriteLn('Duplicate string : "'+AnsiUpperCase(StringL.Strings[i])+ '" in line ' + IntToStr(i)+' and '+ IntToStr(j));
StringL.Strings[j] := StringL.Strings[j] + 'a';
end;
end;
procedure convertToSQL(filePath, fileName : String);
var
TextLines, Headers, Rows : TStringList;
i, j, k, l : Integer;
cre, ins, tblNm : String;
remChar: TRegExpr;
fileOut, fileError : TextFile;
begin
remChar := TRegExpr.Create;
remChar.Expression := '[/]';
// remChar.ModifierI := true;
TextLines := TStringList.Create;
Headers := TStringList.Create;
Rows := TStringList.Create;
TextLines.LoadFromFile(filePath + fileName);
tblNm := Copy2Symb(fileName, '.');
AssignFile(fileOut, '... some path ...');
Rewrite(fileOut); // creating the file
AssignFile(fileError, '... some path ...');
Rewrite(fileError); // creating the file
for i := 1 to TextLines.Count -1 do begin
TextLines[i] := ReplaceStr(TextLines[i], '''', '|');
TextLines[i] := ReplaceStr(TextLines[i], '"', '');
TextLines[i] := ReplaceStr(TextLines[i], '\', '');
end;
// Cleanup Header row
for i := 0 to 0 do begin
if remChar.Exec(TextLines[i]) then
begin
// Remove expr chars
TextLines[i] := ReplaceStr(TextLines[i], remChar.Match[i], '');
end;
// Replace Colons
TextLines[i] := ReplaceStr(TextLines[i], ':', '');
// Remove space chars
TextLines[i] := ReplaceStr(TextLines[i], ' ', '');
// Convert CAPS to lowercase
TextLines[i] := LowerCase(TextLines[i]);
// rem other extraneous bits
TextLines[i] := ReplaceStr(TextLines[i], '\', '');
TextLines[i] := ReplaceStr(TextLines[i], '''', '');
// Pick out header elements
ExtractStrings(['^'], [' '], PChar(TextLines[i]), Headers, true);
end;
// Rename duplicate strings
CheckDuplicateStrings(Headers);
// Append ^ to end of field rows
for i := 1 to TextLines.Count-1 do begin
TextLines[i] := TextLines[i] + '^';
end;
// Create Tbl
cre := 'CREATE TABLE ' + tblNm + ' (';
for i := 0 to Headers.Count -1 do begin
cre := cre + '`' + Headers[i] + '`' + ' VARCHAR(255), ';
end;
// Trim trailing comma
RemoveTrailingChars(cre, [',', ' ']);
WriteLn('Creating Table...');
cre := cre + ');';
Writeln(fileOut, cre);
connect.ExecuteDirect(cre);
// Ins Statements
for i := 1 to TextLines.Count-1 do begin
try
ExtractStrings(['^'], [' '], PChar(TextLines[i]), Rows, true);
ins := 'INSERT INTO ' + tblNm + ' (';
for j := 0 to Headers.Count-1 do begin
ins := ins + '`' + Headers[j] + '`' + ',';
end;
RemoveTrailingChars(ins, [',', ' ']);
ins := ins + ') VALUES (';
for k := 0 to Rows.Count-1 do begin
ins := ins + QuotedStr(Rows[k]) + ',';
end;
RemoveTrailingChars(ins, [',', ' ']);
ins := ins + ');';
// Re-insert apostrophes
ins := ReplaceStr(ins, '|', '''''');
// Do the insert
try
connect.ExecuteDirect(ins);
finally
Rows.Clear;
Writeln(fileOut, ins);
WriteLn('Inserting data... ' + IntToStr(i));
end;
except
on E : Exception do
WriteLn(fileError, E.ClassName + ' ' + tblNm + ' error raised, with message : ' + E.Message);
end;
end;
CloseFile(fileOut);
CloseFile(fileError);
end;
begin
// DB Transaction
trans := TSQLTransaction.Create(nil);
// Connect to DB
connect := TMySQL55Connection.Create(nil);
connect.UserName := 'summat';
connect.Password := 'summat';
connect.DatabaseName := 'summat';
connect.Port := 3306;
connect.Connected := True;
connect.Transaction := trans;
idy := 206; // Number of files to process
path := '... some path ...';
WriteLn('Enter number of file to begin conversion from:');
ReadLn(idx);
try
for i := idx to idy do begin
fno := Format('%.3D', [i]);
fileN := 'EXP0' + fno + '.TXT';
WriteLn('Converting: ' + fileN);
convertToSQL(path, fileN);
end;
finally
connect.Connected := False;
connect.Free;
trans.Free
end;
end.
Recent
