Solved

export query results using tab delimited output

Posted on 2013-12-09
21
1,210 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 3

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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39707303
@bfuchs

what version of Access are you using ?
0
 
LVL 3

Author Comment

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

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 119

Expert Comment

by:Rey Obrero
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 3

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 3

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 119

Expert Comment

by:Rey Obrero
ID: 39710186
@bfuchs,

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

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 119

Expert Comment

by:Rey Obrero
ID: 39710514
@ bfuchs

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

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 3

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 3

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 3

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now