smithvoice.com
 Y'herd thisun? 

“Zubrin, XCOR's Greason, NSS CEO Hopkins and Buzz Aldrin discuss the future of Human Spaceflight at a forum held at the Downey Columbia Memorial Space Center August 23rd 2010”

from this exclusive video by Smith

Controlling Text exports and more

TaggedCoding, Database

The most common questions we get about the Smithvoice.com tip about exporting/converting ISAMs (Export To Anything) concern how to control Text export formats.

"How do we get Tab Delimited files?"  "How can I change the comma delimiter to a pipe character?"  "How do we suppress field names from the first row?"  "How can we change the Text Field Delimiter Character?" and so on.

The answer lies in overriding the default settings for the MStextXX.dll driver.   These defaults are held in the registry and when you deploy your app using any of the dlls your setup program automatically makes these default settings on the user machine.

Basically here's the deal:

All you have to do is create a temporary Schema.INI for the file you are about to export (or add a setting to a Schema.ini that exists at the export location already). In this Schema.ini all you need to say is the name of the export file and the FORMAT setting or any other special details you might wish to use.

Follow this example.

1) create a new folder and in it create a new vb project. In this project add a DAO Or ADO reference. Add a form with a command button.

2) copy your Biblio.mdb to the project folder

3) create a subfolder (we will use this as the location of the exported textfile) call it "txtExpt"

4) in the txtExpt folder, create a new text document with Notepad and type these 2 lines:

[IGORTEST.TXT]
FORMAT=DELIMITED(|)

5) Save the text file, then rename it to "Schema.INI"

6) In the project's button click event type (or paste) this:

Dim db As Database
Dim sSQL As String
On Error Resume Next
Set db = workspaces(0).opendatabase(App.Path &
"\biblio.mdb", False, False)
 
sSQL = "SELECT * INTO [Text;DATABASE=" & App.Path &_
"\txtExpt].[IGORTEST.TXT] FROM
Authors"
db.execute sSQL, dbfailonerror
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr &
Err.Description
Err.Clear
End If
db.Close
Set db = Nothing

Or, for ADO:

On Error Resume Next
  
Dim cn As Connection
Dim sSQL As String
Set cn = New Connection
cn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[path to db]"
cn.Open
 
sSQL = "SELECT * INTO [Text;DATABASE=" & App.Path &_
"\txtExpt].[IGORTEST.TXT] FROM
Authors"
  
cn.Execute sSQL
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr &
Err.Description
Err.Clear
End If
  
If cn.state = adStateOpen then
cn.close
End If
Set cn = Nothing

7) Press the button. If no errors, stop the project and go look at the IGORTEST.TXT file. See how the delimiter is the | as you wanted?

That is the way to pre-specify the delimeter or tab-separated or FixedLength options for your text exports with no registry worries. So you have to create such a fast file or addition to an existing Schema prior to the export but that's a 2 line text I/O so it should be workable and the time it saves can be considerable.

They key is that the local Scheme.ini overrides the registry settings. Any setting you make in the ini is used for the export. So FORMAT=TabDelimited gives you a tabbed output, FORMAT=Delimited(+) puts the + between fields, etc. For FixedWidths you have to specify each column's width like:

[MyExport.DAT]
FORMAT=FixedLength
Col1=MyCustomers Text Width 15
Col2="My Address Field" Text Width 30

Plus, you can add any other settings like:

[MyExport.DAT]
ColNameHeader=True

will add the column(field) names to the top row of the text file.

If you need to change the default doublequote character that wraps the values in text columns, use the undocumented TextDelimiter option like:

[MyExport.DAT]
TextDelimiter=^

(note that Jet3.0/3.5x can substitute the TextDelimiter but you need Jet3.6 to clear the character completely (using TextDelimiter={none})

And using MaxScanRows tells the ISAM driver how many rows to look at to "automatically" determine the type of data in each field, if you set it to MaxScanRows=0 then the whole recordset contents will be used for the determination.

If you deal internationally, use the CharacterSet setting to switch between OEM (extended character set) and ANSI like: CharacterSet=ANSI

You also have control over formatics like decimal characters, currency settings and more. For details on these see the Jet Database Engine Programmer's Guide pages (starting on page 307 of the Jet Database Engine's Developer's Guide, page 361 of the second edition).

Personally, I'd create the Schema.ini in a temp location, export to that location and if no errors FileCopy the text file to the path that the user specified and kill the temp Schema.ini and "orginal" export file.  Why?  Easy cleanup if anything goes wrong during the export (like the user shuts off their machine, etc.)  Plus, It's just easier to make a new Schma.ini than checking to see if one already exists then adding the new settings then removing the settings after the export ... but that's just me.

Want to see a demo of this?  Click here .   Just 2k, a single form project, uses DAO but is easliy altered for ADO.  Uses the standard Biblio.mdb, copy the biblio file to the project folder and run. WARNING, this demo creates and deletes files (Schema.inis and previously exported resultsets in the app.path).  Use a new folder just for this project and step thru the code to see what is going on.



home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better smithvoice.com