Avatar of usslindstrom
usslindstromFlag for Japan

asked on 

Routine - From txt file to SQL (with parse)


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.
Microsoft SQL Server 2008Microsoft SQL Server 2005Databases

Avatar of undefined
Last Comment
Avatar of usslindstrom
Flag of Japan image


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-101-501CA	501C	ABQ3903273	101	SITE-FE-												
# Color Duplex Printers																
# Poster Printer

Open in new window

Avatar of usslindstrom
Flag of Japan image


Here is my SQL layout...

 User generated image User generated image
Avatar of vdr1620
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of usslindstrom
Flag of Japan image


Thank you guys for your assistance.  I will start reading up on implementing that solution.
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo