Automate Importing File

[David Lozzi]

Hello,

I need to automate importation of a excel file into a table. Here's my
scenario: I'm writing an ASP.NET application where users can pull reports on
imported data. The imported data is pulled from an old UNIX based system,
then converted to Excel. I want the user to be able to use the web app to
select and upload the file to the server, then press a button to have the
SQL server process the Excel file and import it. I know I can do this all in
ASP.NET: open excel file and append records to the table, but this can't be
optimal. The Excel file will be in the same format each time so columns and
such will be the same for each import.

Can you provide me some feedback or link to some resources.

Thanks!

David Lozzi

 

[David Portas]

You could write a DTS package to import the data and then invoke that
through DMO.

Based on many past experiences I have to say that Excel is just about
the worst possible format to choose for data interchange. Especially so
if manual intervention by users is involved. There are lots of problems
inherent in the formatting, undefined datatypes and lack of validation
that are implied by the spreadsheet format. If you have a Unix source
that you can import to Excel then that may well be a better place to
source the data for your database.

 

[David Lozzi]

Before it reaches Excel its in a tab delimited text file. WOuld this be
happier?

Also, how do I write what I need. I need a little guidence on the
development of the import. Any references?

 

[William Stacey [MVP]]

Integration services package job (with tasks) would seem to be your friend
here.

 

[David Lozzi]

OK, I made the package and it runs great when manually ran. how do I run it
from my web app? I'm using ASP.Net.

Thanks,

David

 

[David Portas]

A delimited file is certainly easier to import. You can load it with a
single BULK INSERT - see Books Online for details of that command.
Obviously you need to be sure that you will never have TABs in the data
between the delimiters.

 

[David Lozzi]

OK, I made the package and it runs great when manually ran. how do I run it
from my web app? I'm using ASP.Net.

Thanks,

David

 

[David Portas]

In SQL Server 2000 you'll have to use COM to create the SQLDMO Package
Object. Run it with the Execute Method.

Books Online is your friend. It documents the details of all this. If
you don't have BOL you can find it at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp?frame=true

 

[David Lozzi]

Yes, I have and am using the SQL Books Online...

When searching for these items, I just get more confused. Would BULK_INSERT
be easier? You simply say "use COM to create the SQLDMO Package Object. Run
it with the Execute Method." I have no idea what you mean. Sad, I know, but
I'm trying here. Can you go into a little more detail?

Thanks,

 

[REMOVE_BEFORE_REPLYING_dportas]

1) BULK INSERT. Here's an example of how to insert to a table from a
TAB delimited file using T-SQL. You can obviously execute this proc
using the ADO Command Object. Always load to a view rather than a table
otherwise the load will break if you add new columns to the table.

CREATE PROC dbo.usp_load
AS

DECLARE @error INTEGER

BULK INSERT dbo.some_view
FROM 'd:\path\your_file.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

SET @error = @@ERROR
/* error handling goes here */

RETURN @error

GO

To create the view used in this example (at design-time rather than
run-time):

CREATE VIEW dbo.some_view
AS
SELECT col1, col2, col3, ...
FROM your_table

2) The above won't do for an Excel file - you'll have to run a DTS
package for that. The DTS object model is a COM API - not .NET. There
are various examples in the following article showing the different
ways to call a DTS package: C#, VB, ASP, command line, etc:

http://www.sqldts.com/default.aspx?104

 

[David Lozzi]

Ah #2 helped a lot. I'm now getting

Step "DTSStep_DTSDataPumpTask_1" Failed
Error: -2147467259
Source: Microsoft Data Transformation Services Flat File Rowset Provider
Description: Error opening datafile: Logon failure: unknown user name or bad
password.

If you happen to know exactly the solution, please let me know. otherwise I
think I can handle it.

Thanks a lot for you help!
David

 

[Guest]

I think you can create a stored procedure to run DTS and call the sp from
asp.net

CREATE PROCEDURE pr_import_data
AS
exec master..xp_cmdshell 'dtsrun /Sserver_name /Uuser_nName
/Ppassword /Npackage_name'
GO

For Syntax
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp