Manipulate textfile with VBA

R

Romanoff

Hello

I need help for the following VBA-problem:

I have to insert a Value in each Line of one Textfile (file.txt).
This certain Value depends on a other Value in this Line of the
Textfile.
The depending Value is always at the same position,
The textfile is delimited with ";"

A remark, it is possible, that the Textfile has more than 65thousend
rows.


Example, Textfile: depends on FIN_PERIOD

"Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"Close Year 2005";"Year 2005";"FSA_MV";"20050930"

Depending Table, Mapping Table, this is a Worksheet in the Excel
File:
FIN_PERIOD ACC
Year 2004 100
Year 2005 500
Year 2006 1000



===> Result: [this should be done with VBA]

Modified Textfile:
"ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930"

The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500


Can anyone help me ?
I tried, but no success.... i had big problems in replacing the lines
in the textfile...
How can you programming an "vlookup" in VBA code between the certain
position in the textfile and the depending table / mapping table ?


Kind regards,
Roman
 
L

Les

It may be simpler to just parse the file in VBA and not use a worksheet at
all. Try the following subroutine on your text file. I wrote it specifially
to your requirements, so it retains all the quotes (") in your samples. This
is just ad-hoc, but you can fairly easily modify it to be more dynamic if you
plan on doing this on a regular basis, or if the requirements change
frequently.

Private Sub ImportParseText()

Dim fileNum1 As Integer 'file number for reading file
Dim fileNum2 As Integer 'file number for writing file
Dim inFile As String 'filename to parse
Dim outFile As String 'filename to write output to
Dim readLine As String 'holding variable to import lines from file

inFile = Application.GetOpenFilename _
("Text File to parse (*.txt), *.txt, all files (*.*),*.*", , "Import Text
File")

If inFile = "False" Then
MsgBox "Text File Import Cancelled"
Exit Sub
End If

outFile = Application.GetSaveAsFilename( _
InitialFileName:="", _
FileFilter:="Text Files (*.txt), *.txt", _
Title:="Output File Name")
If outFile = "False" Then
MsgBox "Text File Import Cancelled"
Exit Sub
End If

fileNum1 = FreeFile()

Open inFile For Input As #fileNum1
fileNum2 = FreeFile()
Open outFile For Output As #fileNum2

Print #fileNum2, """ACC"";""Time
Dimension"";""FIN_PERIOD"";""VIEW"";""REFERENCE_PERIOD"";"
Do While Not EOF(fileNum1)
Line Input #fileNum1, readLine
If readLine <> "" Then
If InStr(1, readLine, "Year 2004") Then
readLine = """100"";" & readLine
Print #fileNum2, readLine
End If
If InStr(1, readLine, "Year 2005") Then
readLine = """500"";" & readLine
Print #fileNum2, readLine
End If
If InStr(1, readLine, "Year 2006") Then
readLine = """1000"";" & readLine
Print #fileNum2, readLine
End If
End If
Loop
Close #fileNum1
Close #fileNum2
End Sub



--
Les Torchia-Wells


Romanoff said:
Hello

I need help for the following VBA-problem:

I have to insert a Value in each Line of one Textfile (file.txt).
This certain Value depends on a other Value in this Line of the
Textfile.
The depending Value is always at the same position,
The textfile is delimited with ";"

A remark, it is possible, that the Textfile has more than 65thousend
rows.


Example, Textfile: depends on FIN_PERIOD

"Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"Close Year 2005";"Year 2005";"FSA_MV";"20050930"

Depending Table, Mapping Table, this is a Worksheet in the Excel
File:
FIN_PERIOD ACC
Year 2004 100
Year 2005 500
Year 2006 1000



===> Result: [this should be done with VBA]

Modified Textfile:
"ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930"

The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500


Can anyone help me ?
I tried, but no success.... i had big problems in replacing the lines
in the textfile...
How can you programming an "vlookup" in VBA code between the certain
position in the textfile and the depending table / mapping table ?


Kind regards,
Roman
Click to expand...
 
N

NickHK

There's various ways of doing this, with the emphasis either on Excel or
VBA. Your mention of >65K rows tips my solution towards VBA (assuming you
are not targetting XL2007 only).
With a file of ~ 100K rows, this completes in about 10secs. You can play
with the way the file read/write (although disk access is the slow part) and
the manner in which the look up and reassignment is done if you need to
improve performance, but you get the idea. Your should add error handling
etc.

Assuming you have a table like this in A1:B4:
FIN_PERIOD ACC
"Year 2004" "100";
"Year 2005" "500";
"Year 2006" "1000";

You can call the function
Private Sub CommandButton1_Click()
Dim RetVal As Long

RetVal = UpdateFile("C:\Test2.txt", "C:\Test2-Update.txt", Range("A2:B4"))

End Sub

Public Function UpdateFile(InFilePathName As String, OutFilePathName As
String, LookupRange As Range) As Long
Dim FinPeriodLookUp As Variant
Dim FileNum As Long
Dim Temp() As String
Dim i As Long

Set FinPeriodLookUp = LookupRange

FileNum = FreeFile
Open InFilePathName For Input As #FileNum
Temp = Split(Input(LOF(FileNum), #FileNum), vbNewLine)
Close #FileNum

For i = LBound(Temp) To UBound(Temp)
Temp(i) = Application.WorksheetFunction.VLookup(Split(Temp(i), ";")(1),
FinPeriodLookUp, 2, False) & Temp(i)
Next

FileNum = FreeFile
Open OutFilePathName For Output As #FileNum
Print #FileNum, Join(Temp, vbNewLine)
Close #FileNum

UpdateFile = i

End Function

NickHK

Romanoff said:
Hello

I need help for the following VBA-problem:

I have to insert a Value in each Line of one Textfile (file.txt).
This certain Value depends on a other Value in this Line of the
Textfile.
The depending Value is always at the same position,
The textfile is delimited with ";"

A remark, it is possible, that the Textfile has more than 65thousend
rows.


Example, Textfile: depends on FIN_PERIOD

"Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"Close Year 2005";"Year 2005";"FSA_MV";"20050930"

Depending Table, Mapping Table, this is a Worksheet in the Excel
File:
FIN_PERIOD ACC
Year 2004 100
Year 2005 500
Year 2006 1000



===> Result: [this should be done with VBA]

Modified Textfile:
"ACC"; "Time Dimension";"FIN_PERIOD";"VIEW";"REFERENCE_PERIOD";
"1000"; "Close Year 2006";"Year 2006";"FSA_MV";"20060630"
"100"; "Close Year 2004";"Year 2004";"FSA_MV";"20040331"
"500"; "Close Year 2005";"Year 2005";"FSA_MV";"20050930"

The Inserted New Attribut is "ACC" an the Value is 1000, 100 & 500


Can anyone help me ?
I tried, but no success.... i had big problems in replacing the lines
in the textfile...
How can you programming an "vlookup" in VBA code between the certain
position in the textfile and the depending table / mapping table ?


Kind regards,
Roman
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Strip records from large textfile with VBA 5
create Macro – select data, sort by acc no., yr, part no, create P 0
Combine a text and number field to create a date 2
SUM in VBA, Msgbox 3
macro - copy, paste, convert column data 3
Need big help with look up....is this possible 5
Lookup value above cells with non-zero value 1
Newbie with data retrieval questions 1

Top