List in Memory or text files? 1

[VBn00b]

Hi guys,

After sorting through a text file with my VBS scripts I end up with 2 collections of entries. 1 with collection will have 650 lines and the other 1 will have 50 lines.

Question now is do I create 2 lists in memory perform my SQL queries on them?

or

Do I create 2 temp text files and then call them when performing the SQL queries?

Thanks for the help

 

[markdmac]

I think you need to provide more details on what you are trying to do if you wish to have any kind of intelligent feedback/suggestions.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[VBn00b]

Sorry I just tried to make it brief as possible!

From the beginning:

I have a text file with 650 lines of info. Each should contain all of the following fields:
Username, Name, Surname, Password, Email

Yet some lines only contain 3 fields:
Username, Surname, Email.

I need to add all 650 lines to a database in MSSQL. All the lines with 5 fields needs to go into a table called good and all the lines with 3 fields need to go into a table called bad.

So what i've done is i've search for the number of commas on a line. If there are 4 commas on the line then the line is good. If there is less than 4 commas then the line is bad.

I have done the sql part, the sorting, error checking etc.

I would like to know should I create 2 lists in memory (on the fly), 1 good and 1 bad and then process them in the SQL part?

or

Should I create 2 text files, good and bad, and then read from them when processing the SQL part?

Thanks again

 

[markdmac]

You already need to loop through all entries to check the number of commas, so just write directly to SQL from there.

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the data file
Set oTextStream = objFSO.OpenTextFile("SourceFile.txt")
'make an array from the data file
SourceArray = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close
For Each Sample In SourceArray
	'sample="this, that, other, thing, too"
NumCommas = Len(Sample) - Len(Replace(Sample, ",", ""))
	If NumCommas = 4 Then
		WScript.Echo "Good String"
		WScript.Echo "Write to Good SQL Table code goes here"
	Else
		WScript.Echo "Bad String"
		WScript.Echo "Write to Bad SQL Table code goes here"
	End If	
Next

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[VBn00b]

Thanks Thats also a good idea!

I was thinking of doing something along the lines of:

If NumCommas = 4 Then
WScript.Echo "Good String"
WScript.Echo "Line added to Good.txt"
Else
WScript.Echo "Bad String"
WScript.Echo "Line added to Bad.txt"
End If

----

Then read from the good.txt and make the sql entries.

OR

Is there any way to hold a complete list in memory exactly like what you would find in the good.txt and bad.txt file?

eg
Username, Name, Surname, Password, Email
Username, Name, Surname, Password, Email
Username, Name, Surname, Password, Email
Username, Name, Surname, Password, Email

Username, Name, Email
Username, Password, Email
Surname, Password, Email


If NumCommas = 4 Then
WScript.Echo "Good String"
add.string.goodlist strLine
Else
WScript.Echo "Bad String"
add.string.badlist strLine
End If

I would like to try all 3 methods. I will try yours first.

Thanks

 

[markdmac]

I think you are over complicating this.

Where i have specified to add the line to the SQL table you can just execute the SQL Insert command. There is no need to compile a list to then re-read. Insert each line into the appropriate table at the time it is evaluated for the proper number of commas.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[VBn00b]

OK thanks since I am the n00b I will take your advice Will update you when I try this later today!

Cheerz for being so helpful

 

[VBn00b]

Ok its working great! I also just realized I'll be making 700 mssql queries weather I add them to a temps files and read them back of if I do the query in the "if...then" check.

One last issues i'm having is checking if the database/table i will be writing to exists or not! At the moment I can get it to drop and create the database and tables, just not check if exists before dropping:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=127.0.0.1;" & _
"Database=Master;" & _
"user id=test;" & _
"password=test;"
Set objRSSQL = CreateObject("ADODB.Recordset")

SQLQuery = "USE [MASTER]"
objRSSQL.Open SQLQuery, objConn

SQLQuery = "DROP DATABASE [DomainUsers]"
objRSSQL.Open SQLQuery, objConn

SQLQuery = "USE [MASTER]"
objRSSQL.Open SQLQuery, objConn

SQLQuery = "CREATE DATABASE [DomainUsers]"
objRSSQL.Open SQLQuery, objConn

SQLQuery = "USE [DomainUsers]"
objRSSQL.Open SQLQuery, objConn

SQLQuery = "CREATE TABLE [dbo].[tb_Excluded](" & _
"[AccountName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
"[FirstName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
"[LastName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
"[FullName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, " & _
" [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL" & ")"
objRSSQL.Open SQLQuery, objConn

The above is what I have that is working. Could you help me out with the checking of the database and tables please

Thank you!

 

[VBn00b]

Sorry typo:

SQLQuery = "CREATE TABLE [dbo].[tb_Excluded](" & _

should be:

SQLQuery = "CREATE TABLE [dbo].[tb_DomainUsers](" & _

So I end up with a db and name domainusers and a table named domain users!

Thanks

 

[markdmac]

Use On Error Resume Next at the top of your script. That will let you trap errors. Then execute a Select * query of the table and check for a record count to determine if you have data there or not.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.