Delphi (Win32) - SqlBulkCopy?

Hi all,

I am looking for a Delphi/Win32 equivalent of the .Net SqlBulkCopy class... is there something available?

Background: I want to load CSV/Text files directly into a DB table. Files have various formats, e.g. field separator, text delimiter etc.

TIA,
Levend.
0
Levend
8/21/2011 9:27:46 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

17 Replies
2154 Views

Similar Articles

[PageSpeed] 23

> Background: I want to load CSV/Text files directly into a DB table. Files have various formats, e.g. field separator, text delimiter etc.

What database are you using ?
0
Robert
8/22/2011 12:28:47 PM
Hi Robert,

I am mainly using MS SQL Server. Thus a 1:1 equivalent of .Net SqlBulkCopy is what I am looking for - although I would not complain about a solution that addresses other RDBMSes too.

Levend.


> {quote:title=Robert Triest wrote:}{quote}
> > Background: I want to load CSV/Text files directly into a DB table. Files have various formats, e.g. field separator, text delimiter etc.
> 
> What database are you using ?
0
Levend
8/22/2011 1:38:58 PM
> I am mainly using MS SQL Server.

There must be a special BulkCopy procedure for SQLServer database I think.

I had the same problem when I needed to import a big file to a database.
The BDE supported a DbiCopyTable procedure but that will only work for
Paradox and DBase files. At that time I was using .mdb files (Access)

I came up with a method using ADO and a SQL statement. 
Sorry, but the following is from a long time ago but maybe it brings
you somewhere..

{code}
procedure TfrmExample.AddFile;
Var ADOText, TableName, MyDrive: String;
begin
  If chkDrive.Checked Then
    MyDrive:='c:\'
  else
    MyDrive:='h:\';

  TableName:='MyDelimitedFile.txt';

  ADOText:='INSERT INTO MYTABLE '+
           '(field1, field2, field3, field4, field5, field6, field7) '+
           'SELECT * FROM ['+TableName+'] in "'+MyDrive+'" "Text;HDR=Yes;"';

  ADOCommand1.CommandText:=ADOText;
  ADOCommand1.Execute;

  //ADOCommand1.Connection.Close;
end;
{code}

so..
{code}
INSERT INTO MYTABLE (field1, field2, field3, field4, field5, field6, field7) SELECT * FROM [MyDelimitedFile.txt] in "C:\" "Text;HDR=Yes;"
{code}

some extra info I found in the project:
{code}
ADO: Delimiter problem INSERT INTO: regkey: localmachine\microsoft\jet\4.0\engines\text : Format= Delimited(;)
ADO: Import floatfields from a textfile : Quote """ also these fields in the file and not only the textfields..
{code}

I also add under the (OLD!) procedure that is using the BDE but it is only working (not sure) for 
Native ODBC/BDE databases (Paradox, DBase)

{code}
Uses BDE;


procedure TfrmBatchMove.CopyTable(FromDir, SrcTblName, ToDir, DestTblName: String);
var
  DBHandle: HDBIDB;
  ResultCode: DBIResult;
  Src, Dest, Err: Array[0..255] of Char;
  SrcTbl, DestTbl: TTable;
begin
  SrcTbl := TTable.Create(Application);
  DestTbl := TTable.Create(Application);
  try
    SrcTbl.DatabaseName := FromDir;
    SrcTbl.TableName := SrcTblName;
    SrcTbl.Open;
    DBHandle := SrcTbl.DBHandle;
    SrcTbl.Close;
    ResultCode := DbiCopyTable(DBHandle,false,
      StrPCopy(Src,FromDir + '\' + SrcTblName),nil,
      StrPCopy(Dest,ToDir + '\' + DestTblName));
    if (ResultCode <> DBIERR_NONE) then
    begin
      DbiGetErrorString(ResultCode,Err);
      raise EDatabaseError.Create('While copying ' +
        FromDir + '\' + SrcTblName + ' to ' +
        ToDir + '\' +   DestTblName + ', the '
        + ' database engine   generated the error '''
        + StrPas(Err) + '''');
    end;
  finally
    SrcTbl.Free;
    DestTbl.Free;
  end;
end;
{code}

Greets,

Robert.

indeed: The BULK INSERT statement was introduced in SQL Server 7

Edited by: Robert Triest on Aug 22, 2011 4:46 PM
0
Robert
8/22/2011 2:46:43 PM
Levend Sener wrote:

> I am looking for a Delphi/Win32 equivalent of the .Net SqlBulkCopy
> class... is there something available?

	I think that just runs a T-SQL BULK INSERT statement. You can do that
directly.

-- 
Craig Stuntz · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz/
0
Craig
8/22/2011 2:49:07 PM
This works for me without a component. 

You can look at http://www.scalabium.com/smi/. I use his components for
exports and the components are very stable.

Joachim 


Levend Sener wrote:

> I am looking for a Delphi/Win32 equivalent of the .Net SqlBulkCopy
> class... is there something available?
> Background: I want to load CSV/Text files directly into a DB table.
> Files have various formats, e.g. field separator, text delimiter etc.
0
Joachim
8/22/2011 3:00:01 PM
Robert Triest wrote:
> I had the same problem when I needed to import a big file to a database.
> The BDE supported a DbiCopyTable procedure but that will only work for
> Paradox and DBase files. At that time I was using .mdb files (Access)

While I would recommend against it, you could use an ODBC connection to 
MSSQL with BDE.
0
quinn
8/22/2011 3:12:41 PM
Uuuuuh, no BDE... this is like riding a dead horse! ;-)

Levend.

> {quote:title=quinn wildman wrote:}{quote}
> Robert Triest wrote:
> > I had the same problem when I needed to import a big file to a database.
> > The BDE supported a DbiCopyTable procedure but that will only work for
> > Paradox and DBase files. At that time I was using .mdb files (Access)
> ...
> While I would recommend against it, you could use an ODBC connection to 
> MSSQL with BDE.
0
Levend
8/22/2011 6:54:06 PM
Hello Levend

You can give a try to AnyDAC http://www.da-soft.com/anydac/
There are:

- Array DML, allowing to fast execute multiple INSERT's commands:
http://www.da-soft.com/anydac/docu/Very_High_Performance_using_the_Array_DML.html
http://www.da-soft.com/anydac/docu/Array_DML.html

- TADDataMove component, allowing to move data dataset -> dataset,
text -> dataset, dataset -> text. Different test formats are supported.

-- 
With best regards,
Dmitry Arefiev / www.da-soft.com
AnyDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL,
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Dmitry
8/22/2011 7:13:58 PM
Hi Craig,

yes, that's correct... but has a major disadvantage in my case:

I have MSSQL 2008 64Bit but 32Bit MS Office. Thus MSSQL does not know the 32Bit Jet/Text drivers. Unfortunately it is not possible to install 32 and 64 bit Jet drivers in parallel and I do not want to replace 32 bit Office with 64 bit Office (which even MS does not recomment).

Using .Net SqlBulkCopy I can "decouple" MSSQL (whether 32 or 64 bit) and the JET drivers, i.e. mix them (by selecting i86 as .Net target).

Levend.



> {quote:title=Craig Stuntz wrote:}{quote}
> Levend Sener wrote:
> 
> > I am looking for a Delphi/Win32 equivalent of the .Net SqlBulkCopy
> > class... is there something available?
> 
> 	I think that just runs a T-SQL BULK INSERT statement. You can do that
> directly.
> 
> -- 
> Craig Stuntz · Vertex Systems Corp. · Columbus, OH
> Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz/
0
Levend
8/22/2011 7:44:14 PM
Hi Robert,

many thanks for your detailed reply.

I tried the 1st example (ADO, see below and also http://delphi.about.com/od/adptips2005/a/bltip0105_4.htm)

But strangely I always got an error: *Incorrect syntax near the keyword 'in'.*

First I thought that this could be a Delphi error but same happened when I tried it with Access/VBA.

Any idea?

BTW: your suggestion comes very close to what I am looking for :-)

Levend.


> ...
> {quote:title=Robert Triest wrote:}{quote}
> so..
> {code}
> INSERT INTO MYTABLE (field1, field2, field3, field4, field5, field6, field7) SELECT * FROM [MyDelimitedFile.txt] in "C:\" "Text;HDR=Yes;"
> {code}
> ...
0
Levend
8/22/2011 9:03:50 PM
Hi Robert,

just found out why the ADO code you posted did not work with ADO connecting to MSSQL: the syntax only works for MS Access databases... thus _no_ MSSQL :-(

Levend.

> {quote:title=Robert Triest wrote:}{quote}
> > I am mainly using MS SQL Server.
> ...
> I came up with a method using ADO and a SQL statement. 
> Sorry, but the following is from a long time ago but maybe it brings
> you somewhere..
> ...
0
Levend
8/22/2011 10:15:46 PM
You could try ADO and do the following

Use OpenSchema to get the tablelayout/fieldtypes etc

Use a new command with another ADO connection to your new server.
Create the table, and create statements with parameters yes it can be
lenghty, but this will always work
0
Kristof
8/23/2011 6:51:30 AM
> just found out why the ADO code you posted did not work with ADO connecting to MSSQL: the syntax only works for MS Access databases... thus _no_ MSSQL :-(

Since you can use ADO to execute SQL statements to the SQLServer and you have MSSQL 2008 you can use the "Bulk Insert" command.
Maybe it will be a bit hard to figure out how to use the command but it will be the best and fastest way.
(Don't forget to post your results ;-)

http://www.eggheadcafe.com/community/aspnet/13/1758/bulk-insert.aspx

greets.
0
Robert
8/23/2011 7:28:06 AM
Hi Kristof,

well... this is the "hard core" way of solving the problem.
But I was looking for the comfortable way as it is possible with the .Net SqlBulkCopy class.

Thanks,
Levend.

> {quote:title=Kristof Degros wrote:}{quote}
> You could try ADO and do the following
> 
> Use OpenSchema to get the tablelayout/fieldtypes etc
> 
> Use a new command with another ADO connection to your new server.
> Create the table, and create statements with parameters yes it can be
> lenghty, but this will always work
0
Levend
8/23/2011 7:51:51 AM
Hi Robert,

I previously tried the "bulk insert" command - unfortunately it has too many constraints for my purposes, e.g. can not set no text delimiter (double quote etc.).

I have CSV/Text files that have field separators "|", ",", ";" and quote characters "|", """ (double quote) etc.

While the "naked" bcp, bulk insert are lightning fast, they often lack of flexibility to handle different file formats.
So far only the .Net SqlBulkCopy (using schema.ini file) was flexible enough to handle all my file formats.

Thanks,
Levend.



> {quote:title=Robert Triest wrote:}{quote}
> > just found out why the ADO code you posted did not work with ADO connecting to MSSQL: the syntax only works for MS Access databases... thus _no_ MSSQL :-(
> 
> Since you can use ADO to execute SQL statements to the SQLServer and you have MSSQL 2008 you can use the "Bulk Insert" command.
> Maybe it will be a bit hard to figure out how to use the command but it will be the best and fastest way.
> (Don't forget to post your results ;-)
> 
> http://www.eggheadcafe.com/community/aspnet/13/1758/bulk-insert.aspx
> 
> greets.
0
Levend
8/23/2011 7:55:46 AM
Hi,

Are you sure? There are so many options you can set with this command, including a schema file.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

{code}
FIELDTERMINATOR ='field_terminator'
Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character). For more information, see Specifying Field and Row Terminators.
{code}
0
Robert
8/23/2011 8:06:49 AM
Levend Sener wrote:
> Uuuuuh, no BDE... this is like riding a dead horse!

I agree.
0
quinn
8/23/2011 5:25:17 PM
Reply:

Similar Artilces:

Migrate from Delphi 2007 for Win32 to Delphi XE
we use Delphi 2007 for Win32 to support legacy (32Bit) OWL-based pascal applications (yes i know it was a mistake not to switch to VCL 15 years ago). could our applications still be opened and compiled with Delphi XE? The existing projects are all plain Pascal-Code, coming back from the times of Turbo Pascal for Windows and later on Borland Pascal. Are there any improvements we could profit from (i.e IDE, Debugger)? Thanks Andrej > {quote:title=Andrej Dimic wrote:}{quote} > could our applications still be opened and compiled with Delphi XE? I'm not sure, but I guess ...

delphi Win32 using delphi .NET dll
Hi, I'm trying to use a delphi.NET dll in delphi.WIN32. I am currently using CodeGear Delphi 2007 with version2(base version) of .NET I can get the dll to import into the WIN32 application the only problem is when i include things such as: "using Classes,DateUtils, SysUtils" in the .NET dll the win32 application will instantly hang when any of the dll functions are called. Any help would be great thanks. Also I have tried this example and it also crashes for me? http://cc.embarcadero.com/Item/22688 -Braden I also found this.. "The problem is that, wehn you instal...

Delphi.NET loading Delphi.Win32 Driver
Hi, What I'm trying to do is marshal an array of cardinal (or integer) back into managed memory from a win32 dll. I know how to pass managed memory into a win32 dll {code} var aa : array of Integer; Buffer : IntPtr; begin SetLength(aa,2); aa[0] := 1; aa[1] := 80; if not Supports(ExtractFilePath(Application.ExeName)+'Win32_Library\SDK_Driver.Win32.io', TypeOf(IMyFunctions), MyFunctions) then Exit; //loads the driver into memory. MyFunctions contains the method names found in the SDK_Driver. Buffer := Marshal.AllocHGlobal(2 * {Marshal.SystemDefaultC...

Converting Delphi for Win32 to Delphi .Net(Prism)
Hi, I am currently migrating a project from Delphi for Win32 to Delphi.net. Part of my code currently goes into a directory and pulls out a random file from this directory and loads the contents of the file for me. This code doesn't seem to work in Delphi.Net. It uses PString and a number of functions in SysUtils that don't seem to be present in Delphi.net's SysUtils file. If anyone can help me please, it would be greatly appreciated! Many thanks, Jonathan Mackey Jonathan Mackey a écrit : > I am currently migrating a project from Delphi for Win32 to &...

Delphi and Delphi for .Net
It seems that Delphi for .Net is slower than Delphi Win32 native applicaiton. I would like to know is it true all .Net application is slower than Win32 native applicaiton or it is Delphi for .Net only. Your information is great appreciated, Inung On 2011-06-21 18:20:17 +0100, Inung Huang said: > It seems that Delphi for .Net is slower than Delphi Win32 native applicaiton. > I would like to know is it true all .Net application is slower than > Win32 native applicaiton or it is Delphi for .Net only. If you are only running the code in the application once then, yes, yo...

Win32 program: Delphi 7 vs Delphi XE5
How is a D7 Win32 program compared to a Delphi XE5 one in terms of stability and performance? Is Delphi XE5 good enough for a big ERP project with several DLL's and hundreds of units and forms? Thanks in advance Am 26.12.2013 15:02, schrieb lior ilan: > How is a D7 Win32 program compared to a Delphi XE5 one in terms of stability > and performance? > Is Delphi XE5 good enough for a big ERP project with several DLL's and > hundreds of units and forms? > Thanks in advance > Hello, XE5 has increased functionality. Stability seems to be ok for most ...

Win32 Delphi language features introduced since Delphi 7
Hi, Am I right in thinking that the language features introduced since Delphi 7 fall into the categories: a) language features dictated by .Net compatibility. e.g. Namespaces, Inlining, records with methods, operator overloading, pure interfaces, generics, extended RTTI and reflection; b) Unicode strings and supporting procedures? c) 64-bit support What other language features, if any, have been introduced since D7? Had most of the post-D7 languages features, except for generics, Unicode strings, and 64-bit support, been introduced in or before Delphi 2005? How bug-free were ...

SEPA components for Delphi with Source Code (Delphi 5
Hi all, in the european union change next year the Bankingformat to the SEPA Format. All peoples and companies must change the bankingssoftware and the costumer data form acountnummers in the new IBAN and BIC numbers. See: http://www.arma-it.de/shop/artikelueber.php?wgruppeid=211&wgruppe_offen=211 Functions: - generate SEPA XML'S - Calc IBAN - BIC Database (DE,AT and CH) Questions: vertrieb@arma-it.de PS: Bankinssoftware for Develpoers (Germany only) http://www.arma-it.de/shop/artikelueber.php?wgruppeid=212&wgruppe_offen=212 El 26/10/13 21:38, A...

Delphi 7 to Delphi XE
Have been using Delphi 7 for many moons ( have got later versions but never upgraded to ) My first problem is: Component Palette. in XE it is a small toolbar docked in top right in Delphi 7 it gives a large view of all the components. I am struggling to be able to cope/access my components.in Delphi XE. Can I make the component pallette tool bar the same size as Delphi 7, or is there a fast way to view/choose all available components in XE, that I have not spotted yet? Kind Regards, Robert. Hi, What I know is that in Delphi 2010 and XE you can choose between t...

Delphi 2007 to Delphi 7
I've written a class in Delphi 2007 that is not supported in Delphi 7. What would be the best way to achive what I've done in Delphi 2007 in Delphi 7? Thanks, Tom type BondConstants = class { Bond Types } type BondType = record const TREASURY = 3; AGENCY = 0; CORP = 1; MUNI = 2; SBA = 5; MBS = 4; CMO = 6; end; { Day Count Methods } type DayCount = record const ACTUAL_360 = 2; ACTUAL_365 = 1; ACTUAL_ACTUAL = 1; d30_360 = 0; ...

Delphi for PHP or Delphi PRISM
Hi, I have the opportunity to develop a web-based library management system. Nothing fancy, just being able to do the usual CRUD stuff for books and provide a search facility. Borrowing is to be done via an email request to the library admin who then sends out the book(s). Since both Delphi for PHP and Delphi PRISM will enable me to develop the app, which one will allow me to deliver it in less time and also increase (even how small) my marketability as a web developer? Thanks. Phillip Flores Phillip Flores wrote: > Hi, > > I have the opportunity to develop a...

Delphi 4 to Delphi 2007
Hello, I will have to port a D4 application (with source) to D2007. what kind of problem could I face ? I will have to go to customer site tommorow to analyse its source code to quote the work, what should I care of to hestimate the porting time ? Thanks John Terry wrote: > Hello, > I will have to port a D4 application (with source) to D2007. > what kind of problem could I face ? > I will have to go to customer site tommorow to analyse its source code > to quote the work, what should I care of to hestimate the porting time ? You can probably do it by just changi...

Delphi XE / Delphi 2010
Hello! I noticed that Embarcadero® Delphi® 2010 Version is not on the list of products on Embarcadero page. Or is it still possible to buy it? Will RAD Studio XE compile programs written in Delphi 2010 without problems.? Thanks. Am 13.09.2010 09:04, schrieb Petra Nemec: > Will RAD Studio XE compile programs written in Delphi 2010 without problems.? As always you will probably have to recreate the projects as the import is still a bit -- special. Christian Hello! Does anybody know if it is still possible to get a Delphi2010 trial version (if yes where)? ...

Delphi 5 to Delphi 6 and up
Dear List, Trying to add 7Zip compression support to my delphi application. I am using the ported 7Zip sdk (see their website, they have a link). I am stumped on how to rewrite a single function: function ReverseDecode(var Models: array of SmallInt; ....): ..... where the input is mostly a fixed size array of SmallInt. This code perfectly compiles and functions in Delphi 6 and up, but in Delphi 5 I get the error: There is no overloaded version of 'ReverseDecode' that can be called with these arguments And obviously, the input (fixed) isn't the same as the param de...

Delphi and virus, or virus and Delphi.
Hi all. There is some discussion about a 'new' virus, that targets Delphi (and developers). The article is in danish: <http://www.version2.dk/artikel/11833-delphi-udviklere-jages-af-ny-type-malware> but refers to this article: <http://news.cnet.com/8301-27080_3-10312628-245.html> From the Danish article POV, it seems like Delphi itself is vunerable, which is not true. As far as i can see, is the attack vector, injection of (source) code in the 'Sysconst' unit. What's going on? -- Best regards Stig Johansen Perhaps checking other thre...

Web resources about - Delphi (Win32) - SqlBulkCopy? - embarcadero.delphi.database

SqlBulkCopy for Generic List<T>
... and it was taking a very long time (I gave up after 5 minutes). I recalled this post a while back from Mikael Eliasson demonstrating SqlBulkCopy ...

Languages -> C# Articles, Tutorials, Examples: ASP Alliance
.NET,ASP,ASP.NET,SQL,XML,HTML,ADO,ADO.NET,JavaScript,JScript,CSharp,VBScript and VB tutorials from AspAlliance.

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Archive
Archive - Infinite Codex

Archive
Articles Blog Videos Archive The Good, The Bad, and Everything In Between May 2014 Building Multiple Filters with Lo-Dash and AngularJS Compile, ...

Home - Technical Taco
Technical Taco I'm just some guy, you know? View otac0n on GitHub 21 Nov 2012 » Range header, I choose you (for pagination)! 03 Sep 2011 » Getting ...


Learn SQL Server programming - developer Fusion
Get up to speed with the latest on SQL Server and learn SQL Server programming with our in-depth articles, tips and tricks and sample code. Get ...

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 12/28/2015 9:54:50 AM