By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Export data to sequential file

P: 6
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.
Mar 5 '07 #1
Share this Question
Share on Google+
19 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Does it have to be a hash? You could do basically the same thing with a csv.
Mar 5 '07 #2

P: 6
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.
Mar 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
You can choose which delimiter you want to use from the export wizard if you select the text file format.
Mar 5 '07 #4

P: 6
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.
Mar 6 '07 #5

10K+
P: 13,264
Changed thread title.
Mar 6 '07 #6

Rabbit
Expert Mod 10K+
P: 12,315
Your first post said you wanted a carriage return after the second field, is that not a new line?

From the help file:
Expand|Select|Wrap|Line Numbers
  1. CreateTextFile Method
  2.  
  3. Description:
  4. Creates a specified file name and returns a TextStream object that
  5. can be used to read from or write to the file.
  6.  
  7. Syntax: object.CreateTextFile(filename[, overwrite[, unicode]])
  8.  
  9. The CreateTextFile method has these parts:
  10.  
  11. object - Required: Always the name of a FileSystemObject or Folder
  12. object.  filename Required. String expression that identifies the file
  13. to create. 
  14.  
  15. overwrite - Optional: Boolean value that indicates if an existing file
  16. can be overwritten. The value is True if the file can be overwritten; False
  17. if it can't be overwritten. If omitted, existing files are not overwritten. 
  18.  
  19. unicode - Optional: Boolean value that indicates whether the file is
  20. created as a Unicode or ASCII file. The value is True if the file is created
  21. as a Unicode file; False if it's created as an ASCII file. If omitted, an
  22. ASCII file is assumed. 
  23.  
  24.  
  25. Remarks
  26.  
  27. The following code illustrates how to use the CreateTextFile method to
  28. create and open a text file:
  29.  
  30. Sub CreateAfile
  31.     Set fs = CreateObject("Scripting.FileSystemObject")
  32.     Set a = fs.CreateTextFile("c:\testfile.txt", True)
  33.     a.WriteLine("This is a test.")
  34.     a.Close
  35. End Sub
  36.  
  37. If the overwrite argument is False, or is not provided, for a filename
  38. 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.
Mar 6 '07 #7

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim strLongString As String
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
  6.  
  7. Do While Not MyRS.EOF
  8.   'Build up the String here
  9.   strLongString = strLongString & MyRS![Field 1] & ", " & MyRS![Field 2] & ", "
  10.   MyRS.MoveNext
  11. Loop
  12.  
  13. MyRS.Close
  14.  
  15. 'Remove Trailing Comma and Space
  16. strLongString = Left$(strLongString, Len(strLongString) - 2)
  17.  
  18. Open "C:\Sequential.txt" For Output As #1
  19. Print #1, strLongString
  20. Close #1
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Retail, Sat, Direct, Sun, Blah, Mon, Whatever, Tue
Mar 6 '07 #8

Rabbit
Expert Mod 10K+
P: 12,315
Assuming [Field 1] and [Field 2] exist in a Table named Table1, here is your basic code template. You will only need minor modifications:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim strLongString As String
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
  6.  
  7. Do While Not MyRS.EOF
  8.   'Build up the String here
  9.   strLongString = strLongString & MyRS![Field 1] & ", " & MyRS![Field 2] & ", "
  10.   MyRS.MoveNext
  11. Loop
  12.  
  13. MyRS.Close
  14.  
  15. 'Remove Trailing Comma and Space
  16. strLongString = Left$(strLongString, Len(strLongString) - 2)
  17.  
  18. Open "C:\Sequential.txt" For Output As #1
  19. Print #1, strLongString
  20. Close #1
The problem with Print # is that with a string, it will enclose it in double quotes.
Mar 6 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 6 '07 #10

ADezii
Expert 5K+
P: 8,597
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.
Mar 6 '07 #11

Rabbit
Expert Mod 10K+
P: 12,315
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.
Mar 6 '07 #12

ADezii
Expert 5K+
P: 8,597
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.
Mar 6 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 6 '07 #14

P: 6
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
Mar 7 '07 #15

P: 6
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.
Mar 7 '07 #16

ADezii
Expert 5K+
P: 8,597
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.
Mar 7 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 7 '07 #18

P: 6
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!
Mar 7 '07 #19

ADezii
Expert 5K+
P: 8,597
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
Mar 7 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.