Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2012
    Posts
    45
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Batch conversion CSV to XML (mapped to specific fields)

    Hello

    I have tried to figure this out, but I am not finding it easy. I have a lot of data in CSV format, it's a database of names, organisations addresses etc.

    All the data is in it's own column in Excel

    I have 4000 records but can only upload it in batches of 500 in XML format

    Here is an example of the 3 records I have hand-typed into xml (test info only):

    Code:
    <?xml version="1.0" encoding="utf-16" ?> 
    <BATCH xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:Test">
    <HEADER>
    <COUNT>3</COUNT> 
    <ORIGINATOR>Test Company</ORIGINATOR> 
    </HEADER>
    
    <SUBMISSIONS>
    
    <SUBMISSION>
    <SUB_HEADER>
    <NOTES>
    <NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    </NOTES>
    </SUB_HEADER>
    <IDENTIFIER>A9999999</IDENTIFIER> 
    <PRODUCT>ORGANISATIONREF</PRODUCT> 
    <CLASSIFICATION>REFER</CLASSIFICATION> 
    <DATE>2013-01-01</DATE> 
    <CLIENT_REF>A20090474</CLIENT_REF> 
    <SPECIFIC_CLASSIFICATION>TEST</SPECIFIC_CLASSIFICATION> 
    <ORG_REF>
    <STATUS>SUSPECT</STATUS> 
    <ORGANISATION_NAME>Test name</ORGANISATION_NAME> 
    <CONTACT_NAME>MR Sample Test</CONTACT_NAME> 
    <ORG_REF_CON_PER>
    <FIRST_NAME>Sample</FIRST_NAME> 
    <SURNAME>Test</SURNAME> 
    <DOB>1900-01-01</DOB> 
    </ORG_REF_CON_PER>
    <ORG_REF_ADD>
    <STATUS>TEST</STATUS> 
    <FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    </ORG_REF_ADD>
    </ORG_REF>
    </SUBMISSION>
    <SUBMISSION>
    
    <SUBMISSION>
    <SUB_HEADER>
    <NOTES>
    <NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    </NOTES>
    </SUB_HEADER>
    <IDENTIFIER>A9999999</IDENTIFIER> 
    <PRODUCT>ORGANISATIONREF</PRODUCT> 
    <CLASSIFICATION>TEST</CLASSIFICATION> 
    <DATE>2013-04-10</DATE> 
    <CLIENT_REF>A20090474</CLIENT_REF> 
    <SPECIFIC_CLASSIFICATION>SUSPECTED</SPECIFIC_CLASSIFICATION> 
    <ORG_REF>
    <STATUS>SUSPECT</STATUS> 
    <ORGANISATION_NAME>Test name2</ORGANISATION_NAME> 
    <CONTACT_NAME>MR Sample Test2</CONTACT_NAME> 
    <ORG_REF_CON_PER>
    <FIRST_NAME>Sample</FIRST_NAME> 
    <SURNAME>Test2</SURNAME> 
    <DOB>1900-01-01</DOB> 
    </ORG_REF_CON_PER>
    <ORG_REF_ADD>
    <STATUS>TEST</STATUS> 
    <FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    </ORG_REF_ADD>
    </ORG_REF>
    </SUBMISSION>
    <SUBMISSION>
    
    <SUBMISSION>
    <SUB_HEADER>
    <NOTES>
    <NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    </NOTES>
    </SUB_HEADER>
    <IDENTIFIER>A9999999</IDENTIFIER> 
    <PRODUCT>ORGANISATIONREF</PRODUCT> 
    <CLASSIFICATION>REFER</CLASSIFICATION> 
    <DATE>2013-04-10</DATE> 
    <CLIENT_REF>A20090474</CLIENT_REF> 
    <SPECIFIC_CLASSIFICATION>TEST</SPECIFIC_CLASSIFICATION> 
    <ORG_REF>
    <STATUS>SUSPECT</STATUS> 
    <ORGANISATION_NAME>Test name3</ORGANISATION_NAME> 
    <CONTACT_NAME>MR Sample Test3</CONTACT_NAME> 
    <ORG_REF_CON_PER>
    <FIRST_NAME>Sample</FIRST_NAME> 
    <SURNAME>Test3</SURNAME> 
    <DOB>1900-01-01</DOB> 
    </ORG_REF_CON_PER>
    <ORG_REF_ADD>
    <STATUS>TEST</STATUS> 
    <FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    </ORG_REF_ADD>
    </ORG_REF>
    </SUBMISSION>
    <SUBMISSION>
    
    </SUBMISSIONS>
    </BATCH>
    I need to do a batch conversion, I know you can make XML maps, but what would one of these look like. I tried to just use the XML in this example as a map but excel did not like it

    Any thoughts on this?

    Thanks in advance

  • #2
    New Coder
    Join Date
    Aug 2012
    Posts
    45
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I think I need to make an XML Schema but how do I do this?

  • #3
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,763
    Thanks
    23
    Thanked 548 Times in 547 Posts
    Take a look at this site. I thought there were more free converters for this, but this is the only one I found:
    http://www.zamzar.com/convert/xls-to-xml/

    I also played with what you up-loaded. XML-Notepad showed a number of errors. This is the corrected file:
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    
    <BATCH>
    	<HEADER>
    		<COUNT>3</COUNT> 
    		<ORIGINATOR>Test Company</ORIGINATOR> 
    	</HEADER>
    
    	<SUBMISSIONS>
    
    	<SUBMISSION>
    	<SUB_HEADER>
    		<NOTES>
    			<NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    		</NOTES>
    	</SUB_HEADER>
    	<IDENTIFIER>A9999999</IDENTIFIER> 
    	<PRODUCT>ORGANISATIONREF</PRODUCT> 
    	<CLASSIFICATION>REFER</CLASSIFICATION> 
    	<DATE>2013-01-01</DATE> 
    	<CLIENT_REF>A20090474</CLIENT_REF> 
    	<SPECIFIC_CLASSIFICATION>TEST</SPECIFIC_CLASSIFICATION> 
    	<ORG_REF>
    		<STATUS>SUSPECT</STATUS> 
    		<ORGANISATION_NAME>Test name</ORGANISATION_NAME> 
    		<CONTACT_NAME>MR Sample Test</CONTACT_NAME> 
    		<ORG_REF_CON_PER>
    			<FIRST_NAME>Sample</FIRST_NAME> 
    			<SURNAME>Test</SURNAME> 
    			<DOB>1900-01-01</DOB> 
    		</ORG_REF_CON_PER>
    		<ORG_REF_ADD>
    			<STATUS>TEST</STATUS> 
    			<FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    		</ORG_REF_ADD>
    	</ORG_REF>
    	</SUBMISSION>
    
    
    	<SUBMISSION>
    	<SUB_HEADER>
    	<NOTES>
    	<NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    	</NOTES>
    	</SUB_HEADER>
    	<IDENTIFIER>A9999999</IDENTIFIER> 
    	<PRODUCT>ORGANISATIONREF</PRODUCT> 
    	<CLASSIFICATION>TEST</CLASSIFICATION> 
    	<DATE>2013-04-10</DATE> 
    	<CLIENT_REF>A20090474</CLIENT_REF> 
    	<SPECIFIC_CLASSIFICATION>SUSPECTED</SPECIFIC_CLASSIFICATION> 
    	<ORG_REF>
    	<STATUS>SUSPECT</STATUS> 
    	<ORGANISATION_NAME>Test name2</ORGANISATION_NAME> 
    	<CONTACT_NAME>MR Sample Test2</CONTACT_NAME> 
    	<ORG_REF_CON_PER>
    	<FIRST_NAME>Sample</FIRST_NAME> 
    	<SURNAME>Test2</SURNAME> 
    	<DOB>1900-01-01</DOB> 
    	</ORG_REF_CON_PER>
    	<ORG_REF_ADD>
    	<STATUS>TEST</STATUS> 
    	<FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    	</ORG_REF_ADD>
    	</ORG_REF>
    	</SUBMISSION>
    
    	<SUBMISSION>
    	<SUB_HEADER>
    	<NOTES>
    	<NOTE type="SUB" privacyLevel="PRIV">Test Notes</NOTE> 
    	</NOTES>
    	</SUB_HEADER>
    	<IDENTIFIER>A9999999</IDENTIFIER> 
    	<PRODUCT>ORGANISATIONREF</PRODUCT> 
    	<CLASSIFICATION>REFER</CLASSIFICATION> 
    	<DATE>2013-04-10</DATE> 
    	<CLIENT_REF>A20090474</CLIENT_REF> 
    	<SPECIFIC_CLASSIFICATION>TEST</SPECIFIC_CLASSIFICATION> 
    	<ORG_REF>
    	<STATUS>SUSPECT</STATUS> 
    	<ORGANISATION_NAME>Test name3</ORGANISATION_NAME> 
    	<CONTACT_NAME>MR Sample Test3</CONTACT_NAME> 
    	<ORG_REF_CON_PER>
    	<FIRST_NAME>Sample</FIRST_NAME> 
    	<SURNAME>Test3</SURNAME> 
    	<DOB>1900-01-01</DOB> 
    	</ORG_REF_CON_PER>
    	<ORG_REF_ADD>
    	<STATUS>TEST</STATUS> 
    	<FULL_ADDRESS>1 A Test Address, Test, TT1 1TT</FULL_ADDRESS> 
    	</ORG_REF_ADD>
    	</ORG_REF>
    	</SUBMISSION>
    
    	</SUBMISSIONS>
    </BATCH>
    If this helps you.
    Last edited by sunfighter; 04-19-2013 at 11:54 PM.
    Evolution - The non-random survival of random variants.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •