Help Please - Excel & SQL

  • Thread starter Thread starter John Saunders
  • Start date Start date
J

John Saunders

Ok, this is what I need to accomplish.

We have a huge excel file that comes in daily. I need the server to grab
this excel file each day and import the data into a SQL database. Once
that is accomplished, I want the web app to allow the user to search any
data in the table. This is overwhelming to me.. I don't know where to
begin. The excel file has 193 fields and tons of records. Any advice on
this would help me tremendously.
 
...
We have a huge excel file that comes in daily. I need the server to grab
this excel file each day and import the data into a SQL database. Once
that is accomplished, I want the web app to allow the user to search any
data in the table. This is overwhelming to me.. I don't know where to
begin. The excel file has 193 fields and tons of records. Any advice on
this would help me tremendously.
Click to expand...

This would be impossible for me to list all of the code and stuff in one
bash to help you - so instead here are some thoughts - as we have a similar
process here at work.

You'll need to make sure the excel file is in a location that your SQL
Server can access.
You'll need to create a job that runs perhaps a DTS to import the excel file
(I'm assuming its in a .csv or could be etc?)
You'll want to schedule the job to run - perhaps at midnight each day to
bring in the next relevant file.
You'll then need to put the ASP/ASP.Net code together to allow you to search
the results...

I'd suggest that you start SMALL!

You've not said what experience you have with regards to SQL
Server/ASP/ASP.net etc, so its a bit difficult to go much further at this
stage...

Regards

Rob
 
John:



Check into creating a SQL DTS package to handle (and automate) the excel to
SQL table process. The next step is to full-text index the SQL table in
question to allow you to perform full-text querying on the data. With a
proper full-text catalog created by the full-text index, you can now use SQL
keywords such as CONTAINS and FREETEXT to search the data and return
appropriate hits. There are performance considerations when your data gets
to be more than a million rows.



Check out the SQL Server Books Online for more specific information. You may
also want to create more limited searches based on a subset of fields
contained in the table (a most used feature) for more frequent, performance
minded searches.



Hope this gives you a starting point.



John Sturgeon, MCP, MCSD

BizTech Solutions, Inc.
 
Back
Top