1

I need to edit a text file i.e add and update the data using the macro. I have a text file having thousands of records, a separate excel sheet which gives the mapping of data in text file. For example, a record in text file looks like:

ABCDNew Delhi India

The mapping excel sheet for this is as below:

Name    4
City    10
Country 10

Which means that the 1st four characters in record give the name, next 10 give city and next 10 give the country of a particular person and so on. Now, I have to edit a record (one or two fields) for a particular name and save it in same file. Also I have add a new record in the above specified format and then save the file.

The excel sheet just gives the mapping of the fields in the text file. It tells only the field lengths. As u can see in my question, the text is continuos without any delimiter in it. hence the excel sheet is used to identify the variuos fields in the text file. sample record is like:

ABCDDelhiIndia1100019876543210

and the mapping is:

Name 4 City 5 Country 5 PinCode 6 PhnNo 10

The mapping is same for all records in a file. Its just gives a way how the records can be read from a file.

Any Ideas on how can I do this in simplest way?

Improve this question
1
  • I have tried it using Microsoft access database. I loaded the data in access DB and edited the record there. That worked fine as per the requirements but access DB consumes some 800 Mbs of space. Do I want to make the changes directly into the file. Commented Oct 21, 2013 at 9:04

2 Answers 2

Reset to default
2

You can have some delimiter between Name, City and Country for example : ABCD:New Delhi:India. Having delimiter can allow you to separate name, city and country. Now you can easily determine the length of each strings. Now based on your requirements you can easily edit the data and write to the file. You can refer the below code for reference : Text File : exceltest abcd:new delhi:india test:NY:USA

Macro Code : 

Sub readTextFile()

Dim oFSO As New FileSystemObject

Dim oFS

Dim objFSO As New FileSystemObject

Set myTemp = objFSO.CreateTextFile("C:\Users\Rohit\Desktop\exceltestTemp.txt", True)

Set oFS = oFSO.OpenTextFile("C:\Users\Rohit\Desktop\exceltest.txt")

Do Until oFS.AtEndOfStream

stext = oFS.ReadLine

Dim testarray() As String

testarray = Split(stext, ":")

Dim i As Integer 

i = Len(testarray(0)) 'determine length of each String, here as a sample only length of one string is determined

Cells(4, "a").Value = i 'writing a length to excel sheet, you can write to any location

Cells(1, "a").Value = testarray(0) 'writing contents to excel sheet

Cells(2, "a").Value = testarray(1) 'writing contents to excel sheet

Cells(3, "a").Value = testarray(2) 'writing contents to excel sheet

'Search for the Name

If testarray(0) = "test" Then

testarray(1) = "Mumbai"

End If

myTemp.WriteLine testarray(0) & ":" & testarray(1) & ":" & testarray(2) 'writing to a temp text file

Loop

End Sub

Now based on your requirements you can search for the name and edit the data and write to a file. You can then delete the original file and save the temp file as the original file.

PS : Tried on Excel 2007. You need to add reference "Microsoft Scripting Runtime".

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

seems to me like the OP wants to edit and update the txt file and not only create a new one.
Hi ... Thanks for your help. I really appreciate your support but i have a concern here. I cannot have any delimiter between the fields in the file as i have more than 50 fields in a file and there are more than 20 files. Is there any other alternative in a way that i can use the field length and position information from the separate mapping sheet i have.
Hi.. First I want to know how are you mapping a data in a text file with the excel sheet. Is there any way to uniquely identify the mapping. Like : which line of data in text file belongs to which row in a sheet. If possible can you give me some sample data so that I can work upon it. Thank You!
The excel sheet just gives the mapping of the fields in the text file. The mapping is same for all records in a file. Its just gives a way how a record can be read from a file. Hope this helps ...
Sorry I can get your problem exactly. Need more description or some sample data. Thank You.
|
0

What you have described is fixed width data. This is straight forward to import the data as a text file in excel using the 'fixed width' option in the import dialog box. You can then select the start and end for each field. The trickier part is to export it to the same format. The only way I know of to achieve this is via VBA. It seems like it would be straight forward routine to write though, it has probably been written many, many times.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.