| || |
How to convert a txt file to excel based on position?
I have a txt file with more than 2000 values in a single row, I have to convert it into an excel format based on position, please help.
There is no delimiter, need to work based on position only and put the value different columns in excel.
e.g-- TXT FIle
Col#1 Col#2 Col#3
First(1-3) MiddleName(4-8) LastName(9-13)
Ram Singh Kumar
Are the column lengths a fixed formula you can follow? I have no idea how you would parse a name without a delimiter. If they are fixed it's just a matter of parsing the values with substring positions and writing that out to excel columns. Or just inserting commas and letting excel do it when you open it.
You'd have something like this in a loop until end of file.
sourcestr = "ramsinghkumar12delhiindia........................ . ............." (readln from file)
firstname = Mid(sourcestr,1,3)
middlename = Mid(sourcestr,4,5)
lastname = Mid(sourcestr,9,5)
objSheet.Cells(i, 1).Value = firstname
objSheet.Cells(i, 2).Value = middlename
objSheet.Cells(i, 3).Value = lastname
Last edited by NoUse4aName; 05-13-2014 at 03:51 PM.
Thanks for the quick response...............
as per the above method we can put the values in the excel cell.
Do we have an efficient way to match the layout of the text file based on position?
Along with the data validation I have to verify that all the values in the text file are in the pre-specified position. We are approx 20 columns/fields for which we need to confirm the values based on position and format.
Thanks in advance
Can add in another set of lines with something like if firstname = "" then report first name field blank.
Without a second source to verify against though you would never be able to tell that say the last names are in the first name field.
Thanks a lot for the response.... I got it