Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

ODBC to CSV, XLS, or HTML


  • Please log in to reply
7 replies to this topic
ScottEdge
  • Members
  • 57 posts
  • Last active: May 16 2007 02:54 PM
  • Joined: 13 Aug 2005
Anyone looking to try to connect to an odbc table to work with autohotkey can check out the following.

http://bitdaddys.com/odbc2xls.html

you can automatically setup the connection and then run your sql queries from a file through command line to write to a csv file. You can also find a free odbc to csv command line tool there as well with zero documentation on how to use it. Tried other solutions suggested here on the forum but this was the easiest and only one I could get working.

**update: the programer has resolved the issue with the password. There is a maximum sql length I'm not sure what it is (its pretty long). Also you can write just the value designation to a file before running the program you don't need the commented messages created in the fsql file created from the program.

BoBo
  • Guests
  • Last active:
  • Joined: --
Thx for sharing it. Much appreciated. :D

Slaya
  • Guests
  • Last active:
  • Joined: --
Also try ODBC View it's free or charge supports output to csv and can be called though the commandline as well.

http://www.slik.co.nz/downloads.htm

~Slaya

  • Guests
  • Last active:
  • Joined: --
just a heads up on odbc2xls there is a bug in the program if your password starts with "yn" it doesn't store it proberly for retrieval later. I've submitted this to the programer hopefully they will fix it.

ScottEdge
  • Members
  • 57 posts
  • Last active: May 16 2007 02:54 PM
  • Joined: 13 Aug 2005
two more ODBC utilities both cost money though.

ODBC 4 ALL is a 32 bit Windows utility designed to easily allow any application, including DOS apps and batch files, to run SQL queries using any ODBC data source, (Access, Informix, MySql, Oracle, Sql Server, ...), redirecting the output to the screen, an ascii file / table or a DBF table.
When run in interactive mode, you can interactively choose the ODBC Data Source and Table, then insert the SQL command to send to the database server. If the command is a SELECT, the SQL results will be automatically shown on a screen grid, while INSERT, UPDATE etc. usually do not provides any output.

To use it inside a 3rd party application, you can create an ascii configuration file, then run ODBC4ALL.EXE to obtain the results of the SQL query into the desired output format.

In this case user intervention is not required. Even the error messages could be redirected to an ascii file (for example if the SQL query contains a syntax error, or if the database server is unreachable because the server PC is down) allowing the running application to check for errors before managing the results.


http://www.guolo.com/odbc4all/


ODBC2XML is a database publisher that merges data from any ODBC data source into XML documents. Its unique architecture uses predefined XML templates that contain embedded SQL queries, and generates documents that contain the ODBC data merged into the original XML. It comes complete with a callable DLL and a command-line interface for batch processing.

ODBC2XML simplifies the generation of complex XML documents. It allows non-programmers to generate sophisticated XML files without the need for scripting languages, report writers, or other more complex processing tools. Using ODBC2XML, you can:


merge data from Oracle, SQL server, Microsoft Access, Excel spreadsheets, or any other data source with an ODBC driver, into XML files;
generate XML tables and lists containing database data (for applications such as parts catalogues);
merge XML microdocuments from a relational database into "master" XML document (for applications such as user manuals that share feature writeups across a range of products);
generate large volumes of XML based on simple, repetitive changes in one part of the document;
generate traditional relational database reports, but within an XML "wrapper" for sophisticated formatting via XSL; and
generate XML test data.

http://www.intsysr.com/odbc2xml.htm



PhiLho
  • Moderators
  • 6850 posts
  • Last active: Jan 02 2012 10:09 PM
  • Joined: 27 Dec 2005
Yet another command line ODBC tool...
http://www.dmst.aueb.gr/dds/sw/outwit/
(untested)
Posted Image vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

ScottEdge
  • Members
  • 57 posts
  • Last active: May 16 2007 02:54 PM
  • Joined: 13 Aug 2005
http://www.navicat.com/product.html

Here is another ODBC tool. It only connects to MySql tables and it costs $100 to $140 depending on your version. Many of the operations can be run from command line including running queries.

Main Featured Functions of Navicat MySQL (Window Version):

* Timeout Reconnection for SQL server
* Data and Structure Synchronization
* New Query Builder - create query from different databases
* Query Parameter
* SQL Console
* View Builder
* Views, Stored Procedure and Triggers creation
* Private Key for SSH Tunnel
* Supports all MySQL versions
* SSH Tunnel
* HTTP Tunnel
* Foreign Keys
* Foreign Key Data Selection
* Unicode and Character Set Support
* Edit text in Blob field
* Print Table Structure
* Import data from ODBC
* Import / Export data up to 18 most popular formats including MS Access, MS Excel, XML, PDF and TXT.
* Create Schedule for Backup, Import / Export, Data Transfer, Saved Queries and Data Synchronization.
* Create Reports with visual Report Builder
* Report Archive - Reports can be saved as an archive file (.raf) for backup and increased portability.
* Create Report Archive from command line



ScottEdge
  • Members
  • 57 posts
  • Last active: May 16 2007 02:54 PM
  • Joined: 13 Aug 2005
I ran across this utility outside of AHK but twhyman did talk about it in this post

http://www.autohotke...t=3404&start=15

anyway to run sql statements against an ODBC database you can use sqlrun.exe

ODBC SQL runner is the command line tool for run SQL statements using ODBC32 data sources.

Usage:
sqlrun [optional params] [filenames]

Params:
-I[]

* by default uses sqlrun.ini in program directory.
* empty arguments list means -I
* see config file format below

-D

-U

-P

-O

* uses terminal output by default

-L

* uses terminal output by default

Note: parameter in command line overwrites value from config file.





http://www.sqledit.c...dbc/runner.html

http://www.sqledit.com/download.html