Page 1 of 1

Database Filter

Posted: Tue Sep 23, 2003 3:31 am
by jring
...bottom line, I can't seem to make it work! I'm running XP, and I've installed all Microsoft updates. I still can't get the DB filter to do anything, however, what I need to do is:

1. Extract a list from a TEXT File & Process with TPP and
a) Output a DB table

2. Extract a list from a Database & Process with TPP and
a) Output to a .txt File.
b) Output to a New DB table
c) Output to Different Column in the Original Database Table

I also Own DataPipe, so I can clean data once it's in the database, however, I'd really like to be able to output directly to a SQL database table. Suggestions are appreciated!!!

Posted: Fri Oct 03, 2003 5:38 am
by DataMystic Support
1a). Convert it to a INSERT INTO ... SQL form. See you database help for details on the format of this command.#

2a) Simple, with input 'select * from tablename;'
b) See other post on copying data
c) Tricky - could be done by taking 2b) and modifying it from 'INSERT INTO' to 'UPDATE <table> SET X=x WHERE ...'


Posted: Mon Oct 27, 2003 11:19 am
by jring
OK. I've finally figured out how to use the darn thing! For everyone else's benefit, I'm going to explain where i got stumped, and the solution by walking through a quick example:

I have a msAccess file with a table called [Test], and I want to extract everthing, convert to lowercase, and output to a text file.

first, setup the DB Filter as the First filter, and point it to the msAccess file or to the DSN file if you prefer to setup ODBC DSN file.

Second, open a blank text file. Write the SQL Query string into that text file that you'd normally write in MS Access Query Analyzer to pull the correct data set. In this case the SQL statement is:

Select [Test].* From Test;

(you can also write this in the Trial input if you prefer. Then at runtime, you just click 'Trial Run' instead of 'Go Real')

Third: Save the text file. Drag the text file containing the SQL string to the Textpipe tab: "2 Files to Process"

Fourth: Setup up 'Convert to Lowercase' filter as you normally would.

Fifth: On the Output Tab, select 'Single File Output' and name the ouput file as you like.

Click Process file. You're done. For some reason I couldn't figure out where to write the SQL Statement. Doh! :evil: :roll: :wink:

Posted: Mon Oct 27, 2003 4:00 pm
by DataMystic Support
Hi Joseph,

The help for the database filter also describes this process, although I'm not sure it it's in the current release (definitely in 6.6.3 beta). Here it is again anyway:

Add a Special Menu\Database filter
Configure it to point to your database using the Build button e.g. to the Northwind database that comes with MS Access
Set the database filter output options, e.g. for Delimited Output
In the Trial Run area, type

select * from customers

or use

select * from <your table name>

For an Excel spreadsheet, use

SELECT * FROM [Sheet1$]

Click the [Trial Run] button, to see data extracted and populated in the Trial Run Output area. You can also set the Input Filter to use the Trial Run area by default, and then just click the [Go] button. To save this data to a file, set the Single File Output option of the Output Filter.