Dump to file then update database from this file

Hi.

I've made a delphi app that reads from a database (Access using AnyDac).

I need to export my database data to a file. (txt, or something else)
Then give this file to a friend so he can import my data to his database.

How can i do this ?

Thanks in advance.
LVL 16
CodedKAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
AlexConnect With a Mentor Software EngineerCommented:
Hi Codedk

The truth is that i spend a lot of time about this strange problem and i found this solution.

try this:

procedure TForm1.N16Click(Sender: TObject);
var
  queryStr:String;
begin
 QueryStr:='select * INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=C:/A1/;].data.txt from mydata';
  AdQuery1.Close;

  AdQuery1.SQL.Clear;
  AdQuery1.SQL.Add(queryStr);
  try
    AdQuery1.ExecSQL;
  finally
    AdQuery1.Close;
  end;
end;

i change the string of the select as you can see.Try it and let me know if this helps you.
0
 
ThievingSixCommented:
MSSQL or MYSQL?
0
 
CodedKAuthor Commented:
MySQL
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
FactorBCommented:
I asume you think on exporting tables.
Simplest solution will be to manually open the database, right click on a table, choose export from pop up menu and in save as type choose Text (tab delimited) or XML, Excel, HTML, Paradox or few others.
Also this can be done from code (tested with ADO),
using this SQL syntax:

To export data into text file
Select * Into FileName#txt In "C:\FolderName\" ["text;"] From TableName

To import data from text file
Select * Into TableName From FileName#txt In "C:\FolderName\" ["text;"]
0
 
FactorBCommented:
When you create text file, be sure that it does not exists.
In other case delete it
if FileExists ('C:\FolderName\FileName.txt') then DeleteFile ('C:\FolderName\FileName.txt')
The text file is created by the driver and also schema.ini file (it has information about the table structure) is created during the process. Keeping txt and ini files in the same folder gives extra information to the driver that does the import.
0
 
CodedKAuthor Commented:
Hi FactorB.
Thanks for your info :)

I have a problem :
I do this :
Select * Into any_file_name#txt In "C:\A_Folder_that_exists\" ["text;"] From mydata

Error message :
Query input must contain at least one table or query.
0
 
FactorBCommented:
Try using it with TADOQuery and TADOConnection, set the provider to Microsoft.Jet.OLEDB.4.0 and it should work.
0
 
FactorBCommented:
Or open your Microsoft Access Database copy/paste the above query and it will work.
Note that any_file_name should not contain spaces and you actually need to change dot "." with sharp "#"
that means if you want to save the table into external text file
Notes.txt
your sql synonym should be
Notes#txt

0
 
CodedKAuthor Commented:
FactorB everything you say is right but i dont want to create this file for me.
Inside Access works fine but i want to do it with code. This is what i do :
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
procedure TForm1.N16Click(Sender: TObject);
var
  queryStr:String;
begin
 queryStr:='Select * Into 123456#txt In "C:\A1\" ["text;"] From mydata';
 AdQuery1.SQL.Clear;
 AdQuery1.SQL.Add(queryStr);
 try
   //AdQuery1.Open;
   AdQuery1.ExecSQL;
 finally
   AdQuery1.Close;
 end;
end;
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
With Open throws an Access violation.
With ExecSQL works but doesnt create anything !

If i execute this command inside AnyDAC query editor i get this :

error.jpg
0
 
FactorBConnect With a Mentor Commented:
Seems that you are still using AnyDac component for connecting. My idea was to place different components TADOQuery and TADOConnection from  the Tool Palette's dbGo tab and a button with name button1 for example and then copy this code. I've just tested it. It connects to "MyDatabase" that is in the same folder with the application, selects data from "MyTable" and inserts it into file "Notes.txt" in the same folder also. Try it.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StrUtils, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    ADOQuery1: TADOQuery;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
   
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
sQuery:String;
sDatabaseName: String;
sApplicationPath: String;
sFileName: String;
sPartName: String;
sTableName: String;
begin

sDatabaseName:='MyDatabase.mdb';
sTableName:='MyTable';
sFileName:='Notes.txt';

sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sPartName:=LeftStr(sFileName,length(sFileName)-4);
sQuery:='Select * Into '+sPartName+'#txt In "'+sApplicationPath+'" ["text;"] From ['+sTableName+']';

sDatabasePath:=sApplicationPath+sDatabaseName;

try
ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False';
ADOConnection1.Connected:=True;
except
end;

ADOQuery1.Connection:=Adoconnection1;
if fileexists(sApplicationPath+sFileName) then DeleteFile(sApplicationPath+sFileName)  ;

try
AdoQuery1.Close;
ADoQuery1.SQL.Clear;
ADOQuery1.SQL.Add(sQuery);
AdoQuery1.Open;
except
end;
end;
end.
0
 
CodedKAuthor Commented:
FactorB this is the problem i cannot change a big portion of the code.
I have to do it with AnyDAC.
Inside the Query Editor (in the picture above) there is a button (not visible in the image) that can dump
the data in a file (anyfile). So this means that it can do the job (anydac) but maybe i dont do something right.

I know that you consider this question answered but please if you can, download AnyDac and do some tests please.
0
 
CodedKAuthor Commented:
Thank you both :)

Alex your solution works ... But in some computers doesn't work !
When i tried it in another computer nothing was created and no error message appear.
When i tried to compile it in that PC it said something about delimiter... "delimiter error".
0
 
FactorBCommented:
I this that delimiter used to create text files and decimal symbol is the same on your system and is comma ",".
That can lead to conflicts during exports to text "," delimited files. Either choose tab delimited from the driver that you are exporting or even simpler go to Region and Language Options, Standards and Formats tab, chose Customize and change your decimal symbol to period.

Regards,
B
0
 
CodedKAuthor Commented:
Goodmorning FactorB

This was the first thing i did.
I changed it from the Control Panel and then changed it with code too inside form.create.
Didnt work.       So changing delimiter from the Control Panel didnt work.

How can i change to tab delimited from within the driver ?
I've searched google and nothing came up.

Also in the system that has a problem the comma "," is the seperator instead of dot "." but in the text i create in my home PC the text output has ";" as a delimiter...
0
 
FactorBCommented:
Do you include schema.ini on the computer that imports the text.
0
 
CodedKAuthor Commented:
Nothing gets imported.
I am in the export phase ...

I compiled my project there with the database that has some bogus info... and tried to see why it doesnt export anything. Nothing was imported.
I open my Delphi app, it reads everything fine from the dbf file...
   and the try to export the db to a text file.
0
 
CodedKAuthor Commented:
Thank you both.
FactorB thank you very much for your time and effort.
I accepted Alex solution because it works with the current components.
0
All Courses

From novice to tech pro — start learning today.