Importing Excel Spreadsheet Data to SQL Server 2000 Database (using ASP), with field validation levels at each record. Also, unknown record count! Anyone?
Posted on 2004-08-03
I am an ASP programmer and have adopted a ready made script to import data from an excel spreadsheet directly into a SQL server database, after certain field level validations.
The sample of a part of the script is shown below. My problem is:
1. I do not know the row count of the number of rows in the excel sheet, so apparently I have to select like roughly 500 rows in the excel sheet and name the entire range, some name, in my case called it "sheeter". My concern is if the data grows over 500 rows, what shall happen to my SQL statement which I am using to import the table range from excel, which is made of columns and rows (the matrix range). I want to dynamically grow my loop in some way to select more than the 500 if required. If I select the entire column range till the last excel row, then my PC crashes! I have to select a fix and valid range to query, which is logically absolute! Now I need help to grow this record count, till data exists. Now comes the question of empty rows : )
2. I need to validate each and every record and then directly insert the entire row using SQL statements to the database (SQL). My problem is memory and time! Any help on this then?
If anyone has some other alternative, function, procedure etc. please HELP OUT!
Points are your reward, and mine from my boss!
Thanx in advance!