UtterAccess Discussion Forums > Export As Tab-delimited

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

corrieann

May 7 2012, 12:39 PM

I have a procedure that exports a database table:

CODE

Private Sub btnExportPartDatatoTXTFile_Click() 'PARTICIPANT

Dim strFilter As String
Dim strOutputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS. .xlsx)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.CSV")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt, *.tab)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strOutputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please name your output file...", _
                Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferText acExportDelim, , "tblParticipant_EV_Data", strOutputFileName, True

End Sub

I need make Text (tab delimited) an option...preferably the defualt option. I added "*.tab" to the string above, but that doesn't work.

Can anyone assist?

niesz

May 7 2012, 12:46 PM

Do you mean that you want to create the file using TABs as delimiters? or you want *.TAB files to show up in your file dialog?

To do the former, I believe you'll need to create an Export specification and reference it's name in the argument list.

corrieann

May 7 2012, 12:49 PM

I want to do the first option in your reply. I need to export a table from Access into a tab-delimited format.

niesz

May 7 2012, 12:51 PM

An export specification allows you to specify the delimiter.

corrieann

May 7 2012, 12:56 PM

All I heard was "Greek, Greek, Greek..."

theDBguy

May 7 2012, 12:58 PM

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

The only way I was able to export a table with tab delimiters is by using an export specification.

Just my 2 cents...

EDIT: I need to learn how to type faster on this phone.

niesz

May 7 2012, 01:01 PM

The TransferText method accepts an additional argument for a Specification Name:

DoCmd.TransferText acExportDelim, "SpecNameGoesHere", "tblParticipant_EV_Data", strOutputFileName, True

If you walk through the steps to export a table manually, you are prompted to save the export specification. That spec can be re-used over and over again so that you don't have to re-choose all the options.

One of the options is what kind of delimiter to use.

corrieann

May 7 2012, 04:36 PM

Why can't I just ad this line to my script?

CODE

strFilter = ahtAddFilterItem(strFilter, "TAB Files (*.TAB)", "*.TAB")

I added it, but the file output still looks like a comma delimited file.

corrieann

May 7 2012, 05:02 PM

QUOTE

CODE

DoCmd.TransferText acExportDelim, "SpecNameGoesHere", "tblParticipant_EV_Data", strOutputFileName, True

I cannot use an import spec due to the table being different every time. (Long story....see my other posts for explanation)

niesz

May 7 2012, 07:53 PM

If you cannot create an export spec, the only other way I know of would be to create the file manually through VBA.

'a little air code

Open "yourfilename" for output as #1

Print #1, field1 & vbtab & field2 & vbtab & field3 ....

'loop

Close #1

corrieann

May 8 2012, 12:53 PM

No, that won't work. Sometimes the tables have 9 fields, sometimes 217.

niesz

May 8 2012, 12:57 PM

True, ... you'd have to cycle through the Fields collection of the recordset and build up the line of data.

theDBguy

May 8 2012, 01:01 PM

QUOTE (corrieann @ May 8 2012, 10:53 AM)

No, that won't work. Sometimes the tables have 9 fields, sometimes 217.

Sure it would...

intFields = tdf.Fields.Count
For x = 0 To intFields - 1
Put #1, tdf.Fields(x) & vbTab
Next x

(untested)
Just my 2 cents...

EDIT: Oops, didn't see that Walter already got it covered. Sorry.

corrieann

May 9 2012, 03:33 PM

"Put #1"??

corrieann

May 9 2012, 03:39 PM

I have this so far:

CODE

Private Sub btnExportPartDatatoTXTFile_Click() 'PARTICIPANT

Dim strFilter As String
Dim strOutputFileName As String
Dim tdf As DAO.TableDef

Open "tblParticipant_EV_Data" For Output As #1
intFields = tdf.Fields.Count
For x = 0 To intFields - 1
strFilter = tdf.Fields(x) & vbTab
Next x

DoCmd.TransferText acExportDelim, , "tblParticipant_EV_Data", strOutputFileName, True

End Sub

The error I get is this: Object variable or With block variable not set
Debug =

CODE

intFields = tdf.Fields.Count

theDBguy

May 9 2012, 03:48 PM

QUOTE (corrieann @ May 9 2012, 01:33 PM)

"Put #1"??

Yes, as opposed to Walter's suggestion to use:

Print #1

Sorry, I can't remember the difference. Please give it a try and let us know if it works or not. Thank you.

Just my 2 cents...

corrieann

May 9 2012, 03:59 PM

Sorry for my thickness, but I have this now:

CODE

Dim strFilter As String
Dim strOutputFileName As String
Dim tdf As DAO.TableDef

Open "tblParticipant_EV_Data" For Output As #1
intFields = tdf.Fields.Count
For x = 0 To intFields - 1
Print #1 = tdf.Fields(x) & vbTab
Next x

DoCmd.TransferText acExportDelim, , "tblParticipant_EV_Data", strOutputFileName, True

End Sub

And this line highlights in red:

CODE

Print #1 = tdf.Fields(x) & vbTab

With the following error: "Expected: list separator"

theDBguy

May 9 2012, 04:18 PM

Yes, the syntax is to use a comma instead of the equal sign.

And one more thing... If you are creating the text file using the Open statement and putting the data in it using Print or Put, then there should be no need to use the TransferText method.

Just my 2 cents...

corrieann

May 9 2012, 04:27 PM

Ok, but I still get the "Object variable or With block variable not set" error:

CODE

Debug =

CODE

intFields = tdf.Fields.Count

theDBguy

May 9 2012, 04:35 PM

Yes, in your code, you did not "Set" the tdf to a table object. You'll need to set the object variable to the table with the fields you want to export. For example:

Set tdf = CurrentDb.TableDefs("TableName")

Just my 2 cents...

corrieann

May 9 2012, 05:21 PM

CODE

Private Sub btnExportPartDatatoTXTFile_Click() 'PARTICIPANT

Dim tdf As DAO.TableDef

Set tdf = CurrentDb.TableDefs("tblParticipant_EV_Data")
Open "tblParticipant_EV_Data" For Output As #1
intFields = tdf.Fields.Count
For x = 0 To intFields - 1
Print #1, tdf.Fields(x) & vbTab
Next x

End Sub

Error: "Object invalid or no longer set."
On this line:

CODE

intFields = tdf.Fields.Count

I thought there may be conflict with my Dim statement and this line, so I commented it out, but got the same error. Grrr...

theDBguy

May 9 2012, 05:27 PM

Hmm, you might want to step through your code to make sure things are doing what you expect. Also, shouldn't you have an extension in your filename? For example:

Open "tblParticipant_EV_Data.txt" For Output As #1

Just my 2 cents...

corrieann

May 9 2012, 05:29 PM

QUOTE

Open "tblParticipant_EV_Data.txt" For Output As #1

I don't know. I have never used this command before. And the table I am wanting to output is a table within my database. I didn't think those had extensions. Do they?

niesz

May 9 2012, 05:39 PM

PMFJI, but I would cycle through the fields and build up a variable, then at the end of a row, Print# the variable to the file, once per row. The Put# command will not issue a CrLf after the data.

theDBguy

May 9 2012, 09:18 PM

Hi,

QUOTE (corrieann @ May 9 2012, 03:29 PM)

I don't know. I have never used this command before. And the table I am wanting to output is a table within my database. I didn't think those had extensions. Do they?

In this case, we are trying to use the Open statement to create the tab-delimited file "manually" instead of using the TransferText method.

You could also consider what Walter just mentioned about constructing the record in one line before writing it out to the file using the Print# command. I only recommended to use the Put# command because my original approach was to output each field to the file, one at a time.

Just my 2 cents...

niesz

May 10 2012, 06:57 AM

Try something like this: (you can change the file name to a directory or your desktop for testing, and then switch out to your file picker code later)

CODE

Private Sub btnExportPartDatatoTXTFile_Click() 'PARTICIPANT

    Dim rs As DAO.Recordset
    Dim i As Long
    Dim LineOfText As String
    Dim Delimiter As String

    Delimiter = vbTab

    Open "c:\documents and settings\nieszhw\desktop\MyTestTabFile.TAB" For Output As #1

    Set rs = CurrentDb.OpenRecordset("tblParticipant_EV_Data")

    With rs

        'if you want column headers, uncomment the following 5 lines of code
'        For i = 0 To .Fields.Count - 1
'            LineOfText = LineOfText & .Fields(i).Name & Delimiter
'        Next i
'
'        Print #1, LineOfText
'        LineOfText = ""

        'loop through records
        Do While Not .EOF
            'build up line of text
            For i = 0 To .Fields.Count - 1
                LineOfText = LineOfText & Nz(.Fields(i)) & Delimiter
            Next i
            'write line of text to file
            Print #1, LineOfText
            LineOfText = ""
        .MoveNext
        Loop
    End With

    Close #1

End Sub

corrieann

May 10 2012, 11:18 AM

Ok, I implemented your code and brought in my file picker code. So, it looks like this:

CODE

Dim strFilter As String
    Dim rs As DAO.Recordset
    Dim i As Long
    Dim LineOfText As String
    Dim Delimiter As String

    Delimiter = vbTab

strFilter = ahtAddFilterItem(strFilter, "Tab Files (*.tab)", "*.tab")
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.CSV")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strOutputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please name your output file...", _
                Flags:=ahtOFN_HIDEREADONLY)

    Set rs = CurrentDb.OpenRecordset("tblParticipant_EV_Data")

    With rs

        'if you want column headers, uncomment the following 5 lines of code
        For i = 0 To .Fields.Count - 1
            LineOfText = LineOfText & .Fields(i).Name & Delimiter
        Next i

        Print #1, LineOfText
        LineOfText = ""

        'loop through records
        Do While Not .EOF
            'build up line of text
            For i = 0 To .Fields.Count - 1
                LineOfText = LineOfText & Nz(.Fields(i)) & Delimiter
            Next i
            'write line of text to file
            Print #1, LineOfText
            LineOfText = ""
        .MoveNext
        Loop
    End With

    Close #1

End Sub

When I launch the script, the Save window will open and I can name the output file. Once I click OK to save, then it crashes, giving me the following error: "bad file name or number"

Which causes this line to highlight in break mode:

CODE

Print #1, LineOfText

As I step through the code I see that these variables are blank (empty):

CODE

LineOfText = LineOfText & .Fields(i).Name & Delimiter

niesz

May 10 2012, 12:15 PM

You haven't opened the file.

You need this line of code:

Open strOutputFileName For Output As #1

The only thing this function does (ahtCommonFileOpenSave) is put a full path name into a string variable. It has no other effect or purpose. It does not create a file, It does not open a file.

Once you have that path name, you still have to act upon it and open the file.

corrieann

May 10 2012, 12:46 PM

Oops. How did that get deleted? LOL

For those interested, here is the final code, which works!

CODE

    Dim strFilter As String
    Dim rs As DAO.Recordset
    Dim i As Long
    Dim LineOfText As String
    Dim Delimiter As String

    Delimiter = vbTab

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.CSV")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strOutputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please name your output file...", _
                Flags:=ahtOFN_HIDEREADONLY)

Open strOutputFileName For Output As #1

    Set rs = CurrentDb.OpenRecordset("tblParticipant_EV_Data")

    With rs

        'if you want column headers, uncomment the following 5 lines of code
        For i = 0 To .Fields.Count - 1
            LineOfText = LineOfText & .Fields(i).Name & Delimiter
        Next i

        Print #1, LineOfText
        LineOfText = ""

        'loop through records
        Do While Not .EOF
            'build up line of text
            For i = 0 To .Fields.Count - 1
                LineOfText = LineOfText & Nz(.Fields(i)) & Delimiter
            Next i
            'write line of text to file
            Print #1, LineOfText
            LineOfText = ""
        .MoveNext
        Loop
    End With

    Close #1

THANKS EVERYONE!!!

theDBguy

May 10 2012, 12:47 PM

Hi corrieann,

Congratulations!!! Good luck!

niesz

May 10 2012, 12:59 PM

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.