Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    With the help of HansV and WendellB in another post, it appears that our systems folks have been giving us, uh, "less than complete" information about the ability of Access to export to a tab-delimited .txt file. Systems has provided us with an Access2000 "front end" which we maintain, then export to an Excel2000 spreadsheet, then Save As to a tab-delimited .txt file for further manipulation by systems into other applications.

    Our team would be quite interested in understanding how to eliminate that spreadsheet step and go straight from Access into the .txt file. In Help, I chose "tab_delimited". It gave a thorough explanation of the DDE function which made my head swim and never mentioned "tab delimited" or text files. If that's how it's done, I guess I will need the kiddie version.

    However, HansV says to just choose File/Export. In the Save as Type dropdown there is a choice for "Text Files (*.txt;*.csv;*.tab;*.asc)". Does ".tab" mean tab-delimited? How does one isolate it from the other choices (like Excel isolates each choice)? I tried it from the Form View and got a lot of spaces, no tabs. What am I missing?

    Could be that systems has just provided us with an easy-to-understand method because they think we will never grasp how to do it from Access. Job security. Hellloooooo.
    Kris

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    Select File | Export...
    Select "Text Files (*.txt;*.csv;*.tab;*.asc)" in the Save as Type dropdown list.
    You can give your target file any of the four extensions; .csv is traditional for comma-separated and .tab for tab-delimited, but it doesn't really matter as long as you use one of the four. If you use another extension, you'll get an error message stating that the database is read-only.
    When you click Export in the dialog, the Export Text Wizard starts. Here, you can specify how to export the table or query.
    <UL><LI>In the first step, you can select from Delimited or Fixed Width; choose Delimited.
    <LI>In the second step, you can select the delimiter: tab, comma, semicolon, space or custom (specifed by you). Select Tab. You can also specify whether field names should be exported, and which character (if any) to use to surround text fields (single quotes, double quotes or none.)
    <LI>In the third and last step, you can specify the file name again.
    <LI>Before you click Finish, you can click Advanced... to see the settings you selected; you can store these as an export specification for later re-use.[/list]That's really all there is to it...

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    OK, here I sit in the "Export Form As" box with formname.tab in the File name box and the Text Files type chosen. Then you say "When you click Export in the dialog," and I can't find that. I must be in the wrong place? Or perhaps I am supposed to be seeing the Wizard after selecting File/Export and the wizards are not turned on?
    Kris

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    I'm sorry, I thought that you were trying to export a table or query to a text file. In that case, the Export Text wizard appears (or should appear).

    When you export a form, you don't get any choice about the format, only about the text encoding to be used (the latter in Access 2002, don't know about Access 2000).

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    I snuck into the systems structure, where I'm not supposed to be, and had a look around. They have created a "Form Table" which is linked to ODBC and put it under an icon which we are to click when performing the export. The result looks like a datasheet, although we are in Form View. So they are, apparently, exporting the form, not the table. And the whole thing (form and datasheet view) is password protected. Maybe it has something to do with ODBC and Oracle8. Whatever.

    I went into my own database's table and chose to export to a text file. The wizard came on and I was able to follow your instructions. Then I went into THEIR table, signed in, chose export to a text file, and on came the wizard. Ah-ha! I don't know enough about what they have built to understand why they must have a form export with the Excel middleman. But at least I now know how to do a tab-delimited export directly from Access and I'm waiting for the next time they say it can't be done!

    Bottom line is I'm not going to lose the spreadsheet any time soon so I can go ahead and link to it to capture the data discussed in that other post. Thanks again for all your help. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Kris

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    Another method for exporting a tab delimited file is to choose Microsoft Word Merge (*.txt) in the Save As Type, which gives you a tab-delimited file with a txt extension.

    As for their export, they *can't* be exporting the form. Access forms do NOT export as anything that can be viewed by another program. No matter what they claim to be doing, it boils down to a straight export of data, either to a text file or into an Excel worksheet.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    Just out of curiousity, do you want to just export a recordset to a tab delimited file? If so, just do so in code. Here's a sample...(I'm writing it raw here, so this is untested)

    Dim rs as Recordset
    Dim f as Long
    Dim strTemp as string
    Dim i as Long
    Set rs=CurrentDB.OpenRecordset("tblMyTable",dbOpenTabl e)
    If rs.EOF=False then rs.MoveFirst
    f=FreeFile
    Open "C:MyTextFile.txt" for Binary Access Write as f
    Do Until rs.EOF=True
    strTemp=""
    For i=0 to rs.Fields.Count-1
    strTemp=strTemp & rs.fields(i) & chr(9)
    Next i
    strTemp=left(strTemp,len(strTemp)-1) & vbcrlf
    Put f,,strTemp
    rs.MoveNext
    Loop
    rs.close
    close f

    There you have it, a tab delimited text file.....of course you could turn this into a reusable function if you wanted too.
    <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    <P ID="edit" class=small>(Edited by WendellB on 29-May-03 05:19. activate link)</P>look at the the following page http://www.rogersaccesslibrary.com/T...Contents2k.asp
    there is a file called ExportFormattedFixed2k.zip that may have a routine that is useful.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    Charlotte,

    If you export a bound form to a text file, the result is a formatted export of the record source. See screenshot.
    Attached Images Attached Images
    • File Type: png x.PNG (5.5 KB, 0 views)

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    That still isn't exporting the form though, Hans. It's merely exporting from the form.
    Charlotte

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export tab-delimited .txt file (2000 (9.0.3821 SR-1))

    That is correct, Charlotte, but I thought the purpose of the system Kris describes is to export data from the database, not the form itself. His "system experts" claimed that it isn't possible to export to a tab-delimited text file, so they export from the form to an Excel workbook.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •