Description: Infoworks DataFoundry supports extracting data from an Excel file using Generic JDBC Source.
a) Go to Admin>Sources>Add New Source and select Excel under Generic JDBC Source Type.
b) Provide Source Name, Target Schema, and Target File System Location values.
c) Go to Data Catalog > Select the Source that you have created and click on the Ingest button.
d) Provide the JDBC URL in the below format if your excel file is present in the Amazon s3 location.
Sample JDBC URL format For Amazon S3:
jdbc:excel:ExcelFile="";URI=s3://s3-export-test/filename.xlsx; AWSAccessKey=accesskey; AWSSecretKey=secretkey;AWSRegion=northernvirginia;
For URL formats to connect to other Storage Sources, refer to the below link.
http://cdn.cdata.com/help/RXF/jdbc/pg_connectingtostoragesources.htm
e) Provide the Username/Password as user/pass. IWX DataFoundry does not validate it for an Excel source.
f) Click Save & Test Connection.
g) Once the Test Connection Job is Successful, click on Save & Crawl Metadata.
h) The Metadata will be fetched for each individual sheet in the Excel file as separate tables.
Screen show showing the individual sheets in the Excel file.
i) You could verify the Metadata Fetched by clicking on the Details button right next to the table and modify the data type if you want.
i) Click on the Configure Tables tab, select the Tables, provide the other configuration details like Ingest Type, Natural Keys, etc and then ingest the tables.
Applicable Infoworks DataFoundry Versions:
v4.2