SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 In Desparate need of an Import Tool		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

edb2003
Yak Posting Veteran

66 Posts
Posted - 09/25/2003 :  02:53:31  Show Profile  Reply with Quote
Hi everyone,
I am in desparate need of a tool that can do the following:

1. Have a built in time to handle tasks #2 and #3 below at 4 am every morning.

2. Clean out all data found in 2 tables of a database in SQL Server 7.

3. Take a flat fixed width file and extract each field and place it into one of the two tables.

A friend of mine told me a way to build the tool by building an XML parser file in .Net. I was wondering if anyone knew of how to do this or of a tool that can do this.

I am running out of time and need some help in this. Any help in steering me the right direction I would tremendously appreciate it.

Thank you,
Edb

byrmol
Shed Building SQL Farmer

Australia
1591 Posts
Posted - 09/25/2003 :  03:05:05  Show Profile  Reply with Quote
Look up BCP and Jobs in BOL...

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 09/25/2003 :  07:12:50  Show Profile  Reply with Quote
DELETE ...
BULK INSERT ...

Jay White
{0}
Go to Top of Page

robvolk
Most Valuable Yak

USA
15691 Posts
Posted - 09/25/2003 :  08:07:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
A friend of mine told me a way to build the tool by building an XML parser file in .Net.
quote:
I am running out of time and need some help in this.
Talk about incompatible approaches.

Whenever someone suggests XML for a simple task, remind them we've put men on the moon and built the atomic bomb without XML, or even object-oriented programming, and have done nothing equivalent since with either.
Go to Top of Page

edb2003
Yak Posting Veteran

66 Posts
Posted - 09/25/2003 :  10:05:43  Show Profile  Reply with Quote
I love the input expecially about my friend. I should have him take a look at this. He is too busy working on complex .net projects with Toyota and Honda and he has no time for me.

I like the one about going to a web site and getting help from an outsource facility. I will check that one out.

Thank you everyone for your help :)
Looks like I may simply take a crash course in XML and simply see if I can build it over this weekend if I run out of answers today.

I have til the end of next week to complete.

Keep me in your prayers, literally :)
Happy Day,
edb
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 09/25/2003 :  10:33:23  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
A flat fixed with file has nothing to do with XML. I think Rob is saying "why are you even bringing that up?"

Even DTS can import fixed-width files really easily, or the JET engine using the TXT driver.

Open up enterprise manager (yep!), and choose Tools --- Import Data (or something like that) and follow the wizards, it's easy. then just schedule it to run each night. And check out the other options and look at the package it creates, and read books on-line.

But do NOT confuse yourself with XML in all this, UNLESS the file you need to import is in XML format -- and you said it was a fixed width text file, so it must not be.

- Jeff
Edited by - jsmith8858 on 09/25/2003 11:00:32
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 09/25/2003 :  11:08:37  Show Profile  Reply with Quote
I heard you can use and XML Web Service for potty training too ...

Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 09/25/2003 :  11:17:34  Show Profile  Reply with Quote
I'd like to go arounfd the block, but the Shuttle is grounded, so I guess I'll have to build a new one...

Or...create a sproc like the one below and create a scheduled job on the server...


CREATE mySPROC @FilePath varchar(200) @File_Name varchar(55) 
AS
	BEGIN

		DECLARE @filePathAndName varchar(255), @cmd varchar(2000), @Command_String (3000)


		TRUNCATE TABLE myTable1
		TRUNCATE TABLE myTable2
		Select @FilePathAndName = @FilePath + '\' + @File_Name

		SET @cmd = 'bcp ' + @db_name + '..LOAD_TEMP in ' 
			+ @FilePathAndName + ' -t"" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
		SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''

		Exec(@Command_String)

/* Some SQL like the following to follow the logic you need to populate your final tables */

		INSERT INTO FINAL_TABLE (
			          TAx_Year
				, Company_Cd
				, Admin_Sys_Cd
				, BUF_Cd
				, Admin_Unit_Cd
				, Contract_No
				, TIN
				, TIN_Type
				, Return_Type_1099
				, W2_Type
				, State_Cd
				, Tax_Event_Dt
				, State_Tax_WH
				, Prev_State_Tax
				, Fed_Tax_WH
				, Prev_Fed_Tax
				, Wages
				, Soc_Sec_Wages
				, Soc_Sec_Withheld
				, Medicare_Wages
				, Medicare_Withheld
				, ADV_EIC
				, Emp_First_Name
				, Emp_Last_Name
				, Total_Record_Count 
)
			SELECT    Substring(DataRow,1,4)    			AS TAx_Year
				, Substring(DataRow,5,3)    			AS Company_Cd
				, Substring(DataRow,8,3)    			AS Admin_Sys_Cd
				, Substring(DataRow,11,3)   			AS BUF_Cd
				, Substring(DataRow,14,1)   			AS Admin_Unit_Cd
				, Substring(DataRow,15,20)  			AS Contract_No
				, Substring(DataRow,35,9)   			AS TIN
				, Substring(DataRow,44,1)   			AS TIN_Type
				, Substring(DataRow,45,1)   			AS Return_Type_1099
				, Substring(DataRow,46,2)   			AS W2_Type
				, Substring(DataRow,48,2)   			AS State_Cd
				, Substring(DataRow,50,26)  			AS Tax_Event_Dt
				, CONVERT(money,Substring(DataRow,76,14))/100   AS State_Tax_WH
				, CONVERT(money,Substring(DataRow,90,14))/100   AS Prev_State_Tax
				, CONVERT(money,Substring(DataRow,104,14))/100  AS Fed_Tax_WH
				, CONVERT(money,Substring(DataRow,118,14))/100  AS Prev_Fed_Tax
				, CONVERT(money,Substring(DataRow,132,14))/100  AS Wages
				, CONVERT(money,Substring(DataRow,146,14))/100  AS Soc_Sec_Wages
				, CONVERT(money,Substring(DataRow,160,14))/100  AS Soc_Sec_Withheld
				, CONVERT(money,Substring(DataRow,174,14))/100  AS Medicare_Wages
				, CONVERT(money,Substring(DataRow,188,14))/100  AS Medicare_Withheld
				, Substring(DataRow,202,14)			AS ADV_EIC
				, Substring(DataRow,216,15) AS Employee_First_Name
				, Substring(DataRow,231,40) AS Employee_Last_Name
				, Substring(DataRow,271,8)  AS Total_Record_Count 
			  FROM    LOAD_TEMP
			 WHERE    Substring(DataRow,1,1) <> 'H'
			   AND    Substring(DataRow,1,14) <> 'CONTROL_TOTALS'

GO


All I wanted was a cup of coffee....





Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!
Go to Top of Page

edb2003
Yak Posting Veteran

66 Posts
Posted - 09/25/2003 :  14:45:05  Show Profile  Reply with Quote
Sorry for not being detailed. I was thinking of an XML Parsing tool because I had 1 single text file that had records that needed to be moved and formatted into 2 tables.

1 table has unique records
DrawingNum Revisions Filename
2135555 U 231555B.pdf
2nd table has multiple records
DrawingNum Old Revisions OldFilenames
2135555 T 231555T.pdf
2135555 S 231555T.pdf
2135555 R 231555T.pdf
2135555 Q 231555T.pdf
2135555 P 231555T.pdf


The text file looks like this

2135555 P Q R S T

Purpose of Table 1 - All Unique Drawings
Purpose of Table 2 - Old Revision Drawings

Originally, this all should have been built in 1 table but I did not come on board after the fact. The Front-End ASP Tool works great based on the logic of the databases provided to me in the beginning.

Then they changed the file and because the database is running on a propriatary Mantus program, it is very complicated to reproduce the original table layout.

Sorry for any inconveniences.

Ed
Go to Top of Page

edb2003
Yak Posting Veteran

66 Posts
Posted - 09/25/2003 :  14:48:38  Show Profile  Reply with Quote
GOOD NEWS

Another friend of mine gave me the idea to port all data into SQL and run Stored Procedures to create the 2 tables. Then I need to time this for a daily removal of all info in tables and port new data to replace it. I will keep you posted.

thx,
Ed
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 09/25/2003 :  14:52:02  Show Profile  Reply with Quote
Was your other friends name "Brett"?



Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!
Go to Top of Page

edb2003
Yak Posting Veteran

66 Posts
Posted - 09/25/2003 :  16:11:16  Show Profile  Reply with Quote
Definito ... Thanks bunches for the input, Brett.

\\\||///
@ -
/
|___|

edb
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000