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

Exporting Tables/Queries into csv files

P: 17
I am working with large text files.
I have a request to break up 1 large text file into indiviudal files.

My data example is, i have more fields, but in hopes of keeping it simple this is what i have.
CustID, CustName.

The large file currently has over 100 different CustID's. The request is to create a csv file at each change in the custID and to name the file "CustName".csv

i was able to create VBA code to export to CSV at the click of a button, however it exports the whole table or the whole query, how could i create it creates a file per?

export code...
Private Sub Command19_Click()
DoCmd.TransferText acExportDelim, , "qryMiffcoIncoming", "c:\my documents\\test_csv.csv"
'Shell "excel.exe C:\test_csv.xls ", vbNormalFocus ' Opens Specified file
End Sub
Feb 7 '08 #1
Share this Question
Share on Google+
3 Replies


Khriskin
P: 20
It would depend on what you need the program to do. My first inclination would be to suggest getting a list of the unique IDs (by query) and then looping through them (using a recordset). On each loop you could set the export query to filter by the current ID and kick out a csv file.

However I see that you are opening the file right after you create it, did you want to do this for every file created?

Let me know if you would like examples in code as opposed to generalities! ^_^
Feb 7 '08 #2

P: 17
It would depend on what you need the program to do. My first inclination would be to suggest getting a list of the unique IDs (by query) and then looping through them (using a recordset). On each loop you could set the export query to filter by the current ID and kick out a csv file.

However I see that you are opening the file right after you create it, did you want to do this for every file created?

Let me know if you would like examples in code as opposed to generalities! ^_^
I would love some code examples. Opening the file at the end was just for me to check the output. Once it is coded it should run and no need to open.
Feb 12 '08 #3

Khriskin
P: 20
Okay , here's a rough outline for you. It should give you general idea of how I was thinking of handling the problem, but please keep in mind I'm by no means an expert. ^_^;; <-(sheepish look) ((baa!))

1. I'm assuming you're importing the original text file into Access as a table.

2. I'm naming this table [tblData]

2a. I'm assuming [tblData] has two fields (in this mockup), [CustID] and [CustName]

2b. I'm assuming that [CustID] is a Long (autonumber) and [CustName] is a string.

2c. I'm assuming the [CustID] is unique and that [CustName] may have duplicates. (ie. two John Smith's)

3. I'm creating a throw-away query called [qrySQL]

So with these assumptions:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ExportFile()
  2. Dim rsCustID As DAO.Recordset
  3. Dim strSQL As String
  4. Dim strFileName As String
  5.  
  6. 'First we need to get a list of the unique CustIDs
  7. strSQL = "SELECT DISTINCT tblData.CustID"
  8. strSQL = strSQL & " FROM tblData"
  9. strSQL = strSQL & " ORDER BY tblData.CustID;"
  10.  
  11. 'Open a copy of this query as a recordset
  12. Set rsCustID = CurrentDb.OpenRecordset(strSQL)
  13.  
  14. 'I always assume an empty recordset is possible, so...
  15. If Not ((rsCustID.EOF) And (rsCustID.BOF)) Then
  16. rsCustID.MoveFirst
  17.  
  18. 'Loop until you hit the end of the query/recordset
  19. Do While Not (rsCustID.EOF)
  20.  
  21. 'First we need to filter the table to the current CustID
  22. strSQL = "SELECT tblData.CustID, tblData.CustName"
  23. strSQL = strSQL & " FROM tblData"
  24. strSQL = strSQL & " WHERE (((tblData.CustID)=" & rsCustID!CustID & "));"
  25. CurrentDb.QueryDefs("qrySQL").SQL = strSQL
  26.  
  27. 'Since we pulled the unique CustID from the table, it should be safe to
  28. '  assume that there will be data to export
  29. strFileName = "c:\my documents\" & rsCustID!CustName & ".csv"
  30. DoCmd.TransferText acExportDelim, , "qrySQL", strFileName
  31.  
  32. rsCustID.MoveNext
  33. Loop
  34.  
  35. MsgBox "Export Complete."
  36. End If
  37.  
  38. End Sub
  39.  
  40.  
Now obviously there isn't as much error trapping in this as there should be, but that should give you a rough idea of what I was thinking. ^_^
Feb 12 '08 #4

Post your reply

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