Export query to CSV & export specification (1 Viewer)

shenty · Mar 11, 2009

Ok i got a problem and i can't work it out - so i fall back to you guys again.

I want to export some information from a query to a csv file. This is the code that creates my csv file succesfully.

Code:

    Dim AString As String
    AString = "Movements_"
    DoCmd.TransferText acExportDelim, "BCMS Movement Export Specification", "qryExport", "C:\" & AString & Format(DATE, "YYYY_MMDD") & Format(Time, "-HH_MM") & ".csv"
    MsgBox "File created in C:\"

I have created the export specification.

But i want a header to the csv file. I also want each line (record) to have about 20 commas at the end. I also want other information in the exported csv that is not in the query and not all the information in the query in the csv file !

According to the bcms.gov.uk website there is a particular file structure they require to be able to upload files. This is:-

mymovements,H,BEM,7,,,,,,,,,,
mymovements,D,1,ET,UK123456704321,2,05/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,2,ET,UK123456704322,3,07/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,3,ET,UK123456704323,72,05/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,4,ET,UK123456704324,73,05/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,5,ET,UK123456704325,3,05/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,6,ET,UK123456704325,2,07/09/2001,,,,,,,,,,,,,,,,,,,,
mymovements,D,7,ET,UK123456704325,3,08/09/2001,,,,,,,,,,,,,,,,,,,,

I am waiting for the BCMS to clarify each bit but i know mymovements is the filename, the long UK number is in my query (the animal ID), the next number is the movement type which i can work out and then the date followed by all the commas.

I am not yet sure what the ,D, is and i guess the ,%,ET is just the line number which i can get from the my listbox.

Could anyone help me on this ?.

Is it worth adding an expression to my query that simply 'builds' the line to export for each item selected in my listbox. And then using the text file specification just choosing that field ?

Thanks in advance.

Updated:
This is the file as exported at the moment !

231,UK 132037 200007,Caitlin (wonky horn),F,SH
229,UK 132037 300008,Carly (black),F,SH
126,UK 162574 100334,P15 Poppet,F,Lim X
178,UK 162574 100453,,F,BB X
179,UK 162574 700459,,M,Lim X
180,UK 162574 100460,,M,BB X
181,UK 162579 701253,,F,Sim X
182,UK 162579 101254,,F,Sim X
183,UK 162579 201255,,F,BB X

As you can see i have a fields here i don't want in the export and i need to remove the spaces in the UK number etc.

gemma-the-husky · Mar 11, 2009

1. to add extra commas, you just need to add a load of extra columns to your query
2. there is an option on the export spec to let you add the column headers as the fiirst line of the csv (or maybe its a switch in the transfertext command)
3. if you want to hide some columns in the query, remove them from the query!

eg add a column at the begining of the query
"mymovements"

it will probably show as expr1: "mymovements"

it will now show the expression "mymovements" at the start of each row,

Just keep tweaking the query until the columns match

eg to remove the embedded spaces just in the column definition put
ukname: replace([ukfield]," ","")

this will strip the spaces

--------
if you are using this query for somethnig else, copy it, and then play with the new copy to develop the working version for this purpose!

AndyV · Mar 11, 2009

Does the output have to be a csv file? I have a lump of code that sreates .txt files and .sql files. If the first row needs to be some sort of control line, this could make the task easier. Do you have to do anything like have a #count control row at the end of the file? I will stick the code up here if you feel it might be useful to you. Basically it creates the file you want, adds the first row, then you just loop through your query adding the columns/rows you want in the output file, add the last row if you need one.

gemma-the-husky · Mar 11, 2009

no

open the query then do file, export, all

but when it prompts you for the save location, at the bootom - there is an advanced button

click that, and design your export spec to suit

save it, and its resuable, without repeating the exercise

boblarson · Mar 11, 2009

gemma-the-husky said:
no

open the query then do file, export, all

but when it prompts you for the save location, at the bootom - there is an advanced button

click that, and design your export spec to suit

save it, and its resuable, without repeating the exercise

Here's a VISUAL on what Gemma just said:
http://www.btabdevelopment.com/main...teanExportSpecification/tabid/64/Default.aspx

AndyV · Mar 11, 2009

Gemma, what about the first row that he needs. It might not be the column headings, most of the files we receive for importing into other applications do not contain the column headings, because they are predefined. The first row in most of our files contain the date, number of rows in the file etc. etc. That is why I suggested the method I suggested.

AndyV · Mar 11, 2009

My method also creates csv files (I have just checked), and you can insert whatever you need in the first row of data, and ad a hash total row at the end if needed.

gemma-the-husky · Mar 11, 2009

you can either about the column headings from the query as the first row of the text file, or not - whichever you choose

its just easier to use inbuilt facilities than generate a file by writing print statements - but if you prefer to do it that way, then do so. We thought you werent aware of what you could do automatically

shenty · Mar 12, 2009

Thanks guys - i've only had a brief read through but i'll put into action some suggestions tomorrow when i'm back on it.

Much appreciated, i'll come back to you all on my findings.

shenty · Mar 12, 2009

AndyV said:
Does the output have to be a csv file? I have a lump of code that sreates .txt files and .sql files. If the first row needs to be some sort of control line, this could make the task easier. Do you have to do anything like have a #count control row at the end of the file? I will stick the code up here if you feel it might be useful to you. Basically it creates the file you want, adds the first row, then you just loop through your query adding the columns/rows you want in the output file, add the last row if you need one.

AndyV - that code may well be useful, would you mind. The first line IS a header and not column headings. The output file DOES have to be a CSV.

To both gemma-the-husky & boblarson....i have created the export spec already, using the method you mentioned, however i couldn't seem to be able to tell it which columns to ignore and you don't get the option to include a header line, or am i not understanding you correctly.

Love the 'replace' command - thats a new one on me and would have saved me quite a lot of coding earlier on in this database but there you go.

I have just received a 45 page document from the BCMS on how to structure the csv files in a format they require for uploading so i will need to read up.

The form i am creating has 2 listboxes, the one on the left show all animals on the farm. The one on the right is a list of selected animals i want to move off. I have < > buttons between to add & remove animals. The aim is to create and exported csv containing all the records in the right hand listbox. A minor problem i have is that i need to select all the records in the right hand box because i am using this code:

PHP:

                        Dim i As Variant
                        Dim dbs As DAO.Database
                        Dim rst As DAO.Recordset
                        Dim qd As DAO.QueryDef
                        Set dbs = CurrentDb
                        Set rst = dbs.OpenRecordset("AnimalRegister")
                        For Each i In Me.listMoveOff.ItemsSelected
                            rst.FindFirst ("AR_ID = " & Me.listMoveOff.ItemData(i))
                            rst.Edit
                            rst!Temp = False
                            rst![Movement Date] = Me.txtMovedOff
                            rst![Moved To] = Me.txtMovedTo
                            rst![Reason for Movement] = Me.txtReason
                            rst![On Farm] = False
                            rst.Update
                            rst.MoveNext
                        Next i

Is it possible to do the same without having to actually select these items, as placing them into the right hand listbox forms my selection anyway.

Would i be better to create an array of all the records in the the listbox and then use them in the for next loop ?

AndyV · Mar 12, 2009

This is what I have done in the past:

Function Create_CSV()

Dim rSt As ADODB.Recordset, strFile As String
Set rSt = New ADODB.Recordset

rSt.Open "xxx_column_types", CurrentProject.AccessConnection

strDate = Format(Now(), "YYYYMMDD")

strFile = "c:\temp\zzz" & strDate & ".csv"

Open strFile For Output As #1

'Write the initial create table line
strScript = "create first line containing the data that you want"

Print #1, strScript

'Loop through the recordset rows getting the column data you need
Do Until rSt.EOF
strScript = rSt(0) & ","
strScript = strScript & rSt(1) & ","
Print #1, strScript
rSt.MoveNext
Loop

'Add a control footer if you want to
strScript = "add the footer detail here"
Print #1, strScript

Close #1

End Function

This just creates the file called c:\temp\zzz[YYYYMMDD].csv, inserts the header that you want, loops through a recordset, and then adds the footer if you want one. Job done!

AndyV · Mar 12, 2009

By the way, you need to set a reference to Microsoft Scripting Runtime in your Access VBA references.

shenty · Mar 12, 2009

Brilliant Andyv - that is just what i was looking for. I can build a string up for the header now and select just certain fields from the query.

It is identical to how i used to write to files back in the early dos days and came flooding back.

Just one thing - how do i get it to warn if the file already exists and whether to overwrite it.

Thanks to all for looking into this.

AndyV · Mar 12, 2009

Do all of your export files have to be saved with the same name, if not you could always add a date and time string on the end of the file name, that way you would also know exactly when the file was produced. You could save it to another folder and use a file copy routine. I produce a few files like this, and if the name always has to be the same, you could run a routing at the beginning of the function to rename the old file, so you always retain a history.

shenty · Mar 12, 2009

The filenames can be anything so i might just add the time to the filestring - that way i will have a history.

Thanks again mate that was spot on - now i'm off home to build the code that creates it all.

Export query to CSV & export specification (1 Viewer)

shenty

Registered User.

gemma-the-husky

Super Moderator

AndyV

Registered User.

gemma-the-husky

Super Moderator

boblarson

Smeghead

AndyV

Registered User.

AndyV

Registered User.

gemma-the-husky

Super Moderator

shenty

Registered User.

shenty

Registered User.

AndyV

Registered User.

AndyV

Registered User.

shenty

Registered User.

AndyV

Registered User.

shenty

Registered User.

Similar threads

Users who are viewing this thread