how to Import/export Data from/to excel or calc ?

[lkhalid]

hi ,

i want to create a database application using lazarus .
the condition is the ability to export some tables from that database to excel or calc manually /or automatically(by code)
and to import some data from excel or calc to my database application (manually /or automatically(by code))

i dont know wish database to choose :
1-firebird
2-sqlite
3-mysql
4-...

any help will be very appreciated for a beginner 
Thanks.

[taazz]

The easy way out is to use .csv files to export and import the data you need. Excel can open or import those directly and I think calc supports the format too. the server you are going to use plays no part on the job described so choose any one that fulfills all your other technical specifications.

[Mike.Cornflake]

:-)   Are you a beginner to databases, or just a beginner to Lazarus?

As @taazz has said, CSV is a good place to start.  You can use the SDFLaz dataset to connect to a CSV file, then use a TDataSource and TDBGrid to display the contents of your CSV file.  Exporting to Excel comes cheap for you this way as well, Excel can open CSV files directly :-)

A CSV file though is a only a Table.  You want multiple tables, you're going to need a relational database, and here you need to work out what your requirements. 

• Something small with minimal install issues?  SQLLite. 
• Something scalable and needing to deployed on a client servers and supported by client IT?  MSSQL or Oracle. 
  
• Cross platform?  MySQL (MariaDB). 
• Programming for fun?  MySQL (MariaDB) (I'm not saying this engine is only for hobbyists, I'm saying I genuinely find programming with MySQL fun, working with MSSQL and Oracle can be like banging your head against a wall sometimes).

And these are only my opinions, others will give you different answers to the same questions.  You'll also spot I've never used Firebird or Interbase, so a bit clueless there :-)

Exporting from the database to Excel is done in code.  I've never used it, but FPSpreadsheet seems to be the way to go.

[exdatis]

The easy way out is to use .csv files to export and import the data you need

Agree(TCSVExporter),
http://wiki.freepascal.org/CSV
http://forum.lazarus.freepascal.org/index.php/topic,16960.msg92990.html#msg92990

[lkhalid]

thank you guys
csv will be my last resort as it is only a table !

i was coding in vb.net using access database so i was able to open my .mdb and paste all ready data in a table or vice versa .

Let's say that the program is a school application that contain at least 30 tables(students,teachers,school absence,notes,courses ...) .and some times we need to have one of them exported to a simple format (excel)
sometimes we also receive data in that simple format (excel) and we must import them to our database (a new students list for Ex)

i am beginner , coding for fun but i can learn all what's necessary to the job .

All what i need is to choose a database that can be edited easily
i'am under ubuntu but if we didnt find a cross platform solution i can work also under windows .


taaz,Mike.Cornflake,exdatis
thanks you for your answers

[Mike.Cornflake]

i am beginner , coding for fun but i can learn all what's necessary to the job .

Excellent :-)    Welcome to Lazarus/FreePascal!

If you want to stick with Access, then I'm uncertain what to recommend.  ODBC driver probably.

So, I recommend MySQL :-)  You're on Ubuntu, so installing the engine will be easy enough, should be in software centre I think it's called.  I also recommend a database viewer for you to test with.  You may need to look around, not sure what to recommend under Ubuntu, been too long.  Under Windows I use XAMPP, which includes Apache Web Server, and a MySQL administration plugin.

I'd then suggest you use the Database Admin tool to add a database and a few tables, get some test data in there.

Good write up in the wiki (see link below) on how to use Lazarus to connect to MySQL.  Read the bit immediately below the heading, it's important :-)  Lots of people miss the fact that they need to add the dlls (in your case .so files) to both the Lazarus exe dir and your project exe dir.

http://wiki.freepascal.org/mysql#Simple_MySQL_Demo_Using_the_TMySQL5xConnection_Component

Don't read the stuff above the heading I've linked to, it's scary :-)  Or feel free to read it, but be assured you don't need to do that low level programming if you don't want to :-) It's all hidden behind the TMySQL5xConnection components.

Finally, to export data out to Excel, FPSpreadsheet is the place to go.

http://wiki.freepascal.org/FPSpreadsheet

Conceptual code has already been written for you, you'll just need to modify it to suit :-)

http://wiki.freepascal.org/FPSpreadsheet#Converting_a_database_to_a_spreadsheet

Have fun :-)

[taazz]

Personaly I would go for firebird database and its embedded server, you need nothing more to use it as a single user database or to install the server on a machine and let every one connect to it from any OS that is supported by firebird. as for the excel support there are a number of components that can be used most of them are written for delphi but since none of them have any visual components they probably can be used from lazarus as is there is also the fpspreadsheet component pack which you can find at https://lazarus-ccr.svn.sourceforge.net/svnroot/lazarus-ccr/components/fpspreadsheet that is a lazarus specific implementation.

[BigChimp]

Another vote for Firebird (embedded), or perhaps sqlite. Flamerobin is a nice database viewer/management tool for Firebird.

I'd stay away from mysql, if only due to the need to match FPC connection component version with mysql .so/.dll client version.

Advantage of Firebird is that you can use it in single user mode (embedded) or multiple user (client/server) without any code changes.

Data/import export could be done with fpspreadsheet, yes.
For export, there's also http://wiki.lazarus.freepascal.org/fpXMLXSDExport (e.g. export to XML that can be imported with MS Access) but exporting to .xls format is probably easiest in practice.

Re mysql:

Lots of people miss the fact that they need to add the dlls (in your case .so files) to both the Lazarus exe dir and your project exe dir.

I thought copying dlls was a Windows-only phenomenon? Shouldn't .so's be in the library search path (typically different from the executable directory)?

[Mike.Cornflake]

I thought copying dlls was a Windows-only phenomenon? Shouldn't .so's be in the library search path (typically different from the executable directory)?

You know what they say, Assumption makes an ASS out of U and MPTION :-)  I'm very probably wrong, haven't connected to MySQL under Linux...

[BigChimp]

Oh, by the way, I'm working on something a bit similar: reading in data, converting it, and exporting it to Excel, CSV, MS Access, Firebird embedded etc.

Currently the program uses in-memory recordsets, but the export code surely could be usable. The relevant code is in outputparserunit.pas

See https://bitbucket.org/reiniero/db2securityscript/ (directory outputparers; the top directory contains a script for the DB2 database; the outputparser program can process the results from the script).

See also
http://wiki.lazarus.freepascal.org/Projects_using_Lazarus#DB2_Security_Script_importer

Thanks,
BigChimp