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

CSV creation from two tables

P: 6
I have two tables - Orders, Order Items - Access 2000 DB

I need to create a csv file to import this into another system

the format needs to be:

Expand|Select|Wrap|Line Numbers
  1. Order 1, Order 1 Customer name, Order 1 Address,
  2. Order item 1.1, Order Item 1.1 weight,
  3. Order item 1.2, Order Item 1.2 weight,
  4. Order 2, Order 2 Customer name, Order 2 Address,
  5. Order item 2.1, Order Item 2.1 weight,
  6. Order item 2.2, Order Item 2.2 weight,
etc...

also I need some of these fields to be text with " ". All the fields are text format in the tables.

Any help will be welcomed !

NB I am presuming that I will need to parse this somehow...
Aug 6 '08 #1
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
For illustrative purposes, if you had your database setup with separate, but related related tables between orders, order details, and customers (as shown in the Northwind sample db that comes with Access), you can extract the info you want by means of a select query simiilar to the following:
Expand|Select|Wrap|Line Numbers
  1. SELECT Orders.OrderID, [Order Details].ProductID, Customers.CustomerID, Customers.CompanyName, Customers.Address, Customers.City
  2. FROM Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID;
You can then save this query as Customer Orders and use the transfer text method to produce your CSV file by placing the following code behind a button.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportDelim, "Customer Orders", _
  2.     "Orders Report", "C:\CSVFile\AugustOrders.doc"
Aug 6 '08 #2

P: 6
This won' t give me what I want as I need the order details and order items on seperate lines in the csv file. The select query will only put it all into one line.

I think I need to do it in VB, but I do not know the code.
Take Order details -> export to csv
Take Order item details -> export to same csv
next Order
do the same...

I have done the reverse and parsed a text file into Access, I need something similar to export it..
Aug 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,492
I think you may need to process through the two recordsets in VBA.

Basic DAO recordset loop using two recordsets is a template that should, at least, start you off.
Aug 6 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
This won' t give me what I want as I need the order details and order items on seperate lines in the csv file. The select query will only put it all into one line.

I think I need to do it in VB, but I do not know the code.
Take Order details -> export to csv
Take Order item details -> export to same csv
next Order
do the same...

I have done the reverse and parsed a text file into Access, I need something similar to export it..
Ok, then try using a union query to extract the data on 2 lines (as shown), then export via transfertext method:

Expand|Select|Wrap|Line Numbers
  1. Select orderno, customer name, customer address from orders
  2. Union
  3. Select orderno, itemno, weight from order details
  4. Order By orderno
Aug 6 '08 #5

P: 6
NeoPa

Thats the kinda thing I'm looking for..

Also found this..

Expand|Select|Wrap|Line Numbers
  1. ................
  2. Set rsData = New ADODB.Recordset
  3.   With rsData
  4.     .ActiveConnection = m_cnDatabase
  5.     .CursorLocation = adUseClient
  6.     .CursorType = adOpenForwardOnly
  7.     .LockType = adLockReadOnly
  8.  
  9.     .Source = "SELECT * FROM " & rst1
  10.  
  11.     .Open
  12.  
  13.     If (.State = adStateOpen) Then
  14.       hFile = FreeFile
  15.       Open "C:\Temp\orderimport.CSV" For Output As hFile
  16.  
  17.       Do Until .EOF
  18.         sExportLine = ""
  19.         For Each oField In .Fields
  20.           sExportLine = sExportLine & oField.Value & ","
  21.         Next
  22.  
  23.         sExportLine = VBA.Left$(sExportLine, Len(sExportLine) - 1)
  24.  
  25.         Print #hFile, sExportLine
  26.  
  27.         .MoveNext
  28.       Loop
  29.     End If
  30.   End With..................
This looks like the kinda thing I need to write to a file, but it errors withType mismatch on the SELECT table name
Aug 6 '08 #6

NeoPa
Expert Mod 15k+
P: 31,492
No worries, but if you'd like me to look at this for you please let me know which line of the code the error message occurrs on.
Aug 6 '08 #7

P: 6
here is my code that works - any suggestions on tidying it up?
Or is it perfect :-) (doubt it)



Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.  
  3. 'set up error handler
  4. On Error GoTo Proc_Err
  5.  
  6. Dim pRecordsetName As String
  7. Dim pRecordsetName2 As String
  8. Dim mPathAndFile As String, mFileNumber As Integer
  9. Dim r As DAO.Recordset, mFieldNum As Integer
  10. Dim r2 As DAO.Recordset, mFieldNum2 As Integer
  11. Dim mOutputString As String
  12. Dim mFieldDeli As String
  13. Dim myTime As String
  14.  
  15. myTime = Format(Date, "ddmmyy") & "-" & Format(Time, "hhmmss")
  16. pRecordsetName = "FOPS Export Order Header"
  17. pRecordsetName2 = "Fops Export Order Lines"
  18. mPathAndFile = "C:\" & myTime & ".csv"
  19. mFieldDeli = ","
  20.  
  21. 'get a handle
  22. mFileNumber = FreeFile
  23.  
  24. 'close file handle if it is open
  25. 'ignore any error from trying to close it if it is not
  26. On Error Resume Next
  27. Close #mFileNumber
  28. On Error GoTo Proc_Err
  29.  
  30. 'open file for output
  31. Open mPathAndFile For Output As #mFileNumber
  32.  
  33. 'open the recordset
  34. Set r = CurrentDb.OpenRecordset(pRecordsetName)
  35.  
  36. 'loop through all records
  37. Do While Not r.EOF()
  38.  
  39. 'tell OS (Operating System) to pay attention to things
  40. DoEvents
  41. mOutputString = ""
  42. For mFieldNum = 0 To r.Fields.Count - 1
  43. mOutputString = mOutputString _
  44. & r.Fields(mFieldNum) _
  45. & mFieldDeli
  46.  
  47. Next mFieldNum
  48.  
  49. 'write a line to the file
  50. Print #mFileNumber, mOutputString
  51.  
  52.                 'Process Order Line
  53.                 Set r2 = CurrentDb.OpenRecordset(pRecordsetName2)
  54.                 Do While Not r2.EOF()
  55.                 If r("Order Number") = r2("Order Number") Then
  56.                     'tell OS (Operating System) to pay attention to things
  57.                     DoEvents
  58.                     mOutputString = ""
  59.                     For mFieldNum2 = 0 To r2.Fields.Count - 1
  60.                     mOutputString = mOutputString _
  61.                     & r2.Fields(mFieldNum2) _
  62.                     & mFieldDeli
  63.  
  64.                    Next mFieldNum2
  65.  
  66.                     'write a line to the file
  67.                     Print #mFileNumber, mOutputString
  68.                 End If
  69.  
  70.                 'move to next record
  71.                 r2.MoveNext
  72.  
  73.                 Loop
  74.  
  75.                 r2.Close
  76.                 Set r2 = Nothing
  77.  
  78. 'move to next record
  79. r.MoveNext
  80.  
  81. Loop
  82.  
  83. MsgBox "Done Creating " & mPathAndFile, , "Done"
  84.  
  85. Proc_Exit:
  86. On Error Resume Next
  87. 'close the file
  88. Close #mFileNumber
  89.  
  90. 'close the recordset
  91. r.Close
  92.  
  93. 'release object variables
  94. Set r = Nothing
  95.  
  96. Exit Sub
  97.  
  98. 'ERROR HANDLER
  99. Proc_Err:
  100. MsgBox Err.Description _
  101. , , "ERROR " & Err.Number _
  102. & "   ExportDelimitedText"
  103. 'press F8 to step through code and correct problem
  104. 'comment next line after debugged
  105.     Stop:   Resume
  106. Resume Proc_Exit
  107. End Sub
Aug 7 '08 #8

NeoPa
Expert Mod 15k+
P: 31,492
Nothing obviously wrong apart from the indenting.

When used properly it can be a great asset.
Otherwise it will just put everyone off from even looking I'm afraid (not to mention making your own job harder when you want to work on it again).

Anyway, the actual code seems fine.
Aug 7 '08 #9

Post your reply

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