[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBScript to parse text file and update excel file

Posted on 2014-07-16
13
Medium Priority
?
5,909 Views
Last Modified: 2014-07-18
Hi Experts:

I need help with VBScript.  I have a text file that is attached.  I need a VBScript that can parse the text file and read the rows and update the data in the excel file.  

Each record in the text file comprises of 2 rows, so both rows must be read to update one row in the excel file.

The data that needs to be read in the text file is
1. 'Person name' that starts from Position 151-175 in the text file (line 1)
2. 'Code' that starts from Position 18-52 in the text file (line 1)
3. 'Object' that starts from Position 162-166 (line 2)
4. 'Default #' that starts from Position 151-160 (line 2)

Opening this sample file in a good editor will give you the position #.  I used Notepad++.

Thanks in advance for your help.

Regards.
Sample.txt
0
Comment
Question by:Imran Asif
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 8

Accepted Solution

by:
jawa29 earned 2000 total points
ID: 40201658
Hi,

This sounds very straight forward providing nothing changes! (Providing the same cells get edited on the Excel Spreadsheet and the text file is always constructed as per your example).

This snippet of code will read your sample file and retrieve the information you have requested, you use the Mid() function to specify the starting point on the line and how many Characters to read into the string.

' Declare a constant that can be used throughout the script
Const ForReading = 1

' Set a string to the path of the text file (Example: C:\MyDev\MyTextfile.txt)
MyTextFilePath = "C:\MyDev\MyTextfile.txt"

' Set the use of the FileSystemObject so we can open and read the text file
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

' Set the text file to read and open it in read-only mode
Set MyTextRead = FileSystemObject.OpenTextFile(MyTextFilePath, ForReading)

' Loop through the text file until we reach the bottom of it
Do Until MyTextRead.AtEndOfStream

	' Read a line from the text file into a string called Line
	Line = MyTextRead.ReadLine
	
	' Get a segments of the line by using the Mid function
	' Using Trim function on MyName to trim off blank spaces from the start of end
	MyName = Trim(Mid(Line, 151, 24))
	MyCode = Mid(Line, 18, 34)
	
	' Read the next line of the text fiel into a string called Line2
	Line2 = MyTextRead.ReadLine
	
	' Get segments of the line by using the Mid function
	MyObject = Mid(Line2, 162, 5)
	MyDefault = Mid(Line2, 151, 10)
	
' loop to the next unread line (i.e. skipping the two we have just read)
Loop

Open in new window

This snippet of code shows how to write data to a specific cell within Excel, this does require the machine running the code to have Excel installed.
' Set a string to the path of the Excel File
MyExcelFilePath = "C:\MyDev\MyExcel.xls"

' Open the spreadsheet using the Excel Application Object
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.WorkBooks.Open MyExcelFilePath

' Set the Active Worksheet (in this case the first sheet)
Set SheetObject = ExcelObject.ActiveWorkbook.Worksheets(1)

' Modify data in a cell (in this case we are adding data to C2)
' First value in the brackets is the Column number
' Second value is the Cell number
SheetObject.Cells(3, 2).Value = "Test"

' Save and quit
ExcelObject.ActiveWorkbook.Save
ExcelObject.ActiveWorkbook.Close
ExcelObject.Application.Quit

Open in new window

So the conclusion is, if you were to construct a script with the first piece of code then within the loop section add the Excel portion to write the data into Excel
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40201659
Hi, when you say the Excel rows will be updated, which field contains the unique value as the lookup? Will the person name be unique and should be used as an exact match check?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40201825
Can we safely infer that your text file contains pairs of lines, two for each set of data?
0
Technology Partners: 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!

 
LVL 46

Expert Comment

by:aikimark
ID: 40201828
How big will your text file be?
Are there any other constraints or requirements we should consider?
0
 

Author Comment

by:Imran Asif
ID: 40202262
Hi jawa29:

The Excel file has only value 'Test' written.  Is it possible to write the text read from text file written in excel?
Column name should be:
1. 'Person name'
2. 'Code'
3. 'Object'
4. 'Default #'
0
 

Author Comment

by:Imran Asif
ID: 40202279
Hi aikimark:

Can we safely infer that your text file contains pairs of lines, two for each set of data?
Yes.

How big will your text file be?
5 MB

Are there any other constraints or requirements we should consider?
There is a key at each line at position 110, which is of 2 characters. In line 1 it is 31 and line 2 it is 39.  If the line contains 31 at position 110 then the person name will be at position 151-175.  If the line contains 39 at the position 110 then the 'Object' code will be at 162-166

Thanks a lot.
0
 

Author Comment

by:Imran Asif
ID: 40202286
Hi RobSampson:

Actually 'Code' at line 1 Position 18-52 will be unique.

Thanks
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40202304
I Jawa29 has the best approach.

If you have time/performance constraints, then the entire file can be read (.ReadAll), an array of the lines created with the Split() function, using vbCrLf delimiter.  The Jawa29 code would then iterate the array of line data, processing the even-odd pairs of lines.  The tradeoff is that you will need to have available physical memory to hold the line data, which shouldn't be a problem with 5MB text files.
0
 

Author Comment

by:Imran Asif
ID: 40202480
I modified the script by jawa29 to write to the excel file.  But only the first row is inserted and looks like nothing happens after that.

Can you please help?

Thanks,

' Declare a constant that can be used throughout the script
Const ForReading = 1

' Set a string to the path of the text file (Example: C:\MyDev\MyTextfile.txt)
MyTextFilePath = "C:\test\Sample.txt"

' Set the use of the FileSystemObject so we can open and read the text file
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

' Set the text file to read and open it in read-only mode
Set MyTextRead = FileSystemObject.OpenTextFile(MyTextFilePath, ForReading)
x = 1

' Loop through the text file until we reach the bottom of it
Do Until MyTextRead.AtEndOfStream

	' Read a line from the text file into a string called Line
	Line = MyTextRead.ReadLine
	
	' Get a segments of the line by using the Mid function
	' Using Trim function on MyName to trim off blank spaces from the start of end
	MyName = Trim(Mid(Line, 151, 24))
	MyCode = Mid(Line, 18, 34)
	
	' Read the next line of the text fiel into a string called Line2
	Line2 = MyTextRead.ReadLine
	
	' Get segments of the line by using the Mid function
	MyObject = Mid(Line2, 162, 5)
	MyDefault = Mid(Line2, 151, 10)
	
' loop to the next unread line (i.e. skipping the two we have just read)
'Loop

' Set a string to the path of the Excel File
MyExcelFilePath = "C:\test\Book1.xlsx"

' Open the spreadsheet using the Excel Application Object
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.WorkBooks.Open MyExcelFilePath

' Set the Active Worksheet (in this case the first sheet)
Set SheetObject = ExcelObject.ActiveWorkbook.Worksheets(1)

' Modify data in a cell (in this case we are adding data to C2)
' First value in the brackets is the Column number
' Second value is the Cell number
'Do Until SheetObject.AtEndOfStream 
     
     SheetObject.Cells(x, 1).Value = MyName 
     SheetObject.Cells(x, 2).Value = MyCode
     SheetObject.Cells(x, 3).Value = MyObject 
     SheetObject.Cells(x, 4).Value = MyDefault
      
     x = x + 2 
Loop 

'SheetObject.Cells(3, 2).Value = "Test"

' Save and quit
ExcelObject.ActiveWorkbook.Save
ExcelObject.ActiveWorkbook.Close
ExcelObject.Application.Quit
                                          
                                          

Open in new window

0
 

Author Comment

by:Imran Asif
ID: 40202611
I modified the script again and this time 'MyCode' is displayed as '2.26672E+33' in excel column 2.  Can this be displayed as it is in the text file?

' Declare a constant that can be used throughout the script
Const ForReading = 1

' Set a string to the path of the text file (Example: C:\MyDev\MyTextfile.txt)
MyTextFilePath = "C:\test\Sample.txt"

' Set the use of the FileSystemObject so we can open and read the text file
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

' Set the text file to read and open it in read-only mode
Set MyTextRead = FileSystemObject.OpenTextFile(MyTextFilePath, ForReading)
x = 1

' Set a string to the path of the Excel File
MyExcelFilePath = "C:\test\Book1.xlsx"

' Open the spreadsheet using the Excel Application Object
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.WorkBooks.Open MyExcelFilePath

' Set the Active Worksheet (in this case the first sheet)
Set SheetObject = ExcelObject.ActiveWorkbook.Worksheets(1)

' Modify data in a cell (in this case we are adding data to C2)
' First value in the brackets is the Column number
' Second value is the Cell number
'Do Until SheetObject.AtEndOfStream 


' Loop through the text file until we reach the bottom of it
Do Until MyTextRead.AtEndOfStream

	' Read a line from the text file into a string called Line
	Line = MyTextRead.ReadLine
	
	' Get a segments of the line by using the Mid function
	' Using Trim function on MyName to trim off blank spaces from the start of end
	MyName = Trim(Mid(Line, 151, 24))
	MyCode = Mid(Line, 18, 34)
	
	' Read the next line of the text fiel into a string called Line2
	Line2 = MyTextRead.ReadLine
	
	' Get segments of the line by using the Mid function
	MyObject = Mid(Line2, 162, 5)
	MyDefault = Mid(Line2, 151, 10)
	
' loop to the next unread line (i.e. skipping the two we have just read)
'Loop

     
     SheetObject.Cells(x, 1).Value = MyName 
     SheetObject.Cells(x, 2).Value = MyCode
     SheetObject.Cells(x, 3).Value = MyObject 
     SheetObject.Cells(x, 4).Value = MyDefault
      
     x = x + 1 
Loop 

'SheetObject.Cells(3, 2).Value = "Test"

' Save and quit
ExcelObject.ActiveWorkbook.Save
ExcelObject.ActiveWorkbook.Close
ExcelObject.Application.Quit
                                          
                                          

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40203169
And I will be back on deck next week to lend a hand if need be. Your rules seem pretty straightforward now. You would need to look at setting the NumberFormat property of each cell (or column) before writing to it.
0
 
LVL 8

Expert Comment

by:jawa29
ID: 40203988
@imranasif2010, aikimark was right I was asleep!!

Looks like the data is right, I would imagine if you expanded the column in Excel it would give you the right figure. Excel likes to shorten numbers if they are too long for the cell.

But also it is best to follow what RobSampson says about formatting the spreadsheet before adding the data, this way you can guarantee that Excel won't mess about with the data.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

834 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