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

Export Query or Table to text file (csv or other delimiter)

MMcCarthy
Expert Mod 10K+
P: 14,534
This is a module that exports information from a Query or a Table to comma separated values in a text format, or using other symbol! It is very helpful for sharing information between the applications!

The function that does this work is: create_file_from_SQL_SEP(SQL, File_name, sep) As Integer

Parameters:

SQL: You can pass a SQL expression, name of a table, or name of a Query that you want to export in csv
File_name: You specify the file name and path of the file where should be saved the information! Take care! If the file already exists, it would be overwritten! If it doesn’t exist it will be created! At least the specified path should exist, if it doesn’t an error occurs!
Sep: Here you need to specify the delimiter between the fields. As adelimiter you can use more than one symbol! As long as it is in function with the reading capacities of your destination application that will read the respective file.

The study of this module can teach you:

  • Creating functions in Visual Basic with arguments
  • Declaring variables
  • Opening files under Visual Basic for writing
  • Getting free file number and using it
  • Opening recordsets into a database
  • Accessing all fields into a recordset and their values
  • Saving the accessed information into File
Expand|Select|Wrap|Line Numbers
  1. '---------------------------------------------------------------------------------------
  2. ' This code is written by Vladimir Dimitrov
  3. ' It is not to be altered or distributed,
  4. ' except as part of an application.
  5. ' You are free to use it in any application,
  6. ' provided the copyright notice is left unchanged.
  7. '
  8. ' Code Courtesy of Vladimir Dimitrov
  9. '
  10. '-------------------------------------------------------------------------
  11. Function create_file_from_SQL_SEP(SQL, File_name, sep) As Integer
  12. '?create_file_from_SQL_SEP("Jo02", "D:\AJo.EXP", "; ")
  13. On Error Resume Next
  14. Dim line
  15. Dim mydb As Database
  16. Dim myr As Recordset
  17. Dim i
  18. Dim frfile
  19. Dim myupd_last, myupd_current
  20.  
  21.     Set mydb = CurrentDb()
  22.     Set myr = mydb.OpenRecordset(SQL)
  23.  
  24.     frfile = FreeFile()
  25.  
  26.     Open File_name For Output As #frfile
  27.     myr.MoveLast
  28.     myupd_last = myr.RecordCount
  29.     myr.MoveFirst
  30.     For myupd_current = 1 To myupd_last
  31.             line = ""
  32.         For i = 0 To myr.fields.Count - 1
  33.            If i = 0 Then
  34.                line = ns(myr(i))
  35.            Else
  36.                line = line + sep + ns(myr(i))
  37.            End If
  38.         Next i
  39.         Print #frfile, line
  40.         myr.MoveNext
  41.     Next myupd_current
  42.  
  43.     myr.Close
  44.     Close #frfile
  45.  
  46.     mydb.Close
  47.     create_file_from_SQL_SEP = True
  48.  
  49. End Function
  50. '-------------------------------------------------------------------------
  51. Function ns(Stri) As String
  52. On Error Resume Next
  53. Dim result As String
  54.  
  55.     If IsNull(Stri) Then
  56.        result = ""
  57.     Else
  58.        result = Stri
  59.     End If
  60.     ns = result
  61.  
  62. End Function
  63. '----------------------------------------------------------------------
  64.  
Jan 8 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.