Solved

create SQL scripts from a CSV using Delphi

Posted on 2004-09-15
8
1,323 Views
Last Modified: 2008-02-01
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(FileName);
    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.
0
Comment
Question by:south_paw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:Jacco
ID: 12072152
Hi,

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
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072196
if you have a dataset or you can load the CSV in dataset, you can use this:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21133083.html
0
 

Author Comment

by:south_paw
ID: 12075261
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','10');
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Jacco
ID: 12077607
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
0
 

Author Comment

by:south_paw
ID: 12079687
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.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12079888
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(FileName);
    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

0
 
LVL 10

Accepted Solution

by:
Jacco earned 500 total points
ID: 12079894
Didn't cleanup that one to well...

Here is the corrected version:

Procedure CSVimport(FileName: String);
Var
  MyFile, MyColumns: TStringList;
  Row, Col: integer;
begin
  MyFile := TStringList.Create;
  MyColumns := TStringList.Create;
  MyColumns.QuoteChar := ' '; // < this does the trick of not having "s
  try
    MyFile.LoadFromFile(FileName);
    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];

      For Col := 0 to MyColumns.Count -1 Do  // < this really does nothing but I left it in for later conversions
        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;
0
 

Author Comment

by:south_paw
ID: 12080133
Thanks Jacco,

I appreciate your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month3 days, 11 hours left to enroll

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question