Solved

EXPORTING TABLE AS A FORMATTED TEXT WITH SOME DESCRIPTION HEAD......

Posted on 2001-06-27
10
268 Views
Last Modified: 2012-05-04
Hello My Firends,

I want to export my table as a text file with some  head description because of other system wants this special text format.

Forexample;
My table;

Name (Field one)   Telephone_Number (Field2)

Huseyin            532-396 44 90
George             432-234 23 23
Elizabeth          432-123 23 43
.

I want to export table above to text format like below.

#This File was created 1/1/2001
#Author=Friedhelm Neils..
/***********************************************************
 *** File name: featup1.txt                              ***
 ***                                                     ***
 *** Interface file for feature upload with atrackturbo  ***
 ***                                                     ***
 ***                                                     ***
 *** Command: atrturbo -i featup1.txt                    ***
 ***********************************************************/

ACTION UPLOAD FEATURELOAD
/* SYNTAXCHECK YES */
LANGUAGE "turkish"
FROM_DATE $01.01.1995$
PANEL ALL
SORT Articles
USER DEFAULT


FEATURE DEFINITION        

   NAME  OF TYPE ENUMTYPE          
        VALUE "Huseyin"
        VALUE "George"
        VALUE "Elizabeth"
   END

   TELEPHONE_NUMBER OF TYPE ENUMTYPE
        VALUE "532-396 44 90"
        VALUE "432-234 23 23"
        VALUE "432-123 23 43"
   END
END

So, how can I add the descriptions to text file?

Best regards,
Huseyin.






 
   
 
0
Comment
Question by:hbiyik
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 9

Accepted Solution

by:
perove earned 200 total points
Comment Utility
You neet to use VBA to solve this, the *normal* access export will not do for your porblem

Here i a simplified function that you need to do some modification to work perfecly, but you should get the picture

Sub PrintTable()
    Dim myLine As String
    Dim FNR As Integer
    Dim Db As Database
    Dim rs As Recordset
    On Error GoTo hell
    Set DbLokal = CurrentDb
    Set rs = DbLokal.OpenRecordset("MyTable")
    FNR = FreeFile
    Open Format(InputBox("where is the file to be stored"), "!&") For Output As FNR
    Print #FNR, "#This File was created 1/1/2001"
    Print #FNR, "#Author=Friedhelm Neils.."
    '.. more haeder stuff here
    Print #FNR, "NAME  OF TYPE ENUMTYPE"
    rs.MoveFirst
    Do Until rs.EOF
        myLine = "VALUE" & Chr(34) & rs("Name") & Chr(34)
        Print #FNR, myLine
        rs.MoveNext
    Loop
    Print #FNR, "END"
    Print #FNR, "TELEPHONE_NUMBER OF TYPE ENUMTYPE"
    rs.MoveFirst
    Do Until rs.EOF
        myLine = "VALUE" & Chr(34) & rs("Telephone_Number") & Chr(34)
        Print #FNR, myLine
        rs.MoveNext
    Loop
    Print #FNR, "END"
 
   
Close
Exit Sub
hell:
MsgBox "error"
Stop
Close

End Sub
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi hbiyik,

Copy the following code to a (new?) module:

Option Compare Database
Option Explicit

Public Sub ExportTextFile(oFileName As String, TableName As String)

    Dim Rst As Recordset
    Dim DBs As Database

    If Dir(oFileName) <> "" Or IsNull(TableName) Or Nz(TableName, "") = "" Then
        'File Exists
        MsgBox "File " & oFileName & " Exists" & vbCrLf & "Or" & _
               "TableName is missing!" & vbCrLf & vbCrLf & _
               "Please supply valid parameters!"
    Else
        'Export!
        Set DBs = CurrentDb
        Set Rst = DBs.OpenRecordset("SELECT * FROM " & TableName)
        Open oFileName For Output As #1
       
        'Write header to file
        Print #1, "#This File was created 1/1/2001"
        Print #1, "#Author=Friedhelm Neils.."
        Print #1, "/***********************************************************"
        Print #1, "*** File name: featup1.txt                              ***"
        Print #1, "***                                                     ***"
        Print #1, "*** Interface file for feature upload with atrackturbo  ***"
        Print #1, "***                                                     ***"
        Print #1, "***                                                     ***"
        Print #1, "*** Command: atrturbo -i featup1.txt                    ***"
        Print #1, "***********************************************************/"
        Print #1, ""
        Print #1, "ACTION UPLOAD FEATURELOAD"
        Print #1, "/* SYNTAXCHECK YES */"
        Print #1, "LANGUAGE ""turkish"""
        Print #1, "FROM_DATE $01.01.1995$"
        Print #1, "Panel ALL"
        Print #1, "Sort Articles"
        Print #1, "User Default"
        Print #1, ""
        Print #1, ""
        Print #1, "FEATURE DEFINITION"
        Print #1, ""
        Print #1, "  NAME  OF TYPE ENUMTYPE"
        Rst.MoveFirst
        With Rst
            While Not .EOF
                Print #1, "    Value """ & !Name & """"
                .MoveNext
            Wend
        End With
        Print #1, "  END"
        Print #1, ""
        Print #1, "  TELEPHONE_NUMBER OF TYPE ENUMTYPE"
        Rst.MoveFirst
        With Rst
            While Not .EOF
                Print #1, "    Value """ & !Telephone_Number & """"
                .MoveNext
            Wend
        End With
        Print #1, "  END"
        Print #1, "END"
        Close #1
        Rst.Close
        Set DBs = Nothing
    End If
End Sub

'To test the routine run the test sub

Sub test()
     ExportTextFile "MyText.txt", "MyTable"
End Sub


Note that if the text file exists, then the sub will not continue the execution. You can control the behaviour of the routine by changing the following if statement:
If Dir(oFileName) <> "" Or IsNull(TableName) Or Nz TableName, "") = "" Then
To:
If IsNull(TableName) Or Nz(TableName, "") = "" Then

This way, the function will test for the Table name only.

Hope this helps,

Nosterdamus
0
 
LVL 3

Expert Comment

by:sbereli
Comment Utility
Hi!
 I think if there are some different files with different headres and formats, you should write more ABSTRACT module which will serve you for all of formats.

 An example you can see above( those guys did good job), but if you will generalize those functions it will be more efficient.

Best Regards,
0
 
LVL 3

Expert Comment

by:sbereli
Comment Utility
as continue for my previous message, I thought about:

1.write  a module which contain functions as following:
  function which opens a file with "Header format"

2. function which opens DB and inserts the records , i.e. data handler (like PEROVE wrote)

3. function which will coordinate those 2 func. described above.(sort of main)

Best Regards,

0
 
LVL 19

Expert Comment

by:webwoman
Comment Utility
There's a MUCH easier way...
Set up a report with what you need. ONLY what you need, and either key it off a query that's already sorted or sort in the report (if you need it). Put in whatever text you need, from what you provided it looks like you might want a report/subreport, with section headers to put in the other text.

Open the report. Make sure it's what you want.

Then you write a macro that consists of one command -- OutputTo
what you output is your report. Filename you can make up. Format is MSDOS text. You can autostart or not, I usually do.

When you run the macro, you'll get prompted to save the file, overwriting any existing one. What you'll get will be a text file, with whatever info you had in the report, in the order you set up.

I use this to generate a list of upcoming events for a particular period (usually a week), in date order, with beginning/ending blurbs, that gets pasted into a mail message. I'm sure I could automate that part too, but I haven't bothered because there can be some judgement calls on whether to actually include some things.

It takes only a few minutes to set up, requires no vb knowledge or commands, and works just as well as anything else. ;-)

Worse case would be you generate 2 reports, open them in a text editor, copy/paste one then copy/paste the other.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hbiyik
Comment Utility
Thank my friend your answer. I'll evaluate them and turn you back.

CYA
Huseyin.
0
 

Author Comment

by:hbiyik
Comment Utility
Hello Nostradamus and Perove,

I tried your VB codes. However, I got same error message for them. It was "Type mistmatch" for
"Set Rst = DBs.OpenRecordset("SELECT * FROM " & TableName)"
line.

I set TableName="rehber" (My table name is "rehber")

I think there is a problem at defined preferences. I changed but I couldn't get result. Which preferences have to be defined to work properly?

There is the begining of the code that I modified below.


Public Sub ExportTextFile(oFileName As String, TableName As String)

   Dim Rst As Recordset
   Dim DBs As Database
TableName = "rehber"

   If Dir(oFileName) <> "" Or IsNull(TableName) Or Nz(TableName, "") = "" Then
       'File Exists
       MsgBox "File " & oFileName & " Exists" & vbCrLf & "Or" & _
              "TableName is missing!" & vbCrLf & vbCrLf & _
              "Please supply valid parameters!"
   Else
       'Export!
       Set DBs = CurrentDb
       Set Rst = DBs.OpenRecordset("SELECT * FROM " & TableName)
       Open oFileName For Output As #1
.
.

Best regards,
Huseyin.

0
 
LVL 9

Expert Comment

by:perove
Comment Utility
Maby you are using ADO to "talk" the data insted of DAO, which I and Nostra.. had in mind when we wrote the code.

goto tools->references and see if you have selected "micrsosft active X data objects..."(ADO) instead of Micrsosoft DAO xx object library(DAO).

If you have not any other VBA code that uses recordset and database object in your project just replace the ADO with DAO

perove
0
 

Author Comment

by:hbiyik
Comment Utility
Hello My Friends,

Yes, that's it. It works. I can get file format that I wanted.

Thanks Nosterdamus, Webwoman for answers.

Nosterdamus, this method was the same as Perove and he answered as first and last. Webwoman, your method doesn't response my problem. It wasn't efficient because I'm gonna use several text files and their fields. So, the VBcodes will be good for me.

Perove, you deserved the my 200 points. Thank you again.

By the way, are your Companies looking for IT Specialist. I think this is my last work at my current Company. So, my Company was sold. I think I will be unemployment.

hbiyik@yahoo.com
ISTANBUL/TURKEY

Best regards,
Huseyin.

0
 

Author Comment

by:hbiyik
Comment Utility
Thanks.
By the way, where are you from?
Huseyin.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

744 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

9 Experts available now in Live!

Get 1:1 Help Now