Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
I have a problem wich i cant seem to resolve.
Posted it before, but this is hopefully a better explanation; Have a txt file with alot of data placed in one row (50.000 rows). Across this row of data there is a constant value coming back called SHEET If imported in excel; Example: SHEET 7K295 or SHEET 6H320 etc. So find the string SHEET and when found copy the name SHEET +7K295 I want to do this in a loop (the value SHEET could exist more than 100 times in that column). And then also copy the 4th row below the string "SHEET", and the 9th, the 14th and 19th and put those values like this in excel: (4,9,14,19 = always constant so this is the same for all data) ColumA SHEET 7K295 SHEET 6H320 etc Column B data 4th row below SHEET 7K295 data 4th row below SHEET 6H320 etc Column C data 9th row below SHEET 7K295 data 9th row below SHEET 6H320 etc Column D data 14th row below SHEET 7K295 data 14th row below SHEET 6H320 etc Column E data 19th row below SHEET 7K295 data 19th row below SHEET 6H320 etc Hopefully someone can help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
I usually find problems like this arre better solved by modifying the text
file. I usually write a filter which reads one text file and write the modified text to a second file. In this case I would create a CSV file and then read the CSV file into excel after the changes are made. I would add semicolons to force excel to read data into a new column, and add carriagge returns to have excel put the data in a new row. " wrote: I have a problem wich i cant seem to resolve. Posted it before, but this is hopefully a better explanation; Have a txt file with alot of data placed in one row (50.000 rows). Across this row of data there is a constant value coming back called SHEET If imported in excel; Example: SHEET 7K295 or SHEET 6H320 etc. So find the string SHEET and when found copy the name SHEET +7K295 I want to do this in a loop (the value SHEET could exist more than 100 times in that column). And then also copy the 4th row below the string "SHEET", and the 9th, the 14th and 19th and put those values like this in excel: (4,9,14,19 = always constant so this is the same for all data) ColumA SHEET 7K295 SHEET 6H320 etc Column B data 4th row below SHEET 7K295 data 4th row below SHEET 6H320 etc Column C data 9th row below SHEET 7K295 data 9th row below SHEET 6H320 etc Column D data 14th row below SHEET 7K295 data 14th row below SHEET 6H320 etc Column E data 19th row below SHEET 7K295 data 19th row below SHEET 6H320 etc Hopefully someone can help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
Well the problem is that i have loads of these txt files, so
automation would save alot of time. Isnt it possible? Joel wrote: I usually find problems like this arre better solved by modifying the text file. I usually write a filter which reads one text file and write the modified text to a second file. In this case I would create a CSV file and then read the CSV file into excel after the changes are made. I would add semicolons to force excel to read data into a new column, and add carriagge returns to have excel put the data in a new row. " wrote: I have a problem wich i cant seem to resolve. Posted it before, but this is hopefully a better explanation; Have a txt file with alot of data placed in one row (50.000 rows). Across this row of data there is a constant value coming back called SHEET If imported in excel; Example: SHEET 7K295 or SHEET 6H320 etc. So find the string SHEET and when found copy the name SHEET +7K295 I want to do this in a loop (the value SHEET could exist more than 100 times in that column). And then also copy the 4th row below the string "SHEET", and the 9th, the 14th and 19th and put those values like this in excel: (4,9,14,19 = always constant so this is the same for all data) ColumA SHEET 7K295 SHEET 6H320 etc Column B data 4th row below SHEET 7K295 data 4th row below SHEET 6H320 etc Column C data 9th row below SHEET 7K295 data 9th row below SHEET 6H320 etc Column D data 14th row below SHEET 7K295 data 14th row below SHEET 6H320 etc Column E data 19th row below SHEET 7K295 data 19th row below SHEET 6H320 etc Hopefully someone can help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
your problem seems to be very simple to solve, but you started a new tread
and with the same failing: it is hard to understand what is realy your data structure. you are talking about data in *one* row, and looking for text *under* string SHEET what does mean *under*, is it character position, word position, cell parsed position ... please post here few top lines either of your input file, or parsed cvs file after read-in into excel, just keep in mind to anotate where lines begin/end since line wrap while posting will destroy the layout. je napisao u poruci interesnoj roups.com... I have a problem wich i cant seem to resolve. Posted it before, but this is hopefully a better explanation; Have a txt file with alot of data placed in one row (50.000 rows). Across this row of data there is a constant value coming back called SHEET |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
Thanks so much for the helping hand;
This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
Let me know if this works. I think there may be some problems with the
example you gave. change the path name, input file, and output file as needed. the program creates a csv file which can be opened in excel after the macro is run. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine 'remove spaces at beginning of line Do While Left(InputLine, 1) = " " InputLine = Mid(InputLine, 2) Loop If (Left(InputLine, 3) = "___") Then If Len(OutputLine) 0 Then OutputLine = OutputLine + ";" tswrite.WriteLine OutputLine OutputLine = "" End If 'remove underline Do While Left(InputLine, 1) = "_" InputLine = Mid(InputLine, 2) Loop OutputLine = InputLine Else OutputLine = OutputLine + "," + InputLine End If Loop If Len(OutputLine) 0 Then OutputLine = OutputLine + ";" tswrite.WriteLine OutputLine OutputLine = "" End If tswrite.Close tsread.Close Exit Sub End Sub " wrote: Thanks so much for the helping hand; This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
I didn't notice the comment at the bottom your posting that you added the
"____" to the file. I thought it was part of the data. This should work Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine If (InStr(InputLine, "TEXT") 0) Then If Len(OutPutLine) 0 Then tswrite.WriteLine OutPutLine OutPutLine = "" End If Else If Len(OutPutLine) 0 Then OutPutLine = OutPutLine + "," + InputLine Else OutPutLine = InputLine End If End If Loop tswrite.Close tsread.Close Exit Sub End Sub " wrote: Thanks so much for the helping hand; This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking
for! Can't i send you a present :)? Thanks!!! J.Lemmens Joel wrote: I didn't notice the comment at the bottom your posting that you added the "____" to the file. I thought it was part of the data. This should work Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine If (InStr(InputLine, "TEXT") 0) Then If Len(OutPutLine) 0 Then tswrite.WriteLine OutPutLine OutPutLine = "" End If Else If Len(OutPutLine) 0 Then OutPutLine = OutPutLine + "," + InputLine Else OutPutLine = InputLine End If End If Loop tswrite.Close tsread.Close Exit Sub End Sub " wrote: Thanks so much for the helping hand; This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
I like to help! It always easier to get the interface working correctly then
to try to fix the problems later on. Moving cells around in excel can become complicated and is slower then modifying the text file which is the source of your problem. " wrote: JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking for! Can't i send you a present :)? Thanks!!! J.Lemmens Joel wrote: I didn't notice the comment at the bottom your posting that you added the "____" to the file. I thought it was part of the data. This should work Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine If (InStr(InputLine, "TEXT") 0) Then If Len(OutPutLine) 0 Then tswrite.WriteLine OutPutLine OutPutLine = "" End If Else If Len(OutPutLine) 0 Then OutPutLine = OutPutLine + "," + InputLine Else OutPutLine = InputLine End If End If Loop tswrite.Close tsread.Close Exit Sub End Sub " wrote: Thanks so much for the helping hand; This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy values (loop) from TXT file to excel
It's fantastic how it works. If i didnt got this i had to do many
steps to get it right and that would cost alot of time wich can be spent more wisely. Thanks Joel! Again like to send you some flowers or something :) Joel wrote: I like to help! It always easier to get the interface working correctly then to try to fix the problems later on. Moving cells around in excel can become complicated and is slower then modifying the text file which is the source of your problem. " wrote: JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking for! Can't i send you a present :)? Thanks!!! J.Lemmens Joel wrote: I didn't notice the comment at the bottom your posting that you added the "____" to the file. I thought it was part of the data. This should work Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine If (InStr(InputLine, "TEXT") 0) Then If Len(OutPutLine) 0 Then tswrite.WriteLine OutPutLine OutPutLine = "" End If Else If Len(OutPutLine) 0 Then OutPutLine = OutPutLine + "," + InputLine Else OutPutLine = InputLine End If End If Loop tswrite.Close tsread.Close Exit Sub End Sub " wrote: Thanks so much for the helping hand; This is what the txt file looks like: _______________________________SHEET 7K294 0,2949.41 276.52,3104.61 TEXT _______________________________7K294 1.8 NORMAL 271.02,32.11 TEXT _______________________________TRANSPORTSYSTEM - A 1.5 NORMAL 193.62,32.91 TEXT _______________________________CENTER SYSTEM 1.5 NORMAL 193.42,28.61 TEXT _______________________________SFC STEP 4 - WAIT 1.5 NORMAL 193.42,24 TEXT 1256345 1.2 NORMAL 223.55,20 TEXT A 1.2 NORMAL 195.78,15 TEXT 12-12-06 1.2 NORMAL 193.22,12.1 TEXT SHEET 7K2946 etc ---------------------------------------- To understand the "important" lines to collect I have put _______________________________ in from of the data to make it more understanable in this post. As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in excel (see prev. post). Thanks so much!!! Best Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a file with only values no formulas | New Users to Excel | |||
Copy and paste special - values into new excel file | Excel Discussion (Misc queries) | |||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement | Excel Programming | |||
Copy values from file A, Find/Replace these values in File B | Excel Programming |