Solved

VBScript to Parse Text File and Output to SQL

Posted on 2009-06-30
3
711 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBS Script not working correctly. 1 37
sql query help 2 44
Running Access application from Task Scheduler 6 33
Oracle 10g - insert string with special characters 8 41
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

910 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

23 Experts available now in Live!

Get 1:1 Help Now