Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-06-27
10
Medium Priority
?
285 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 800 total points
ID: 6231930
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
ID: 6231953
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
ID: 6232612
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:sbereli
ID: 6232627
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
ID: 6232913
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
 

Author Comment

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

CYA
Huseyin.
0
 

Author Comment

by:hbiyik
ID: 6234330
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
ID: 6234469
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
ID: 6237269
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
ID: 6237277
Thanks.
By the way, where are you from?
Huseyin.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

782 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