• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1342
  • Last Modified:

export query results using tab delimited output

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
bfuchs
Asked:
bfuchs
  • 10
  • 4
  • 4
  • +2
1 Solution
 
Angelp1ayCommented:
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
 
duttcomCommented:
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
 
bfuchsAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
@bfuchs

what version of Access are you using ?
0
 
bfuchsAuthor Commented:
hi, access 2k FE and its SQL server 2005 BE, trying it from an mdb.
0
 
bfuchsAuthor Commented:
@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
 
duttcomCommented:
0
 
Rey Obrero (Capricorn1)Commented:
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
 
bfuchsAuthor Commented:
@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
 
mbizupCommented:
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
 
bfuchsAuthor Commented:
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
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@bfuchs,

upload a copy of a .mdb with the table
0
 
bfuchsAuthor Commented:
@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
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@ bfuchs

try changing the query you are exporting to a make table query,
then export the created table.
0
 
bfuchsAuthor Commented:
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
 
mbizupCommented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
Hi duttcom,
As it turns out, yours was the only solution that worked (odd, but a fact!).
Thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now