Export

john

I need to automatically export only certain fields in a business object to a csv file on a shared location so it can be imported into another system. I used the code

If EXISTS Temp WHERE (Temp.Accepted IS UNDEFINED) Then
FIND 'Payroll List Temps (PAYE)'
EXPORT Temp TO 'C:/Shared/NewTemp.csv'
Else
DISPLAY MESSAGE 'There Are Currently No New Temps To Export'

In the hope that providing a record hadn't been updated (hence the message) a csv file would be created which matched the criteria set by the 'Payroll List Temps (PAYE)' query, but I was wrong on this as it exports the whole record.

I have had the idea of creating a temporary business object to carry only the fields that I require then exporting this instead. Am I right in thinking this is the only way of doing this?

Thanks
John

aware_support2

Hi John,

To specify the attributes to be exported you can use a document template with a single tag in it:

<<LIST(...)>>

The LIST function allows you to specify the query, the list of desired attributes, and the separator to use between attributes (for example, a comma). Please refer to the Aware IM User Guide for more details on function LIST.

You can then create the export file like this:

EXPORT DOCUMENT PayrollList TO FILE 'C:/Shared/NewTemp.csv'

john

A ha that looks good, I started to wonder as I started to do this how my iead was going to work.

I will have a look at that

Thanks again
John

john

Hmm slight problem with that but I am sure you'll have the answers. I have got it working. My code reads:

If EXISTS Temp WHERE (Temp.Accepted IS UNDEFINED) Then
FIND 'Payroll List Temps (PAYE)'
EXPORT DOCUMENT PAYETextDoc TO FILE 'C:/Shared/NewTemp.csv'
Else
DISPLAY MESSAGE 'There Are Currently No New Temps To Export'

(thanks for that)

However any non manditory fields (left as blank spaces) seem to be missing from my csv file and consequently will not fit into our other program. It all works fine when I export from a query as this exports precisely what is on the screen. We wanted to make the process automatic however because we want the file to be exported before the user has the opportunity to ammend the record.

Do you have any suggestions our will I need to re-think how I am going to do this?

Thanks again
John

john

Just a quick point to note from my last post, we can't really fill in the missing fields in the records with a seporator as this will not sit right (and probably not be validated correctly) in our other program

Thanks
John

aware_support2

Can you give an example of a single record as exported from query on screen and by the document?

john

I wasn't sure if you wanted me to email this as a file or not. Basically I have a Business Object called 'Temp' which has a lot of fields. Some of the address fields are non manditory. Even though I have included them in the text file they do not populate when they are not filled in

If you need me to email you anything just let me know

Thanks
John

aware_support2

You can post a portion of a record when exported using each of the two methods so the difference is clearly visible. For example, one record has fewer commas for empty values while the other has commas even if the values are empty.

john

The results from my query where I chose the visible fields are:

NMC15,Jonathan,Hunter,4 Commonwealth Close,,,Winsford,Cheshire,Cw7 1PT,01606554538,21/03/1981,0,1,jk889988a,20/10/06,Barclays Bank Plc,21 Dingle Walk,,Winsford,Cheshire,Mr J Hunter,90876543,897890,,001,NMC,1

(spaces left for NULL values)

My text template reads:

<<List (Temp,',','TempID','Name','Surname','Street','Address2','Address3','Town','County','PostCode','PhoneNumber','DOB','SexNumber','MaritalNumber','NINumber','StartDate','BankName','BankStreet','BankAddress2','BankTown','BankCounty','AccountName','AccountNumber','SortCode','RollNumber','AgencyCode','AgencyID','PaymentTypeNumber')>>

and the result from my template reads:

NMC15,Jonathan,Hunter,4 Commonwealth Close,Winsford,Cheshire,Cw7 1PT,01606554538,21/03/1981,0,1,jk889988a,20/10/06,Barclays Bank Plc,21 Dingle Walk,Mr J Hunter,90876543,897890,001,NMC,1

I hope that makes sense. I think you can see the gaps after the Street "4 Commonwealth Close" have been omitted from the second result. This preceeds "Town" which is "Winsford.

Thanks so much for taking the time to look at this
John

aware_support2

I see. We are going to improve either of the two methods in the near future to allow exporting selected attributes while preserving the separators between empty values.

john

Has there been any improvements made to the export problem I had (above) with the release of recent versions?

I believe my build number at the time of the problem was 922.

Many Thanks
John

aware_support

Hi John,

sorry, not yet.

Best Regards