Export data to sequential file

[jason2412]

Hi,

Not sure if this is the right place to post this query so apologies in advance.

I have two columns in an access table.

Example for 2 records:

Field 1 - Retail
Field 2 - Sat
Field 1 - Direct
Field 2 - Sun


Can someone tell me how you output this as a sequential file? So the text file reads Retail#SatDire ct#Sun ...so that a hash is inserted after field 1 and a carriage return is entered after field 2.

Any help would be appreciated.

[Rabbit]

Does it have to be a hash? You could do basically the same thing with a csv.

[jason2412]

Does it have to be a hash? You could do basically the same thing with a csv.

Yes, has to be a hash as a script then reads the text file. Can this be done...i.e so the output is not column by column but reads across the way? I have tried looking around for a solution but have drawn a big blank.

[Rabbit]

You can choose which delimiter you want to use from the export wizard if you select the text file format.

[jason2412]

Maybe I am not being clear. I dont need help with the export function...i.e tab delimited etc. I need help creating a sequential file.

Example:

Field 1, Field 2
Field 1, Field 2
Field 1, Field 2

The above is an example of exporting the file. What I need is the file to look like:

Field 1, Field 2, Field 1, Field 2, Field 1, Field 2

So all records run one after the other across the way.

[r035198x]

Changed thread title.

[Rabbit]

Your first post said you wanted a carriage return after the second field, is that not a new line?

From the help file:

CreateTextFile Method

Description:
Creates a specified file name and returns a TextStream object that
can be used to read from or write to the file.

Syntax: object.CreateTextFile(filename[, overwrite[, unicode]])

The CreateTextFile method has these parts:

object - Required: Always the name of a FileSystemObject or Folder
object.  filename Required. String expression that identifies the file
to create. 

overwrite - Optional: Boolean value that indicates if an existing file
can be overwritten. The value is True if the file can be overwritten; False
if it can't be overwritten. If omitted, existing files are not overwritten. 

unicode - Optional: Boolean value that indicates whether the file is
created as a Unicode or ASCII file. The value is True if the file is created
as a Unicode file; False if it's created as an ASCII file. If omitted, an
ASCII file is assumed. 


Remarks

The following code illustrates how to use the CreateTextFile method to
create and open a text file:

Sub CreateAfile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\testfile.txt", True)
    a.WriteLine("This is a test.")
    a.Close
End Sub

If the overwrite argument is False, or is not provided, for a filename
that already exists, an error occurs.

So what you'll have to do is create a loop that concatenates all your data into one string.

[ADezii]

Hi,

Not sure if this is the right place to post this query so apologies in advance.

I have two columns in an access table.

Example for 2 records:

Field 1 - Retail
Field 2 - Sat
Field 1 - Direct
Field 2 - Sun


Can someone tell me how you output this as a sequential file? So the text file reads Retail#SatDire ct#Sun ...so that a hash is inserted after field 1 and a carriage return is entered after field 2.

Any help would be appreciated.

Assuming [Field 1] and [Field 2] exist in a Table named Table1, here is your basic code template. You will only need minor modifications:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim strLongString As String

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Table1", dbOpenSnapshot)

Do While Not MyRS.EOF
  'Build up the String here
  strLongString = strLongString & MyRS![Field 1] & ", " & MyRS![Field 2] & ", "
  MyRS.MoveNext
Loop

MyRS.Close

[B]'Remove Trailing Comma and Space[/B]
strLongString = Left$(strLongString, Len(strLongString) - 2)
 
Open "C:\Sequential.txt" For Output As #1
Print #1, strLongString
Close #1

OUTPUT:

Retail, Sat, Direct, Sun, Blah, Mon, Whatever, Tue

[Rabbit]

Assuming [Field 1] and [Field 2] exist in a Table named Table1, here is your basic code template. You will only need minor modifications:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim strLongString As String

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Table1", dbOpenSnapshot)

Do While Not MyRS.EOF
  'Build up the String here
  strLongString = strLongString & MyRS![Field 1] & ", " & MyRS![Field 2] & ", "
  MyRS.MoveNext
Loop

MyRS.Close

[B]'Remove Trailing Comma and Space[/B]
strLongString = Left$(strLongString, Len(strLongString) - 2)
 
Open "C:\Sequential.txt" For Output As #1
Print #1, strLongString
Close #1

The problem with Print # is that with a string, it will enclose it in double quotes.

[NeoPa]

Maybe I am not being clear. I dont need help with the export function...i.e tab delimited etc. I need help creating a sequential file.

Example:

Field 1, Field 2
Field 1, Field 2
Field 1, Field 2

The above is an example of exporting the file. What I need is the file to look like:

Field 1, Field 2, Field 1, Field 2, Field 1, Field 2

So all records run one after the other across the way.

Maybe it's me, but you seem to be contradicting yourself.
In post #1 you say you want a Carriage Return after the Field2s. Now you say you want all the data showing in a single line with commas separating?
It's particularly hard to answer questions where the questioner themself seems to be unsure what they want.
I'm absolutely sure we have an answer for your question. If only you can express it clearly and unambiguously.

[ADezii]

The problem with Print # is that with a string, it will enclose it in double quotes.

Rabbit:
Write # will - not Print #. That is why I intentionally used it instead of the alternative. The Output will be exactly as shown.

[Rabbit]

Rabbit:
Write # will - not Print #. That is why I intentionally used it instead of the alternative. The Output will be exactly as shown.

That works then. I must have gotten them mixed up.

[ADezii]

Maybe it's me, but you seem to be contradicting yourself.
In post #1 you say you want a Carriage Return after the Field2s. Now you say you want all the data showing in a single line with commas separating?
It's particularly hard to answer questions where the questioner themself seems to be unsure what they want.
I'm absolutely sure we have an answer for your question. If only you can express it clearly and unambiguously.

NeoPa:
That's exactly why I specified 'code template' in my solution.

[NeoPa]

NeoPa:
That's exactly why I specified 'code template' in my solution.

I'm not sure I understand what you're explaining here ADezii.
I was not implying criticism of your post - or your code.
I was trying to get the OP to understand the importance of stating his requirements clearly and accurately.
If it needs to include the <CR> in each line then the way to proceed would surely be to use the Export function rather than Print# within VBA.
If not, then your solution would be the sensible approach.
We can't know which path to suggest without a more clearly stated question.

[jason2412]

Yes, I can see how I confused the matter when I included an example with commas.

For clarity:

Field 1 - Retail
Field 2 - Sat
Field 1 - Direct
Field 2 - Sun

The required output for the above example would be:

Retail#SatDire ct#Sun

So a hash is added after field 1 and a carriage return () is added after field 2. All the data would run sequentially (left to right).

The hash and carriage return symbol is needed because a script needs that format.

Hopefully that clarifies things and a solution can be provided.

Big Thanks