Generate TXT file from a Table

[fgcity]

Hi all.

I have a Table with 3 fields.

A: 54
B: XKR56
C: 13:03 (This is time)
A: 32
B: YKT67
C: 14:12 (This is time)


I need to generate a TXT file from this Table that would be in the following format:

54
XKR56
13
03
32
YKT67
14
12

....... And so on

How do i do that?

 

[georgedwilkinson]

Try:

select replace(MyFieldName, ":", chr(13)) from MyTable;

and see if that gets you closer.

The next big deal is getting that to go to a text file instead of the screen.

 

[fgcity]

yeah, that worked, i see what you mean here.

To add all the data to a single field. But what if the data is more than 255 characters?

one field can hold only that much does it not ?

And then how do i save that single field to a txt file ?

 

[DCrake]

Simple Software Solutions

To start with you will need to use the following methodology

Open Myfile for output as #1

Where Myfile is the name of the path and file name of the text file

Then you need to open the desired table as a recordset

Employ a Do Until EOF for the recordset

set up a for next loop for each of the fields in the recordset

As you visit each of the fields store the contents to a string variable. you may need to convert none string fields to string types:

then write the string to the myfile

Finally close the open myfile


Summary:

Dim Myfile As String
Dim TmpStr As String

Myfile = "C:\Temp\Sample.Txt"

   Set Rst = CurrentDb.OpenRecordset(YourQuery)

   Open MyFile for output as #1

   Do Until Rst.EOF
      For x = 0 To Rst.Fields.Count -1
         TmpStr = Cstr(Rst(x))
         Print #1, TmpStr
      Next
   Rst.MoveNext
   Loop
   Set Rst = Nothing

   Close #1

This is the essence of what needs to be done. What I have not included is any error checking for existing output files, Null values in fields, Etc.

but this will get you on your way.

CodeMaster:
http://www.icraftlimited.co.uk

 

[fgcity]

Tnx, Really appriciate it. I'll try it now.

 

[fgcity]

YES, Worked like a charm

Tnx DCrake

I appriciate the help ALLOT.

 

[DCrake]

Simple Software Solutions

Glad to be of help

Don't forget when you get the desired response from a member to reward them by upping their reputation.

David

 

[georgedwilkinson]

Sorry I missed out on the fun. I've been having problems with the forum.

Glad you got it fixed.

 

[fgcity]

ok, then please help out with the following if possible.

i create a FileA from the fields i need in the format i need as described above.

When my loop runs it recreates the file and rewrites it. Which is VERY good since that's the way i want it.

But i need to do also the following:

since my loop runs every 5 seconds it rewrites the file every 5 seconds.

I need to create a second TXT file that would contain 1 if the file has changed (in context) or 0 if it hasn't.

How can i do this?

The reason i am asking this is that i have a GPRS device that downloads this TXT files from the internet through GPRS, so i need to download the File with the "0" or "1" to see if there is any data to download at all so this file is 1byte and if there is any change then to download the changed file.

I want to do it this way since GPRS is costing by the KB so i don't want to redownload the whole file every 5 seconds and pay the charges but only to download the small file and if there is any change to take out the bigger one.

 

[DCrake]

Simple Software Solutions

Do you need to test the download every 5 seconds? sounds pretty intense to me!
Also does it run on a 24 hour cycle?

How are you getting the data into Aceess in the first place?

Can you device identify the file size before it downloads it?

David

 

[fgcity]

Yes i need to refresh every 5 seconds, Yes it runs 24/7, the device can identify the file size since it's running Linux. But the file size can still remain the same with data change since a letter could simply change so the file size would be the same.

I;m getting the data into access with a software that receives SMS messages through a UCP protocol. It has a plug in to output the in messages and out messages in an Access Table

 

[DCrake]

Simple Software Solutions

So logically speaking how do you think it could detect any differences between the previous download and the current download?

Like you say one character change may not afect the overall file size and you need to read the file contents prior to the decision to download.

If you could read the contents before making that decision then it can be done. Let me know if this is possible.

David

 

[fgcity]

The way i think of it is that ACCESS will make a file called "Stat.txt"

Which will tell the status of the file (the main one)

In that stat file there will be a Date and time

so Access will update the date and time in that file only if the main text file has changed,

So When i download that Stat File (which is going to be allot smaller than the main file.

I will see if the data and time is the same a sthe previuse one i have downloaded and if so then i'll use the file that i already previusly downloaded.

So basicly i need Access to update the Stat.txt file with the date and time of the last update of the main file.

Does this make sence ?

 

[DCrake]

Simple Software Solutions

So from what I gather is that you are attempting to avoid performing duplicate downloads by comparing the time stamp of the incoming file to the last appended file.

But can you clarify at what point does the charges kick in?

and

Where is the source file generated and sitting waiting to be downloaded?

and

Where is your Access Database?

David

 

[fgcity]

the charges don't kick in.

I recieve an SMS.

That SMS contains the Text "34 KTL45" and the recieved time and the sender number

I take that SMS Message and split it into "34" and "KTL45"

i have a database of Spots from 0-60 so that "34" is the number of that spot.

So the :KTL45: goes to spot 34 in an empty field.

Each SMS that comes in has a Reciever Number (Me) this number is "3030"

If someone sends an SMS to this number then i know that i will hold this "KTL45" in spot 34 for 2 Hours (3030 is for 2 hours)

parallel to this i create a TXT file with the Code You gave Me DCrake, To generate the TXT file in the format i need from the Data Table of the Spots.

This file is posted on the Internet: on my Web server.

So with my device i can download this file through GPRS on the internet.

The whole idea is that GPRS charges per Kilobite. So i have to refresh only when needed, but i have to download atleast something every 5 seconds to see if the data has changed.

This is the only way i can trigger the device to download the whole Text file.

I Can't send anything to the device through GPRS only i can Download from it.

 

[DCrake]

Simple Software Solutions

Not completely tuned in to your logic, but trying.

So the question is what triggers the desicion to donwload?

Is is that changes have been made to your access database table or is it something else.

Do you create a new record in your table for each sms message received?

What are Spots and what are their purpose?

This file is posted on the Internet: on my Web server.

Do tyou mean FileA.txt the one you created earlier?

So with my device i can download this file through GPRS on the internet.

What is this file?

but i have to download atleast something every 5 seconds to see if the data has changed.

Download What?

 

[fgcity]

Yes, the question is what triggers the device to download.

I can't say what are the purpose of the "spots"

Yes, the file.TXT that i created earlyer with the format :

4
14
46
120
ghj35

IS what is actually posted on the internet (in a web link i have created)

The IS File is : MyTXT.txt which is posted on the weblink.



I have the file : MyFile.txt with the content:

4
14
46
120
ghj35

Posted on the internet.

I can download the file if i enter the weblink i have.

I have to download this file from my device through GPRS. But i don't want to download the same file if the data hasn't changed since that's allot of data every 5 seconds,

So i need a second file Stat.txt that would contain the Date of the last change of "MyFile.txt"

So i download that "Stat.txt" every 5 seconds and if the date is not the same as the previus data then i download the "MyFile.txt"

 

[DCrake]

Simple Software Solutions

Things are becoming a little more clearer now. However you suggest you only want to examine the date not the date and time of the previous file.

Don't know if this will help but when you create the file using the Open Myfile For OutPut as #1 but you can also use Open MyStatFile For Append As #1
Whereas Output creates a file; Append opens an existing file and writes to it as an extra line. So in theory you could write the data/time you created the earlier one and use this to compare it with for changes.

Suddenly everything has come to light When you post the large file to the internet you need to also post the small file, in this file is the date and time you posted the large one. So by downloading the small file you can determine whether you need to dwonload the large file.

So post both files together with a flag in it 0 or 1 as you suggested at the beginning in the small file. Then every 5 seconds download the small file and read the contents to make the decision to download.

David

 

[fgcity]

tnx, i'm doing it now. Hope it works.

It's getting a bit confusing now with allot of queries and updates.


There are many restrictions that i have to have in mind for this