Exporting to a Text Document

[mlr0911]

I have a query that I am trying to export to a text document. My question is how can I get the data to show up in tab delimited?

 

[mlr0911]

I am actually trying to line up the data to show the following:

First Name Last Name Address


Everything would need to line up under the appropriate column not matter how many characters are used.

I hope that I am saying this right.

Thanks for your help.

 

[The_Doc_Man]

There is such a thing as defining an Export specification that includes your choice of delimiters and text encapsulation. Should be under File >> Export, and remember that you can export anything that gives you a recordset, including tables AND SELECT queries. Don't try to export an action query.

 

[mlr0911]

I am trying to line up the columns in my table for output to another external system. I will be exporting this file as a .txt file, but the information needs to be in a specific format so that the other external system can read it from the .txt file.

The format would be like this:
First Name (1-40)
Middle Name (41-81)
Last Name (82-122)

Address 1(123-173)
Address 2(174-124)

Currently, I am trying to write a query that will do this, but I am not able to put the proper amount of empty spaces between the Names or addresses. Here is an excerpt of what I have:

expr1: "TRS" & [IP] & " " & [Acct#] & " " & [FUll_Name_01] & " " & [Full_Name_02]


Thanks for your help.

 

[mlr0911]

I know that I can do this by exporting through Access delimiter encapulation, but how can this be accomplished through code?

 

[The_Doc_Man]

There are ways and then there are ways...

If you are exporting directly to the file type you wanted, that is one thing. If you are exporting to an intermediate that you will import later, that is entirely different.

To make things line up via a program, you need a form or macro that will run some VBA code. The VBA code will probably have to elaborate a recordset for you explicitly, though you can at least provide a query with the data in the right order.

In VBA, you will use the OPEN (and CLOSE) verbs, the PRINT verb, and (to get things into specific columns), the TAB() function. Look these up in Access Help to see how they are used.

The alternative is to use a query in which NONE of the fields are output directly, ALL of them are output via the FORMAT(,) function, which is a way to force a specific field width on output. But I'm not sure about the field width sticking around correctly on an export. Which is why I mentioned the VBA options.

 

[Dennisk]

if you want to columns to line up why not export as fixed width, its only a constant value . Search access help for export or transfertext

 

[Addmore]

Please may you please help me on this one. Have a query that I want to output to a .txt file and also define the field lengths and put leading zeros in one of the fields if the data does not fill it up. Would also like to format these fields by justifying them as well, eg left right justification.
Field Lengths might be Name=20, Address=30, Quantity=7

So it would something like this
Name Address Quantity
Alex 2 Del Rd 0000233

 

[gemma-the-husky]

but as default, i am sure docmd.transfertext with a parameter acexportdelimited DOES produce a csv

 

[HiTechCoach]

but as default, i am sure docmd.transfertext with a parameter acexportdelimited DOES produce a csv

The problem is that they do not want a Comma Separated Values (CSV) format, but want fixed with.

 

[gemma-the-husky]

misread it boyd

what about

docmd.transfertext with a parameter acexportfixed, then

 

[Addmore]

Thanks guys, finally managed to do it in a somewhat unorthodox way. this waht I did. It works but dont think it is the best way cos you have to repeat the process for each field or column that you add into the file.

Dim rst As Recordset
'DoCmd.OpenQuery "On Hand"
Set rst = CurrentDb.OpenRecordset("On Hand")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\Onhand.DAT", True)
Do Until rst.EOF = True

'Define Containers
SPCDrawOfficeNum = Space(32)
'Get Length for Recordset fields
LenDrawOff = Len(rst!DrawOfficeNum)

'Get Container lengths for calculation
LenSPCDraw = Len(SPCDrawOfficeNum)

'Get The Space Remaining after Variable is in container
ConDraw = LenSPCDraw - LenDrawOff

'Write To .txt file

TextFile.writeline (rst!DrawOfficeNum & (Right(SPCDrawOfficeNum, ConDraw))

rst.MoveNext
Loop
TextFile.Close
MsgBox "File Saved..."