south_paw
asked on
create SQL scripts from a CSV using Delphi
Hello there,
After a little advice. I am trying to create SQL scripts from a CSV file using Delphi. I have used the following method in order to read the CSV:
Procedure CSVimport(FileName: String);
Var
MyFile, MyColumns: TStringList;
Row, Col: integer;
begin
MyFile := TStringList.Create;
MyColumns := TStringList.Create;
begin
MyFile.LoadFromFile(FileNa me);
For RowCtr := 0 to MyFile.Count-1 Do
Begin
MyColumns.CommaText := MyFile[Row];
For Col := 0 to MyColumns.Count -1 Do
Begin
MyColumns[Col] := MyColumns[Col];
end;
MyFile[Row] := MyColumns.CommaText;
end;
end;
end.
Now I am just after some advice as to how the scripts are created. I can figure out the source, just need some general direction...
Ta.
After a little advice. I am trying to create SQL scripts from a CSV file using Delphi. I have used the following method in order to read the CSV:
Procedure CSVimport(FileName: String);
Var
MyFile, MyColumns: TStringList;
Row, Col: integer;
begin
MyFile := TStringList.Create;
MyColumns := TStringList.Create;
begin
MyFile.LoadFromFile(FileNa
For RowCtr := 0 to MyFile.Count-1 Do
Begin
MyColumns.CommaText := MyFile[Row];
For Col := 0 to MyColumns.Count -1 Do
Begin
MyColumns[Col] := MyColumns[Col];
end;
MyFile[Row] := MyColumns.CommaText;
end;
end;
end.
Now I am just after some advice as to how the scripts are created. I can figure out the source, just need some general direction...
Ta.
if you have a dataset or you can load the CSV in dataset, you can use this:
https://www.experts-exchange.com/questions/21133083/Export-DBF-using-SQL-script.html
https://www.experts-exchange.com/questions/21133083/Export-DBF-using-SQL-script.html
ASKER
Basically, I want to write an app that will create the table and insert the records.
i.e. if I have a csv such as:
Niin,desc,qty
012345678,bolt,15
012345679,washer,10
it would create a SQL script like (user can define table name):
CREATE TABLE TABLE1 (ID INT(2),Niin VARCHAR(50),desc VARCHAR(50),qty, PRIMARY KEY (ID));
INSERT INTO TABLE1 VALUES (1,'012345678','bolt','15' );
INSERT INTO TABLE1 VALUES (2,'012345679','washer','1 0');
i.e. if I have a csv such as:
Niin,desc,qty
012345678,bolt,15
012345679,washer,10
it would create a SQL script like (user can define table name):
CREATE TABLE TABLE1 (ID INT(2),Niin VARCHAR(50),desc VARCHAR(50),qty, PRIMARY KEY (ID));
INSERT INTO TABLE1 VALUES (1,'012345678','bolt','15'
INSERT INTO TABLE1 VALUES (2,'012345679','washer','1
A few questions:
- How do you know when to create a varchar or an int. (Maybe you need a second row giving types: first row fieldsnames second row types)
- What about strings that contain a "," (might be better to enclose strings with quotes and use special encoding when a " is needed inside a string)
Let me know what you want.
Regards Jacco
- How do you know when to create a varchar or an int. (Maybe you need a second row giving types: first row fieldsnames second row types)
- What about strings that contain a "," (might be better to enclose strings with quotes and use special encoding when a " is needed inside a string)
Let me know what you want.
Regards Jacco
ASKER
Jacco,
The primary key will be the only INT, the rest will always be VARCHAR. There also would not be an instance when a string would contain a comma, therefore this does not need to be factored in.
Ta.
The primary key will be the only INT, the rest will always be VARCHAR. There also would not be an instance when a string would contain a comma, therefore this does not need to be factored in.
Ta.
Hi there,
I thought that the qty field was integer.
Here is what I came up with:
Procedure CSVimport(FileName: String);
Var
MyFile, MyColumns: TStringList;
Row, Col: integer;
Line: string;
begin
MyFile := TStringList.Create;
MyColumns := TStringList.Create;
MyColumns.QuoteChar := ' '; // < this does the trick of not having "s
try
MyFile.LoadFromFile(FileNa me);
If MyFile.Count > 0 then
Begin
MyColumns.DelimitedText := MyFile[Row];
For Col := 0 to MyColumns.Count -1 Do
MyColumns[Col] := MyColumns[Col] + ' varchar(50)';
MyFile[Row] := 'CREATE TABLE TABLE1 (ID INT(2), ' + MyColumns.DelimitedText + ', PRIMARY KEY(ID));';
end;
For Row := 1 to MyFile.Count-1 Do
Begin
MyColumns.CommaText := MyFile[Row];
Line := 'INSERT INTO TABLE1 VALUES (' + IntToStr(Row);
For Col := 0 to MyColumns.Count -1 Do
MyColumns[Col] := MyColumns[Col]; // < this really does nothing but I left it in for later conversions
MyFile[Row] := 'INSERT INTO TABLE1 VALUES (' + IntToStr(Row) + ',' + MyColumns.DelimitedText + ');';
end;
// .. now do something with SQL
ShowMessage(MyFile.Text);
finally
MyFile.Free; // < prevent memory leaks
MyColumns.Free; // < prevent memory leaks
end;
end;
Regards Jacco
I thought that the qty field was integer.
Here is what I came up with:
Procedure CSVimport(FileName: String);
Var
MyFile, MyColumns: TStringList;
Row, Col: integer;
Line: string;
begin
MyFile := TStringList.Create;
MyColumns := TStringList.Create;
MyColumns.QuoteChar := ' '; // < this does the trick of not having "s
try
MyFile.LoadFromFile(FileNa
If MyFile.Count > 0 then
Begin
MyColumns.DelimitedText := MyFile[Row];
For Col := 0 to MyColumns.Count -1 Do
MyColumns[Col] := MyColumns[Col] + ' varchar(50)';
MyFile[Row] := 'CREATE TABLE TABLE1 (ID INT(2), ' + MyColumns.DelimitedText + ', PRIMARY KEY(ID));';
end;
For Row := 1 to MyFile.Count-1 Do
Begin
MyColumns.CommaText := MyFile[Row];
Line := 'INSERT INTO TABLE1 VALUES (' + IntToStr(Row);
For Col := 0 to MyColumns.Count -1 Do
MyColumns[Col] := MyColumns[Col]; // < this really does nothing but I left it in for later conversions
MyFile[Row] := 'INSERT INTO TABLE1 VALUES (' + IntToStr(Row) + ',' + MyColumns.DelimitedText + ');';
end;
// .. now do something with SQL
ShowMessage(MyFile.Text);
finally
MyFile.Free; // < prevent memory leaks
MyColumns.Free; // < prevent memory leaks
end;
end;
Regards Jacco
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jacco,
I appreciate your help.
I appreciate your help.
You can build a script with SQL insert statements which can then be run on the server.
You can also insert the record directly into the SQL database (without an intermidiate script).
And you can create a binary file for using with a bulk insert command (MSSQL)
What do you want to do? What database server do you want the script for?
If you want to create a script you need some knowledge of the type of the fields in the database.
Regards Jacco