UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export 1 Csv File Per Order Number., Access 2016    
 
   
old_cadger
post Nov 11 2017, 01:32 PM
Post#1



Posts: 1
Joined: 11-November 17



Hi there,

We are running Access 2016

We have a vendor that wants us to provide a single csv file for each order we place with them.

Lets say we have 3 different orders in Access.

It is easy for me to export a csv that has all orders together.

Order Number,Quantity,PartNumber,Shipto
1234,1,ACME123,Store1
1234,1,ACME321,Store1
1235,1,ACME123,Store2
1236,2,ACME123,Store3

How would can we export a csv file for each order number like below? Is this possible in Access?

Filename - 1234.csv
Order Number,Quantity,PartNumber,Shipto
1234,1,ACME123,Store1
1234,1,ACME321,Store1

Filename - 1235.csv
Order Number,Quantity,PartNumber,Shipto
1235,1,ACME123,Store2

Filename - 1236.csv
Order Number,Quantity,PartNumber,Shipto
1236,2,ACME123,Store3

Thank you for your time.
Go to the top of the page
 
RJD
post Nov 11 2017, 01:49 PM
Post#2


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Sounds like you need a VBA procedure to step through the invoices and produce a CSV file for each as you find them. This is certainly possible, in fact pretty routine, with Access.

What does the query look like now that produces the data for the invoices (SQL)? And what is its name?

If you have difficulty visualizing this, or setting it up, perhaps you could post a cut-down db with some data and we could guide you further.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
orange999
post Nov 11 2017, 01:59 PM
Post#3



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Further to Joe's questions, I think you may need more identification for the files.
For example, if you have multiple orders for store 1234 on specific day or week, you might want to put the date and possibly a sequence number to identify the file.

1234_20-Nov-2017.csv or something that makes sense in your environment

--------------------
Good luck with your project!
Go to the top of the page
 
RJD
post Nov 11 2017, 03:40 PM
Post#4


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


Hi again: Just tinkering around with your issue - and developed a small demo. Here's the code that should do what you want ...

CODE
Public Function ExportCSVFiles()

Dim rstList As DAO.Recordset
Set rstList = CurrentDb.OpenRecordset("qryOrderNumbers")
rstList.MoveFirst

Do Until rstList.EOF

Dim qdfOrderNumber As QueryDef
Set qdfOrderNumber = CurrentDb.CreateQueryDef("tempqry", "SELECT * FROM tblOrders WHERE OrderNumber ='" & rstList!OrderNumber & "'")

DoCmd.TransferText acExportDelim, "TblOrdersExport", "tempqry", "C:\temp\" & rstList!OrderNumber & ".csv"

CurrentDb.QueryDefs.Delete "tempqry"

rstList.MoveNext

Loop

End Function

Note that I created a new query using DISTINCT to get each order number, rather than repeats, and used that to step through the orders. Also note that i used the querydef approach to create limited queries to export.

Also, I assume you will create a query to limit the range of order numbers or dates, and use that instead of the table approach shown in this demo.

There are probably other ways to get at this, but this one works in my tests.

I completely agree with Orange that you should tag a date onto the file name. I didn't do that here, but it should be fairly easy to do. Thanks, Orange - a good and necessary suggestion to incorporate.

HTH
Joe
Attached File(s)
Attached File  Export1CSVFilePerOrder.zip ( 21.77K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Nov 12 2017, 06:43 PM
Post#5


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


Since you haven't responded to this, I was looking at it again - and the saved DISTINCT query is, of course, not required, but this can be accommodated in the code itself ...

CODE
Public Function ExportCSVFiles()

Dim rstList As DAO.Recordset
Set rstList = CurrentDb.OpenRecordset("SELECT DISTINCT OrderNumber FROM tblOrders")
rstList.MoveFirst

Do Until rstList.EOF

Dim qdfOrderNumber As QueryDef
Set qdfOrderNumber = CurrentDb.CreateQueryDef("tempqry", "SELECT * FROM tblOrders WHERE OrderNumber ='" & rstList!OrderNumber & "'")

DoCmd.TransferText acExportDelim, "TblOrdersExport", "tempqry", "C:\temp\" & rstList!OrderNumber & "_" & Format(Date, "yyyymmdd") & ".csv"

CurrentDb.QueryDefs.Delete "tempqry"

rstList.MoveNext

Loop

End Function

Same approach, just eliminating the extra saved query - and accommodating a date in the file name as Orange suggested.

Now you just have to correct for the table name and the target folder.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 03:54 PM