Welcome! Log In Create A New Profile

Advanced

Importing Data from Excel (XLS) file to HF

Posted by DarrenF 
DarrenF
Importing Data from Excel (XLS) file to HF
February 26, 2009 04:41PM
All,

An example of importing data from an Excel file to a HF file - this example imports the ISO Countries data (Countries data sourced from the ISO standards web site).

This was originally posted on the main WD forum, but thought it may be useful here as well:

IDXLSFile is int
iNo_rows is int
glocaliIResult is int

// Open the Currencies excel file
IDXLSFile = xlsOpen("c:\My Projects\InterPoint\Extract Files for Import\currencies.xls")

// If the Excel file was found, then carry on processing
// ELSE
// Report an error
IF IDXLSFile <> -1 THEN
	
	// Get number of rows in the Excel file
	iNo_rows = xlsNbRow(IDXLSFile)
	
	// Test for existence of Country table
	IF NOT HOpen(Country,hOReadWrite) THEN
		Error("Unable to locate the required Country database table!")
		RETURN
	END
		
	// HCreation will delete and re-create these tables and also reset all the rec no's and auto IDs
	HCreation(Country)
		
	// Process each row of the Excel file
	// Start at row 2 to ignore the Excel title row...
	FOR glocaliNV_Row = 2 TO iNo_rows
		
		// Country Details
		Country.cCountryCode = xlsData(IDXLSFile,glocaliNV_Row,1)
		Country.cCountryName = xlsData(IDXLSFile,glocaliNV_Row,5)
		Country.cCurrencyCode = xlsData(IDXLSFile,glocaliNV_Row,2)
		Country.nCurrencyCode = xlsData(IDXLSFile,glocaliNV_Row,3)
		Country.cCurrencyDesc = xlsData(IDXLSFile,glocaliNV_Row,4)
		Country.bActiveCountry = xlsData(IDXLSFile,glocaliNV_Row,6)
		Country.bActiveCurrency = xlsData(IDXLSFile,glocaliNV_Row,7)
		Country.cCurrencySymbol = ""
		Country.dtCreated = DateSys + TimeSys
		Country.cNotes = "Auto loaded from ISO import file on " + DateToString(DateSys())
		
		// Write the data to the database
		HAdd(Country)
						
	END
	
	// Close XLS file
	xlsClose(IDXLSFile)
		
ELSE
	Info(xlsMsgError(IDXLSFile))	

END
Grand77
Re: Importing Data from Excel (XLS) file to HF
November 24, 2009 11:37PM
Thanx a lot
Allard
Re: Importing Data from Excel (XLS) file to HF
February 09, 2011 03:14PM
Hi

Does excel need to be installed on the users computer to use this method

Thanks for the code!!

Allard
edmundo
Re: Importing Data from Excel (XLS) file to HF
October 02, 2013 07:51PM
gracias por tu aporte me dio mucha ayuda
Author:

Your Email:


Subject:


Spam prevention:
Please, enter the code that you see below in the input field. This is for blocking bots that try to post this form automatically. If the code is hard to read, then just try to guess it right. If you enter the wrong code, a new image is created and you get another chance to enter it right.
Message: