Export Query to PIPE delimited text file

[Paul Howarth]

I'm using Access 2007-2010.
I want to export Query to PIPE delimited text file.
I don't know how to write code.

External Data tab Exports to Excel or txt file just fine. But I'm not seeing a way, (such as an Advanced checkbox), to specify the delimiter.

Is there a way to export the Query results and specify the delimiter?

[ADezii]

You can use VBA Code to Export a Query to a PIPE Delimited Text File. The following Code should do that that along with Field Names in the First Row. It is generic in nature and should accept any Query Name as an Argument to OpenRecordset() . I am in work where I do not have Microsoft Access installed, so this Code is purely off the top of my head. It may/may not be 100% accurate so accept it for what it is, basically AIR CODE.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
 
Set MyDB = CurrentDB
Set rst = MyDB.OpenRecordset("<Query Name here>", dbOpenSnapshot)
 
Open "C:\Test\Pipe.txt" For Output As #1
 
rst.MoveFirst
 
With rst
  'Write Field names to Output File, delimiting by '|'
  For intFldCtr = 0 To .Fields.Count - 1
    strBuild = strBuild & .Fields(intFldCtr).Name & " | "
  Next
    Print #1, Left$(strBuild, Len(strBuild) - 3)    'Field Names, remove ending ' | '
      strBuild = ""     'Must RESET
 
  Do While Not .EOF     'Values in Fields delimited by '|'
    For intFldCtr = 0 To .Fields.Count - 1
      strBuild = strBuild & .Fields(intFldCtr).Value & " | "
    Next
      Print #1, Left$(strBuild, Len(strBuild) - 3)      'Each Record, remove ending ' | '
        strBuild = ""     'Must RESET for Next Record
          .MoveNext
  Loop
End With
 
rst.Close
Set rst = Nothing

What C:\Test\Pipe.tx t will look like:

First | Last | MI | ZIP Code
A | Putnick | O | 66543
B | Birdseed |  | 98765
C | Charlie | Y | 12340
D | David |  | 
E | Eddie | I | 19987

[PhilOfWalton]

Adezii's method looks good. Theres nothing worth watching on TV, so
here's a totally different approach, but I suggest you try it on a spare Accdb.
Create a query Query1

SELECT MSysIMEXSpecs.*
FROM MSysIMEXSpecs;
 

Run it. It will either give you an output if you have any saved Import/Export specifications, or give an error message that table MSysIMEXSpecs is not found.

In this case, select your query; On the Eternal Data Ribbon, select Export to Text File and run through everything. The critical thing is that after the export is done, a form comes up giving you the option of "Save Export Steps". Say yes.

This should create the MSysIMEXSpecs table

If you run Query1 again you should get a new record.

Here are the values you can try

DateDelim \
DateFourDigitYe ar -1
DateLeadingZero s 0
DateOrder
DecimalPoint .
FieldSeparator |
FileType 0
SpecID (This is an autonumber)
SpecName Test1
SpecType 1
StartRow
TextDelim ""
TimeDelim :

Save the record

Run this code

Sub ExportAsPipeDelimited()
 
    DoCmd.TransferText acExportDelim, "Test1", "YourQueryName", CurrentProject.Path & "\YourOutputFile.CSV"
 
End Sub
 

No guarantees

Phil

[informerFR]

Thanks a lot for your great tricky solution Phil. You made my day !