Solved

VBScript to Parse Text File and Output to SQL

Posted on 2009-06-30
3
712 Views
Last Modified: 2012-05-07
I am in need of a VBScript that will parse a text file and output the data into SQL query format.

Text file to parse will contain this data in this format:

Blob00004.DAT
Blob00005.DAT
Blob00016.DAT
Blob00017.DAT
Blob00019.DAT
Blob00020.DAT
Blob00342.DAT
Blob00343.DAT
Blob00344.DAT
Blob00346.DAT
Blob01768.DAT
Blob01769.DAT
Blob01770.DAT
Blob01772.DAT

What I am looking for is a script that will read the above text file line by line and output into a new text file that is structured in SQL format.  The output would need to be the following:

('4', '5', '16', '17', '19', '20', '342', '343', '344', '346', '1768', '1769', '1770', '1772')

I can then take this data and copy it into a SQL query to update a table in the database

UPDATE DiskFileVolume
SET RawVolumeKey = 12
WHERE DiskFileKey IN ('4', '5', '16', '17', '19', '20', '342', '343', '344', '346', '1768', '1769', '1770', '1772')

Thanks in advance for any assistance!
0
Comment
Question by:jhrad
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
mnialon earned 150 total points
ID: 24746563
Hello
please try this
  Set F1 = CreateObject("Scripting.FileSystemObject")
 
   Text = "("
   
   set modele=new regexp
 
   modele.pattern="Blob0*(.*)\.DAT"
 
 
   Set iFile = F1.OpenTextFile("t.txt")
 
 
   Do While Not iFile.AtEndOfStream
     sText = iFile.ReadLine
     set correspondances=modele.execute(sText)
     Text = Text & "'" & correspondances(0).submatches(0) & "',"
     set correspondances=nothing
   Loop
 
 
   Text = mid(text,1,len(text)-1)
   Text = "UPDATE DiskFileVolume SET RawVolumeKey = 12 WHERE DiskFileKey IN " & text & ")"
   wscript.echo Text

Open in new window

0
 

Author Comment

by:jhrad
ID: 24748302
This script is exactly what I am looking for and need.  Is there any way to get the output and pipe or redirect it into a text file, instead of a message box window?
0
 

Author Comment

by:jhrad
ID: 24748735
I figured it out.  Thank you very much for your assistance!

Set F1 = CreateObject("Scripting.FileSystemObject")
 
   Text = "("
   
   set modele=new regexp
 
   modele.pattern="Blob0*(.*)\.DAT"
 
 
   Set iFile = F1.OpenTextFile("C:\BlobDir.txt")
 
 
   Do While Not iFile.AtEndOfStream
     sText = iFile.ReadLine
     set correspondances=modele.execute(sText)
     Text = Text & "'" & correspondances(0).submatches(0) & "', "
     set correspondances=nothing
   Loop
 
 
   Text = mid(text,1,len(text)-1)
   Text = "UPDATE DiskFileVolume SET RawVolumeKey = 12 WHERE DiskFileKey IN " & text & ")"
   'wscript.echo Text
   WScript.StdOut.Write(Text)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question