Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

export query results using tab delimited output

Posted on 2013-12-09
21
Medium Priority
?
1,324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

597 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