Vault add fields to ODBC export

How do I add new fields to ODBC export in Vault?  Do I need to add them to the index first?
 
My journal looks like this:
 
Coverage Effective Date = <DDSDocValue name="COV_EFF_DT_1" type="text" len="6">010114</DDSDocValue> 
 
Coverage End Date = <DDSDocValue name="COV_END_DT_1" type="text" len="6">123114</DDSDocValue> 
 
 
I want to add these two new values.
 
 
ODBC export operates on the document records in the .drd files and does not use the indexes at all.
So you don't need to add an index to export a field via ODBC.
 
I set up some test data to simulate this.
 
Export configuration:
 
[IDCARDS]
...
ExportEnable=1
ExportSource=VaultSQL
ExportUser=vaultexportuser
ExportPassword=test
ExportTable=exportdata.dbo.Documents
 
ExportField1=FileName,int.file,s
ExportField2=FileOffset,int.pointer,s
ExportField3=EntityID,doc.account,s
ExportField4=EntityName,doc.name,s
ExportField5=DocumentMnemonic,MNEMONIC,s
ExportField6=DocumentDate,doc.date,d
ExportField7=DocumentPageCount,doc.pages,n
ExportField8=ProfileName,int.profile,s
ExportField9=ResourceName,int.resource,s
ExportField10=DocumentType,doc.type,s
ExportField11=CoverageEffective,COV_EFF_DT_1,d
ExportField12=CoverageEnd,COV_END_DT_1,d
Table layout for reference:
 
CREATE TABLE [dbo].[Documents](
[FileName] [nchar](200) NULL,
[FileOffset] [nchar](32) NULL,
[EntityID] [nchar](64) NULL,
[EntityName] [nchar](128) NULL,
[DocumentMnemonic] [nchar](16) NULL,
[DocumentDate] [date] NULL,
[DocumentPageCount] [smallint] NULL,
[ProfileName] [nchar](16) NULL,
[ResourceName] [nchar](16) NULL,
[DocumentType] [nchar](16) NULL,
[CoverageEffective] [date] NULL,
[CoverageEnd] [date] NULL
) ON [PRIMARY]
Loader log:
 
08:07:57 exporting [docdata\110102-085022-idcard.drd] to [VaultSQL] table [exportdata.dbo.Documents]
         0    10   20   30   40   50   60   70   80   90   100
         |    |    |    |    |    |    |    |    |    |    |
         XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
08:07:57 exported complete, [500] added, [0] errors
08:07:57 exporting [docdata\111101-092331-idcard.drd] to [VaultSQL] table [exportdata.dbo.Documents]
         0    10   20   30   40   50   60   70   80   90   100
         |    |    |    |    |    |    |    |    |    |    |
         XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
08:07:57 exported complete, [500] added, [0] errors
08:07:57 exporting [docdata\111201-13410-idcard.drd] to [VaultSQL] table [exportdata.dbo.Documents]
         0    10   20   30   40   50   60   70   80   90   100
         |    |    |    |    |    |    |    |    |    |    |
         XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
08:07:57 exported complete, [500] added, [0] errors
Test output:
 
sqlcmd -E -S .\SQLEXPRESS -Q "SELECT TOP 10 * FROM [exportdata].[dbo].[Documents]" -o test.csv -s"," -w 700 -W
 
FileName,FileOffset,EntityID,EntityName,DocumentMnemonic,DocumentDate,DocumentPageCount,ProfileName,ResourceName,DocumentType,CoverageEffective,CoverageEnd
--------,----------,--------,----------,----------------,------------,-----------------,-----------,------------,------------,-----------------,-----------
110102-085022-idcard,000000500000167A,21538553,Neil   Manning,YFRMICTM,2002-01-11,3,IDCARDS,default,AFP,2000-06-11,2000-06-17
110102-085022-idcard,000000500000302A,09514281,Laphonso   Hassan,PQUDQIOU,2002-01-11,11,IDCARDS,default,AFP,2001-01-04,2001-01-17
110102-085022-idcard,0000005000004A62,28692236,Criag   Palmer,CUOTITQT,2002-01-11,3,IDCARDS,default,AFP,2001-07-28,2001-07-28
110102-085022-idcard,000000500000640A,08480310,Tammi   Childs,YEVHECOY,2002-01-11,3,IDCARDS,default,AFP,2002-02-04,2002-02-21
110102-085022-idcard,0000005000007DBA,21520115,Samaki   Horry,DYKXKJVT,2002-01-11,7,IDCARDS,default,AFP,2002-08-17,2002-08-19
110102-085022-idcard,000000500000979A,22701227,Paul   Herrera,WWCITVNK,2002-01-11,12,IDCARDS,default,AFP,2003-02-05,2003-02-06
110102-085022-idcard,000000500000B12A,18240748,Tariq   Barnes,PQMGDUPX,2002-01-11,7,IDCARDS,default,AFP,2003-09-12,2003-09-18
110102-085022-idcard,000000500000CB1A,09496254,Wendy   Stepanova,JUPXXSYR,2002-01-11,4,IDCARDS,default,AFP,2004-03-20,2004-03-24
110102-085022-idcard,000000500000E4FA,06410903,Alvin   Burton,TWAUYHRN,2002-01-11,11,IDCARDS,default,AFP,2004-09-18,2004-10-05
110102-085022-idcard,000000500000FF8A,12268480,Balwinder   Wallace,QLHKERNI,2002-01-11,8,IDCARDS,default,AFP,2005-04-07,2005-04-21
 
(10 rows affected)
 
if the date in the document record is not in yyyy/mm/dd format, you might need to switch to using string instead of date (in export config and table layout)
you could also run into issues if old data is present and does not have these fields, it may error out trying to export those jobs if they ever get reprocessed because of date validation (using string helps work around that as well)
 
> What would be the best way to go about adding the new fields to the ODBC export?
 
The simplest would be to add the following settings to the existing export settings:
 
profiles.ini:
[IDCARDS]
...
ExportField11=CoverageEffective,COV_EFF_DT_1,d
ExportField12=CoverageEnd,COV_END_DT_1,d
 
> Am I correct that it has to be in a Vault index first before
> it can be exported via the ODBC export?
 
No, you do not need to add a custom attribute to an index in order to use ODBC export.
 
Export does not read the indexes at all. It scans the document records in the .drd and reads the properties from there.
 
> Will it cause problems if ODBC export runs and older files don't have the new fields?
 
If you export the values as a date, yes. The way Vault exports data will cause an error if the date on the Vault side does not exist or is otherwise malformed.
 
You can export the field as a string ('s' vs 'd') in which case the missing attributes will show up as an empty string.
UPDATED:  September 25, 2017