Routine - From txt file to SQL (with parse)

usslindstrom
usslindstrom used Ask the Experts™
on
Experts,

As usual, I'm in need of some serious help that's probably extremely easy for all of you.  :)

Background:  In our enterprise, we have a vbscript that adds printers to user's workstations.  This script is run against a computer and reads a text file with a list of printers and associated room numbers, and based on the variable %computer% installs printers that are assigned to itself.

I've attached a copy of the text file that gets scanned by vb below.

Well...  I've taken this business logic to the next level, and designed a SQL database /vb script pair that mimics the original behavior of this script.  It's actually very slick, and I'm quite proud of myself.  :)  In saying that, I've normalized the crap out of the database, and split the text file you see below into 8 individual tables, including 2 lookup tables and a ton of 'views'.  (I've included screenshots of the new table layout).

So now comes my "I have NO idea how I'm going to do this" question that I need help with.

We have approx 45 sites that are doing business currently using the original vbscript / textfile solution.  I need to develop a "transitory" solution that will migrate everyone from the text file into the SQL database.

How would I write a script that could read the contents of what you see below, located at each one of the sites...  Into the SQL instance?  As they're ALL doing business the old way with the text files, I'm guessing I need to periodically read the file and inject any changes into SQL.

ANY ideas on this topic are more than welcome, and I appreciate any help that you guys can give.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
The way it's broken down in this code:

Column 1 = Printer Name
Column 2 = Printer Designation Code (Company Policy)
Column 3 = Serial Number
Column 4 = Printer Location
Column 5 = First installed room & default printer
Column 6 - Unlimited = Additional room installs, but not default.
# Host Name	Model	Comment	Location	Default Printer	Non-Default Installed	Non-Default installed	Etc ...									
# (From Printer)	(Specific)	(Optional)	(Optional)	(During Logon)												
																
SITE-	delete															
																
# B/W Printers																
SITE-213-400BA	400B	APT7703924	213	SITE-213-												
SITE-214-400BA	400B	APT7703898	214	SITE-214-												
SITE-216-400BA	400B	APT7703921	216	SITE-216-												
SITE-217-420BA	420B	CNBX310321	217	SITE-217-												
SITE-215-383BA	383B	ABU4804625	215	SITE-215-												
SITE-207-400BA	400B	XPK8327759	207	SITE-207-												
SITE-205-420BA	420B	CNBX115552	205	SITE-205-												
SITE-202-383BA	383B	ABU4804658	202	SITE-202-												
SITE-201-383BA	383B	ABU4904857	201	SITE-201-												
SITE-201F-383BA	383B	ABU4904876	201F	SITE-201F-												
SITE-227-380BA	380B	VBB3X13915	227	SITE-227-												
SITE-226-400BA	400B	XPK8327808	226	SITE-226-												
SITE-221-383BA	383B	ABU4904836	221	SITE-221-												
SITE-120-363BA	363B	3500MLP	120	SITE-120-												
SITE-121-400BA	400B	XPK8327830	121	SITE-121-												
SITE-121-363BA	363B	3500M98	121	SITE-121-												
SITE-117-363BA	363B	3500MMX	117	SITE-117-												
SITE-111-654BA	654B	793HWLC	111	SITE-111-												
SITE-102-420BA	420B	USBNM15129	102	SITE-102-												
SITE-102-383BA	383B	ABU6220177	103	SITE-102-												
SITE-103-383BA	383B	ABU5412595	103	SITE-103-												
SITE-103C-383BA	383B	ABU5412565	103C	SITE-103C-												
SITE-103A-383BA	383B	ABU5413216	103A	SITE-103A-												
SITE-103-383BA	383B	ABU5412595	103	SITE-IT-
PDO-204-HP5SIMX	005B	J2550B  	101	SITE-FE-												
																
																
# B/W Duplex Printers																
																
																
# Color Printers																
SITE-209-810CA	810C	APU7800420	209	SITE-209-	SITE-206-	SITE-213-	SITE-203-	SITE-216-	SITE-102-	SITE-215-	SITE-208-	SITE-212-	SITE-217-	SITE-205-	SITE-204-	SITE-FE-
SITE-237-802CA	802C	UPE5600865	237	SITE-237-												
SITE-237-480CA	480C	GK7E007238	237	SITE-237-												
SITE-229-810CA	810C	APU7X00472	229	SITE-229-	SITE-237-	SITE-228-	SITE-225-	SITE-233-	SITE-235-	SITE-230-	SITE-232-					
SITE-226-511CA	511C	926944973	226	SITE-226-												
SITE-220-460CA	460C	JPCKB28319	220	SITE-220-												
SITE-120-501CA	501C	ABW4900176	120	SITE-120-												
SITE-121-501CA	501C	ABW4900196	121	SITE-121-												
SITE-117-810CA	810C	APU7X00480	117	SITE-117-	SITE-121-	SITE-120-	SITE-111-	SITE-116-	SITE-118-	SITE-114-	SITE-115-					
SITE-117-501CA	501C	ABW4900200	117	SITE-117-												
SITE-101-501CA	501C	ABQ3903273	101	SITE-101-												
SITE-103A-503CA	503C	APE8913454	103A	SITE-103A-												
SITE-103C-501CA	501C	ABQ3903261	103C	SITE-103C-												
SITE-103A-503CA	503C	APE8913454	103A	SITE-IT-												
SITE-WH-810CA	810C	APU7X00482	WH	SITE-WH-
SITE-101-501CA	501C	ABQ3903273	101	SITE-FE-												
																
# Color Duplex Printers																
																
																
# Poster Printer

Open in new window

Author

Commented:
Here is my SQL layout...

 Tables.png Views.png
Commented:
I would suggest you use SSIS for the Import and then schedule the ETL package using a SQL job to update the values or changes

Now, if you are already familiar with SSIS ..this would be a simple task.. All you need to do is create a Dataflow task with Flat File Source and Then use a Conditional Split Transformation to Filter out unnecessary data like #color printers etc and then load the valid data into SQL tables..you can also do lookups using Lookup transformation, if needed

SSIS
http://www.accelebrate.com/sql_training/ssis_tutorial.htm

Import text file
http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/
Aaron ShiloChief Database Architect
Commented:
hi

i agree with vdr1620

i would to go with SSIS to solve this issue.

Author

Commented:
Thank you guys for your assistance.  I will start reading up on implementing that solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial