VBScript to parse text file and update excel file

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
Imran AsifAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jawa29Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobSampsonCommented:
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
aikimarkCommented:
Can we safely infer that your text file contains pairs of lines, two for each set of data?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

aikimarkCommented:
How big will your text file be?
Are there any other constraints or requirements we should consider?
0
Imran AsifAuthor Commented:
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
Imran AsifAuthor Commented:
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
Imran AsifAuthor Commented:
Hi RobSampson:

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

Thanks
0
aikimarkCommented:
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
Imran AsifAuthor Commented:
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
Imran AsifAuthor Commented:
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
RobSampsonCommented:
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
jawa29Commented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.