Solved

export query results using tab delimited output

Posted on 2013-12-09
21
1,239 Views
Last Modified: 2013-12-17
Hi all,
I am looking for a way to export a query results in tab delimited format.
The code below works fine, however the output is in comma delimited instead of tab.
    DoCmd.TransferText acExportDelim, , "MyTable", "\\NetworkLocation\ExportedFile.txt", True
0
Comment
Question by:bfuchs
  • 10
  • 4
  • 4
  • +2
21 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39707203
You need to create a specification. Start an export manually (File > Export) until you get to the wizard screen with a button "Advanced" in the bottom left. Configure the export as you like (including the tab delimited bit) and save. After you've saved cancel the export.

The missing parameter in your code is for the specification name
DoCmd.TransferText acExportDelim, YOUR_SPECIFICATION, "MyTable", "\\NetworkLocation\ExportedFile.txt", True

Open in new window

0
 
LVL 12

Expert Comment

by:duttcom
ID: 39707218
You can use a saved export to set the delimeter to a tab. Use the export data wizard to export your data manually. At the last step, choose the option to save the export steps. Note the name you give the export, e.g. MyExport. If you look in the list of saved exports you will see that MyExport points to \\NetworkLocation\ExportedFile.txt

Now you can use the name of the saved export as the SpecificationName parameter in your code above like so-

DoCmd.TransferText acExportDelim,"MyExport" , "MyTable", "\\NetworkLocation\ExportedFile.txt", True

and the specifications you set in the export steps will be used for the export.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39707295
Hi, thanks for your responses, exactly this is what i have been trying that since yesterday and for some reason getting the error attached, can you tell me what am i doing wrong?
Doc1.doc
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39707303
@bfuchs

what version of Access are you using ?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39707380
hi, access 2k FE and its SQL server 2005 BE, trying it from an mdb.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39707405
@capricorn1

btw, this is something i have been trying to do a while ago in ADP and it didn't work.
see my other post (2013-04-10 at 12:48:31 ID 28093155), as of now nobody came up with a solution..
0
 
LVL 12

Accepted Solution

by:
duttcom earned 500 total points
ID: 39707414
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39707417
did you create an export specification?



to create the export specification
1.select the table
2.File>export
3. in the Save As Type list, click Text Files (*.txt, *.csv, *.tab, *.asc), and then click Export
4. In the export text wizard select Delimited width
5 .Click Advanced,
6. In the Export Specification dialog box Field Information List, correct any descrepancies
    a. Specify the field Delimiter
7.  Save the export specification  < the name that you give will be used in the command line below to export the table using codes

DoCmd.TransferText acExportDelim, "ExportSpecName", "Table?queryName", " C:\Program Files\MyData\myData.txt", True
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39707433
@capricorn1
of course i did create the specification, and only after that when running the code below did i received the attached message,
    DoCmd.TransferText acExportFixed, "MyExportSpec", "MyTable", "\\NetworkLocation\ExportedFile.txt", True

@duttcom
I guess if nobody comes up with something original i will have to try that procedure..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39708200
Do any of your field names contain spaces or special characters?

If so, try creating a query based on that table using aliases that avoid spaces, etc:


Select [ID Field] AS IDField, [First Name] AS FirstName, etc
FROM YourTable

and use that query in your export:

 DoCmd.TransferText acExportFixed, "MyExportSpec", "MyQuery", "\\NetworkLocation\ExportedFile.txt", True
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39709758
Hi all,
for the sake if testing, I tried to remove the id field from the view (as the error message stated its the ID), and now i get the same message saying the SS Option is invalid..see attached. (I am also attaching the wizard screenshot, maybe you can find something there that is causing it..

@mbizup
the only thing i have is underscore as part of the table name.
can it be due to a memo field (one of the calculated output fields did the access link wizard decided its an memo..)?
Doc1.doc
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39710142
I'd be surprised if an underscore would cause that problem.

Just for kicks, try a few tests:

- Try the Export without the field names in the first row (uncheck that option)

- Try a similar tab delimited export on a different table (maybe create a small table with very simple field names  - just letters/no special characters)

- Try compact/repairing your database
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39710186
@bfuchs,

upload a copy of a .mdb with the table
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39710274
@mbizup
without the names didn't help
on a diff table with only two columns and few rows it worked
its a sql server db, not an access, only linked to access


@capricorn1
its a little of a complicated task as i would need permission from the manager...

@All
however, can it be that it does only work for tables, not for views linked in access as tables?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39710354
Possibly  -

Can you try writing your view (a simplified version of it if you want as a test case) as an Access query (on the table itself, not on the linked view) and exporting the Access query?

 DoCmd.TransferText acExportFixed, "MyExportSpec", "MyQuery", "\\NetworkLocation\ExportedFile.txt", True
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39710514
@ bfuchs

try changing the query you are exporting to a make table query,
then export the created table.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39710524
hi guys,
I think i got it, its the fact that the view has as one the columns a sql scalar-valued function, (figured out that by creating a view without those columns and doing the export there it works), therefore if my options are to re-write the entire t-sql logic in vba or to use suggestion of duttcom (ID: 39707414), i will go that latest..

however, just wondering why an export using the wizard works for this scenario and only running it by code is giving this problem??
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39710793
A linked SQL View actually runs in SQL Server (which has those Functions at its disposal), not in Access.  The trasnfertext command is probably trying to run the same SQL statement in Access (which doesn't have those functions at it's disposal).

Have you tried duttcom's suggestion?  If that has the same hangups as the trasnfertext command, then you'd have to use some method of getting the data into Access in a usable format - such as capricorn1's Make Table idea at http:#a39710514.

You could also try creating a local query in access (SELECT * FROM YourView) and exporting that -- Not sure whether that query would try to process the view SQL in access or just select the resulting data.  If the latter, then it should work.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39712464
hi all,

Ok, just updating you with the latest...

Tried capricorn1's suggestion and didn't work either..

therefore i came to the conclusion, that is the Memo field that is causing the problem...

proved that by doing the make table approach, and manually changed the data type of field to text before running the transfertext command.

intersting, this limitation looks like affecting only when using transfertext with specification, because acExportDelim worked fine with memo.

however since i need that field full context, and some contain more then 255 characters, it boils down to the option offered by duttcom..

@mbizup
i did try using select * from view...had the same issue.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39712829
hello,

Here comes another update...

I found a way how to overcome that problem as follows

first create a table with the same structure, except for the memo field that should be defined as text(255), then create the specification, later change the table...

tested the above and worked fine.

except for...I don't see the tabs in the exported file,

If that is the case, this looks like a real BUG in access.

I'm attaching a sample of the file,
MissingDocs.doc
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39725451
Hi duttcom,
As it turns out, yours was the only solution that worked (odd, but a fact!).
Thank you!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question