• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Upload Excel data to SQL Server

Client has a half dozen Excel files (same format) that they want to upload to SQL Server every month from their accounting software, so they can use some BI tool to analyze the data.

Over 1.5 million rows of data each month that must be massaged before getting it into SQL Server (some of the date fields are just month and year, and some of the numeric fields contains spaces).

I've been doing this via Access, but the entire process, loop through folder, identify each table, link that table to Access, and append records to SQL Server is taking close to 12 hours (with SQL Server transaction log disabled).

I've not used SQL Server 2008 too extensively, so I'm trying to determine whether there is a better (less time consuming) way to accomplish this.  Does SQL Server 2008 still have a DTS function which would allow me to go directly from Excel to SQL Server?  If so, would I use a stored procedure for that?  How would I set that up to process files with different names and in different folders each month?
0
Dale Fye
Asked:
Dale Fye
  • 5
  • 3
1 Solution
 
Anthony PerkinsCommented:
append records to SQL Server is taking close to 12 hours (with SQL Server transaction log
disabled).

Just a minor correction: You cannot disable the Transaction Log.  The Transaction Log is always used, all you can do is minimize its use by changing the database Recovery Model to Simple.

Does SQL Server 2008 still have a DTS function which would allow me to go directly from Excel to SQL Server?
Yes,  is is called SSIS.  It is a complex application and you will not learn it in one afternoon, but you should be able to do the basics fairly quickly.

If so, would I use a stored procedure for that?
Not necessarily, it is a totally separate application (like DTS) that can use Stored Procedures.

How would I set that up to process files with different names and in different folders each month?
You can dynamically set the folders and file names.
0
 
Dale FyeAuthor Commented:
@ac,

Doesn't look like SSIS is available as part of SQL Server Express.
0
 
Anthony PerkinsCommented:
You are right.  It is not.  Your next choice is using something like OPENROWSET() or something like that.  However, that is not going to make your life easy when it comes to using dynamic files and folders.

If it was me, and I knew MS Access well (I don't) I would right an app there to do your requirements.  If that is out of the question, consider something like VB or .NET or even VBScript to automate it.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeAuthor Commented:
@ac,

Thanks.  I've got the Access application running, it is just extremely slow.  But does run between the time they leave at night and the time they get in the next morning, so it is marginally acceptable.

I was just hoping to make it a bit more efficient by going straight from Excel to SQL Server.

Thanks.  I'm going to keep this open for a couple of days to see if anyone else has any ideas and will close/award points next week.
0
 
Anthony PerkinsCommented:
I was just hoping to make it a bit more efficient by going straight from Excel to SQL Server.
And you can still do that by using MS Access and the old fashioned ADO recordsets, just don't import the data into MS Access or it will be a dog.

Alternatively you can again use MS Access to get the folder and file names, and execute OPENROWSET() from MS Access using ADO.
0
 
Dale FyeAuthor Commented:
Thanks, ac.

I was originally importing the Excel data to Access, then running an append query to get it into SQL Server, this as taking about 16 hours.  The app currently uses Excel automation to open and extract data from Excel, and ADO to write the data to SQL Server.

I'll take a look at the OpenRowSet() syntax and see if I can make that work.
0
 
nathanielIT ConsultantCommented:
this might help you to get started

http://nlhizon.net84.net/?q=node/29#overlay-context=
0
 
Dale FyeAuthor Commented:
still working on this issue, but it was lowered in my priority list.  will hopefully get back to it soon.
0
 
Dale FyeAuthor Commented:
ac,

Went with the OpenRowset() technique.

Thanks for the help on this.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now