Export query to .txt file with incrementing number and specific formatting

DonnaCulff

Registered User.
Local time
Yesterday, 21:46
Joined
Aug 25, 2009
Messages
30
Good afternoon all,

I hope everyone is well?

I was wondering If anyone could help with the following problem I am having. I need to export the results of a query in the following format, each row having a line number...

1,61545001,,21/04/2009 08:00,21/04/2009 17:00,16/04/2009 17:00
2,61545001,,28/04/2009 08:00,28/04/2009 17:00,23/04/2009 17:00
3,61545001,,05/05/2009 08:00,05/05/2009 17:00,30/04/2009 17:00
4,61545001,,12/05/2009 08:00,12/05/2009 17:00,07/05/2009 17:00
5,61545001,,19/05/2009 08:00,19/05/2009 17:00,14/05/2009 17:00
6,61545001,,26/05/2009 08:00,26/05/2009 17:00,21/05/2009 17:00
7,61545001,,02/06/2009 08:00,02/06/2009 17:00,28/05/2009 17:00
8,61545001,,09/06/2009 08:00,09/06/2009 17:00,04/06/2009 17:00
9,61545001,,16/06/2009 08:00,16/06/2009 17:00,11/06/2009 17:00

The fields involved are:
  • txtUnitCode e.g 61545001
  • txtStartDate e.g. 21/04/2009
  • txtStartTime e.g. 08:00
  • txtEndDate e.g. 11/06/2009
  • txtEndTime e.g. 17:00
I have managed to export a whole table with the following code, and I am sure I can change it so it points to a query. Its just the formatting I am stuggling with.

''''''''''''''''''My Code'''''''''''''''''''''

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String

Set db = CurrentDb
blnColumnHeaders = True
strDelimeter = Chr(9)
Set rst = db.OpenRecordset("select * from tblData")

Open "S:\Telemark\PROJECTS\ACTIVE_PROJECTS\exportfile.txt" For Output As #1
If blnColumnHeaders = True Then
For Each fld In rst.Fields
strText = strText & fld.Name & strDelimeter
Next fld

'get rid of the last delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))

strText = strText & vbNewLine
End If

Do While Not rst.EOF
For Each fld In rst.Fields
strText = strText & fld.Value & strDelimeter
Next

'get rid of the extra delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine
rst.MoveNext
Loop

'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1
End Sub
I have tried to create a specification also but it doesnt look like I want it to (the data is used by a web program and must be exact) can I do this in code only? I know there is some formatting above but with a loop required for the incrementing of the line number and an unfamilarity with formatting Im stuggling a little.

Many thanks for any help / ideas in advance

Donna
 
Attached is a sample database to do what you need to do.
It may not be the best solution, but it is one of the solutions.

Peace out.

EZ
 

Attachments

The only thing you arfe missing seems to be the row number.

I have re-jigged your code to make it more manageable

Code:
Dim rst As DAO.Recordset
Dim nIndex As Integer
Dim strText As String

'Set the increment to base value of 1
nIndex = 1

Set rst = CurrentDb.OpenRecordset("select * from tblData")

Open "S:\Telemark\PROJECTS\ACTIVE_PROJECTS\exportfile.t xt" For Output As #1

'Create the column headings string 
For Each fld In rst.Fields
    strText = strText & strDelimiter & fld.Name
Next fld

strText = "Row" & strText
Print #1, strText

'Reset the strText

strText = ""

Do While Not rst.EOF 
   For Each fld In rst.Fields
       strText = strText & strDelimiter & Nz(fld.Value,"NULL")
   Next

 
   'prefix the rown index number to the string
   strText = nIndex & strText   'Write line to text file
   Print #1, strText

   'Reset strText
   strText = ""
   'Increment nIndex by 1
   nIndex = nIndex + 1
   
   rst.MoveNext
Loop

Close #1
rst.close
Set rst = Nothing

End Sub

You may notice I have moved the delimiter to a prefix instead of a suffix as we need to prefix the data with the row number. This save having to insert the delimiter and then drop the trailing delimiter.


David
 
Sorry for the delay in the reply guys!

Ezfriend, thanks for your reply, I couldnt manage to open the file on my PC though? :confused:
-----------------------------------------------------------------------
DCrake, that worked perfectly for incrementing numbers, thank you very much :)

I guess what I need to do now is get the formatting down 100%... I am working on that at present though
 

Users who are viewing this thread

Back
Top Bottom