Database Filter

Discuss DetachPipe.com huge attachment service and DetachPipe attachment management Add On for Microsoft Outlook. Get help with installation and running, notify us of bugs, request new features and enhancements.

Moderators: DataMystic Support, Moderators

jring
Posts: 18
Joined: Tue Sep 23, 2003 3:13 am

Database Filter

Postby jring » Tue Sep 23, 2003 3:31 am

...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!!!

User avatar
DataMystic Support
Site Admin
Posts: 2136
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Fri Oct 03, 2003 5:38 am

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 ...'
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

jring
Posts: 18
Joined: Tue Sep 23, 2003 3:13 am

Data-Base-Filter

Postby jring » Mon Oct 27, 2003 11:19 am

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:

User avatar
DataMystic Support
Site Admin
Posts: 2136
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Mon Oct 27, 2003 4:00 pm

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.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “DetachPipe.com and DetachPipe Add On Support”

Who is online

Users browsing this forum: No registered users and 1 guest