|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Automate Importing FileHello,
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 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 Portas SQL Server MVP -- 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? -- Show quoteDavid Lozzi Web Applications Developer dlozzi@(remove-this)delphi-ts.com "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1131721686.461895.277140@f14g2000cwb.googlegroups.com... > 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 Portas > SQL Server MVP > -- > 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 Portas SQL Server MVP -- 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1131729658.524983.90700@f14g2000cwb.googlegroups.com... >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 Portas > SQL Server MVP > -- > 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 Portas SQL Server MVP -- 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, Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1131730446.155383.58250@g47g2000cwa.googlegroups.com... > 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 Portas > SQL Server MVP > -- > 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 Portas SQL Server MVP -- 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 <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message Show quote news:1131734313.446116.198130@g14g2000cwa.googlegroups.com... > 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 Portas > SQL Server MVP > -- > Integration services package job (with tasks) would seem to be your friend
here. -- Show quoteWilliam Stacey [MVP] "David Lozzi" <dlozzi@nospam.nospam> wrote in message news:OjRBg6s5FHA.1184@TK2MSFTNGP12.phx.gbl... > 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 > 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 Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:u5KcPct5FHA.3592@TK2MSFTNGP12.phx.gbl... > Integration services package job (with tasks) would seem to be your friend > here. > > -- > William Stacey [MVP] > > "David Lozzi" <dlozzi@nospam.nospam> wrote in message > news:OjRBg6s5FHA.1184@TK2MSFTNGP12.phx.gbl... >> 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 >> > > 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 Show quote "David Lozzi" wrote: > 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 > > > "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message > news:u5KcPct5FHA.3592@TK2MSFTNGP12.phx.gbl... > > Integration services package job (with tasks) would seem to be your friend > > here. > > > > -- > > William Stacey [MVP] > > > > "David Lozzi" <dlozzi@nospam.nospam> wrote in message > > news:OjRBg6s5FHA.1184@TK2MSFTNGP12.phx.gbl... > >> 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 > >> > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics