Import an Excel Spreadsheet into a Database File

[Support_Ron]

The following project is an example of reading an Excel spreadsheet and importing the data to a Database file.:

Code: Select all

<project name="Excel to DB" mainModule="Main" version="1.0">

	<module name="Main">

		<readExcel inputFile="/gademo/Demo.xls" outputRowSetVariable="exceldata" sheetNames="Sheet 1">
			<data>
				<column index="1" name="ID Number" type="DECIMAL" size="9" />
				<column index="2" name="Last Name" type="CHAR" size="15" />
				<column index="3" name="First Name" type="CHAR" size="10" />
				<column index="4" name="Address 1" type="CHAR" size="15" />
				<column index="5" name="Address 2" type="CHAR" size="15" />
				<column index="6" name="Address 3" type="CHAR" size="15" />
				<column index="7" name="Country" type="CHAR" size="15" />
				<column index="8" name="City" type="CHAR" size="15" />
				<column index="9" name="State" type="CHAR" size="2" />
				<column index="10" name="Zip Code" type="CHAR" size="9" />
				<column index="11" name="Gender" type="CHAR" size="1" />
				<column index="12" name="Hobby" type="CHAR" size="50" />
				<column index="13" name="Age" type="DECIMAL" size="3" />
				<column index="14" name="Height" type="CHAR" size="4" />
				<column index="15" name="Weight" type="DECIMAL" size="3" />
				<column index="16" name="Hair Color" type="CHAR" size="4" />
				<column index="17" name="Area Code" type="DECIMAL" size="3" />
				<column index="18" name="Phone Number" type="CHAR" size="8" />
			</data>
		</readExcel>


		<sql resourceId="Dev 54" autoCommit="false">
			<query label="Clear Table">
				<statement>Delete from gademolib.demo </statement>
			</query>
			<query label="Insert" inputRowSetVariable="${exceldata}">
				<statement>insert into gademolib.demo values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)</statement>
			</query>
		</sql>

	</module>

</project>

In the above project, we defined all the columns in the Excel Read Task. You do not need to define the columns. One reason to define the columns is if the columns in the spreadsheet are in a different order than the fields in the file.