Exporting Access Query to Tab Delimited Text Files

[Kamarul]

Hi,

i'm looking for script that can Export Microsofts Access queries to tab
delimited files.
i'm really new in scripting and dont have any idea on how to create a
script on this.
i've found this script (below) in the group, when i tested it, its run
OK, but the output is in csv format.
i need it to be in Tab Delimited text with field headers on 1st row and
without any quote.

Really appreciate if anyone would like to help me on this.
Thank you and Thanks in advance.


'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "D:\Documents and Settings\TEST\TEST.mdb"
Const QRY_NAME = "TESTQRY" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"


Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END

 

[freakazeud]

Hi,
no need to make it that complicated. Check out the transfertext method:
http://msdn.microsoft.com/library/d...ac11/html/acmthactTransferText_HV05186521.asp
It will do exactly what you want.
If you want to use a specific deliminater or exclude the quotes then
manually export the query ones through the file menu...then use the advanced
option to create an export specification...save that specification and
reference it correctly in the transfertext method.
HTH
Good luck

 

[John Nurick]

Hi Kamarul,

Oliver assumed you want to write code that runs within Access. In that
case, DoCmd.TransferText is almost certainly the way to do it.

But "scripting" has a much wider meaning. If you are thinking in terms
of VBScript code that runs independently of any Access application, you
will need something along the lines of the code you quoted. To make that
code write to a tab-delimited file instead of CSV, you need to put the
details of the format you need in a file called schema.ini in the same
folder you are exporting to.

For more about schema.ini, see:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090

http://office.microsoft.com/en-us/assistance/HP010321661033.aspx
or the Help topic (part of the Jet SQL Refernce) Initializing the Text
Data Source Driver

Hi,

i'm looking for script that can Export Microsofts Access queries to tab
delimited files.
i'm really new in scripting and dont have any idea on how to create a
script on this.
i've found this script (below) in the group, when i tested it, its run
OK, but the output is in csv format.
i need it to be in Tab Delimited text with field headers on 1st row and
without any quote.

Really appreciate if anyone would like to help me on this.
Thank you and Thanks in advance.


'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "D:\Documents and Settings\TEST\TEST.mdb"
Const QRY_NAME = "TESTQRY" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"


Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END

 

[Kamarul]

Hi John,

Thanks for your response. Does it mean that i still can use the
original script (Pasted above)?
i have created the schema.ini files, but not sure how to make the next
step.
i guess that i have to modify the DATA_DESTINATION value right?
how would it look like if base on my requirement? (tab delimited, has
field headers and no quotes)
and other thing is, i have to run 3 different queries, so do that mean
i need to put the details of all 3 in the schema.ini?

Thanks.

 

[John Nurick]

Hi Kamarul,

I've pasted the sample script below for easy reference.

i have created the schema.ini files, but not sure how to make the next
step.
i guess that i have to modify the DATA_DESTINATION value right?
how would it look like if base on my requirement? (tab delimited, has
field headers and no quotes)

The DATA_DESTINATION constant in that sample script specifies "text
file" and "field headers" - but all the rest, e.g. tab delimited, no
text qualifiers (quotes) and so on has to be specified in schema.ini.
All you have to change in DATA_DESTINATION is the destination folder
(which in the sample is "C:\Temp\"), and the filename ("MyFile.csv").

and other thing is, i have to run 3 different queries, so do that mean
i need to put the details of all 3 in the schema.ini?

In general, yes. Schema.ini must be in the destination folder (in the
sample, "C:\Temp"), and if you want to export three different queries to
three different files there must be a section in schema.ini for each of
the files. The section header needs to be the same as the filename (so
for the sample script it would be
[MyFile.csv]



'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "D:\Documents and Settings\TEST\TEST.mdb"
Const QRY_NAME = "TESTQRY" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"


Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END