EXPORT TO TEXTFILE (to specific positions)

[MsLady]

Beloved Access gurus,
I am wondering if it's possible to export specific columns into certain positions in the textfile? I haven't seen this done with Access before so i dont know. I have an access table that i would like exported periodically into a textfile (meaning, i have to do this with vba).

For example - loop through table, exporting into textfile:
column 1 ---> start on 1st line Position 20 in the text file (after 19 spaces)
column 2 ---> beginning of next line.
column 3 ---> back to previous line, position 40.

Is this possible? If so, any thoughts or suggestions will be greatly appreciated.

 

[Pat Hartman]

Look up the Print statement. You can't jump around in the file, you'll have to write all the fields for line 1, then the fields for line 2, etc.

 

[The_Doc_Man]

There are ways to do this directly and indirectly. VBA is the preferred way.

I'll point you to reading material from Access Help.

1. VBA's "Open for Output as ..." clause
2. VBA's "PRINT USING ...." clause
3. as an alternative to #2, VBA's "PRINT #x, FORMAT$(....); ...." statements

If you have a PRINT-USING template or a FORMAT$ template that has 19 spaces in front of the value, that's one way.

If you define a string constant in your class or general module that contains 19 spaces and that constant never gets edited, that's another way. In which case you just print the string constant followed by the first real field.

I'm sure there are other ways. This is the easiest to see.

Explanation: Treat the string constant as just another string to be printed. Remember that in print statements, you use ; not , to separate fields that you want to see adjacent to each other.

 

[liddlem]

Hi MsLady
Cant say that Ive tried this before myself, but think that I would approach it as follows.

1. Dim variable to identify each line that you plan to write
Dim FirstLine, SecondLine, etcLine as String

Do while NOT EOF()
2. FirstLine = " (19 spaces) " & FieldOneValue & " (x Spaces) " & FieldThreeValue
3. SecondLine = FieldTwoValue
4. Write FirstLine to TextFile
5. Write SecondLine to TextFile etc etc.
6. Move to next record
Loop

Hope this gets you started

 

[MsLady]

Wow, I really appreciate the wealth of information.
I have been putting this away for so long cos i didn't even know where to start and my boss has been nagging me about this lately.
Now it doesn't look so hard. I'll get started on this tomorrow and will let you all know my progress.

Pat Hartman: Thanks! that's what i thought. But liddlem provides a way of extracting each value from it's column..(?).
Certified Grandpa: Thank you: I'll read up and thanks for the tips! I don't think the constant will get edited tho.
liddlem: You break it down to seem so simple : ). It looks like i'll have to write sql statement to get the value of each column separately, am i right. I hope it doesn't run slow, but i'll def. get on this method first tomorrow.

Thanks for the Great brilliant ideas! i love you all *hugs

 

[WayneRyan]

MsLady,

Desired output file:

         1         2         3         4
1234567890123456789012345678901234567890 <-- "Just a ruler"

                  Field1    Field2       <-- 1st line, first record
Field3       Field4                      <-- 2nd line, first record
                  Field1    Field2       <-- 1st line, second record
Field3       Field4                      <-- 2nd line, second record

Code:

Dim rst As DAO.Recordset

Open "C:\OutputFile.txt" For Output As #1

Set rst = CurrentDb.OpenRecordSet("Select * From YourTable Order By Field1")
While Not rst.EOF And Not rst.BOF
   Print #1, Space(18) & RTrim(rst!Field1) & Space(9 - Len(RTrim(rst!Field1))) & rst!Field2
   Print #1, RTrim(rst!Field3) & Space(13 - Len(RTrim(rst!Field3))) & rst!Field4
   rst.MoveNext
   Wend
Close #1

hth,
Wayne

 

[DCrake]

Simple Software Solutions

Hi

Just picked up this thread, would like to make a comment or two.

What you have not mentioned is that the contents of each field may vary in length on subsequent runs. For example column 1 may contain a PK which will change in length from 999 to 1000 (3 to 4). So if you are preceeded your PK with 19 spaces then on the line would be 22 characters in length, whereas the second line would be 23 characters on length.

What I would suggest is to create a function call PadOut - for arguments sake. And would work as follows

Dim strLine As String

:Column1
StrLine = PadOut(Cstr(FieldNameA),20)

:Column2
StrLine = StrLine & PadOut(CStr(FieldNameB),10)

:etc

StrLine = StrLine & "^"


Public Function PadOut(AnyString As String,StrLength As Integer) As String

PadOut = Right(Spaces(100) & AnyString,StrLength)

End Function

The above function prefixes the incoming AnyString with 100 spaces and returns the right x charaters of the total length.

This ensures that no matter how long the incoming string is it will always return the maximum - parametised length of the string.

For Example (will use *'s as spaces for brevity)

PadOut("123.45"),10) would return ****123.45
PadOut("12345.67",10) would return **12345.67
PadOut("",10) would return *********

This will mean you will always have proportional spacing of your line.

To add another tip to this thread is to add an 'End of Line' marker to the end of the comleted line. Such as ^ so when the line is being read back in if can first check that the last character in the string is a carat this allows error trapping whereby a missing ^ would mean that the entire line was not created sucessfully.


Code Master:

 

[MsLady]

hi wayne & DCrake, you are hot like faya

Same method that liddlem suggested, so i thank you all, including Pat and Doc_man.
I didn't know it was this simple I wasn't familiar with the print function at all.

DCrake, Thanks for your great brilliant thought. I can def. use this in my other tasks. I've been wondering about that possibility and haven't gotten a chance to ask my boss yet, i have just been swarmed with other tasks. If that's the case-with the spacing like you say, which i suspect it is, you just saved me alot of work and i am forever grateful.

I'll be back to post my db.
Thanks all

 

[MsLady]

Look up the Print statement. You can't jump around in the file, you'll have to write all the fields for line 1, then the fields for line 2, etc.

im jumping around now and even flying
lol...j/k I know what you mean tho. Thanks Pat

 

[Pat Hartman]

I'm happy that you're happy