asked on

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

ASKER CERTIFIED SOLUTION

jawa29

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RobSampson

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?

aikimark

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

aikimark

How big will your text file be?
Are there any other constraints or requirements we should consider?

Imran Asif

ASKER

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 #'

Imran Asif

ASKER

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.

Imran Asif

ASKER

Hi RobSampson:

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

Thanks

aikimark

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.

Imran Asif

ASKER

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

Imran Asif

ASKER

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

RobSampson

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.

jawa29

@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.