How do I use XmlToDatabase to export web form data to a text file as comma separated values (CSV)?


#1

Here is the example of the transactions.xml file used (located in [DRIVE]:\Inetpub\wwwroot\wfgen\WfApps\WebServices\xmlToDatabase\App_Data):

<transaction name="EXPORT2TEXT">
    <databases>
        <database name="EXPORT2TEXT" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Export2Text\;Extended Properties='text;HDR=No;FMT=Delimited';" provider="System.Data.OleDb">
            <command type="INSERT" loop="NO" xpath="/NewDataSet/Table1/">INSERT INTO [CBRE_EXPORT2TEXT]  
             (  
             [Field1]  
             ,[Field2]  
             ,[Field3]  
             ,[Field4]  
             )  
             VALUES  
             (  
             '{PARAM:MyParamAsText1}'  
             ,{PARAM:MyParamAsNumeric2}  
             ,3  
             ,'{PARAM:MyDateTimeAsMacro1}'  
             )
            </command>
        </database>
    </databases>
</transaction>

You will require a schema.ini file within the C:\Export2Text\ folder.

[EXPORT2TEXT.txt]   
Format=Delimited(,)   
CharacterSet=ANSI   
ColNameHeader=False   
Col1=Field1 Char   
Col2=Field2 Integer Width 40  
Col3=Field3 Integer Width 40  
Col4=Field4 Date Width 15  
comment=my comment

If you don’t need a header row (as in this example), set the parameter HDR=No in the connection string. Also, be sure to set Read and Write/Modify permissions on the folder which contains the EXPORT2TEXT.txt file.

Reference: http://www.connectionstrings.com/?carrier=textfile