Solved

VBScript to Parse Text File and Output to SQL

Posted on 2009-06-30
3
710 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now