Solved

VBScript to Parse Text File and Output to SQL

Posted on 2009-06-30
3
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 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