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�Direct#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�Direct#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
 
 'Remove Trailing Comma and Space
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
 
 'Remove Trailing Comma and Space
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�Direct#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

[jason2412]

ADezii - Thanks for the code. Do I just copy this into a module? Just not sure how I use the code so that I can see the text file generated.

[ADezii]

ADezii - Thanks for the code. Do I just copy this into a module? Just not sure how I use the code so that I can see the text file generated.

Just Copy and Paste it wherever you want. It can be placed in the Click() Event of a Command Button. a Function Procedure, Sub-Routine Procedure, etc. Replace the comma after Field1 if you want a different Delimiter, replace the comma after Field2 if you want an End of Record indicator. Change the Path of Sequential.txt if you like, and simply Open it to view the Output. Any other questions, feel free to ask.

[NeoPa]

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�Direct#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

Do you need a record separator of a Carriage Return specifically, or would a Carriage Return / Line Feed sequence (standard DOS/Windows line separator) do?
If so, then a standard text export would do for you.
If not, ADezii's code, with simple modifications to use the '#' & <CR> (vbCR constant in VBA) in place of the ','s would be the recommended approach.
In truth, for the former, using ADezii's code with the VbCrLf constant instead, would also work perfectly well for you.

[jason2412]

Pasted the code in the click event of a command button but got a file/path access error. When I go into debug it highlights the line 'Open "C:\Sequential.txt" For Output As #1'.

I created a folder in my C: called sequential...was that the right thing to do?

Not very good with visual basic etc so not sure why that last line of code is not working.

Ps...cheers to all for the help, appreciated!

[ADezii]

Pasted the code in the click event of a command button but got a file/path access error. When I go into debug it highlights the line 'Open "C:\Sequential.txt" For Output As #1'.

I created a folder in my C: called sequential...was that the right thing to do?

Not very good with visual basic etc so not sure why that last line of code is not working.

Ps...cheers to all for the help, appreciated!

There may be restrictions on the System that involved writing to the Root Directory of Drive C: (C:\). You can create any Folder/Sub-Folder you like but just remember to modify your code, specifically:
Open "C:\NewFolder\Sub-Folder\Sequential.txt" For Output As #1