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.
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.
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.
Last edited: