Solved

VBScript to Parse Text File and Output to SQL

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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