Import an Excel Spreadsheet into a Database File
Looking for an example project to get you started?
1 post
Page 1 of 1
- Support Specialist
- Posts: 10
- Joined: Tue Mar 03, 2009 5:45 pm
The following project is an example of reading an Excel spreadsheet and importing the data to a Database file.:
Code: Select all
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.<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>
1 post
Page 1 of 1