Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting data....
I am not sure which way would be best for this... worksheet functions of VBA
Here is the situation, I have a large text file that I need to break info out of.. This is not comma, or easyily delimited files, so I need to figure out how to do this (I have kludged something, but it takes so long and I get error messages due to memory. I KNOW there has to be a cleaner way to handle this. The data comes from a combination of 2 or 3 rows, has a varible length name at the beginning, a "code", then the next 19 items are varible length and seperated by spaces (these are the only spaces in this portion of the data, so space could be used to delimit in this section), then the rest of the string is again varible length, open text). I have a routine that assembles the data into one line. (I am not sure if it is best to extract the data, and then rejoin them in to one row, or to join the 2-3 rows of raw data and then parse. joining the lines seems to make the most sense... Here are a few samples of the data: BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31 2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK 27 BHAM REALTY COS ADD N O 4 BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00 345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000 0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31 351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000 S 90 FT LOT 17 BLK 7 OLLINGER & STE Would it be best to use a function or use programing? I am thinking it would be best to seperate the name from the rest - (I have a routine that seems to do this ok, it is based on he fact of the number right after the name, and a corralating code that I manually paste in to a seperate column (this code is based on what file the records come from) Any ideas? Thanks! Bruce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting data....
Not sure why you assemble the data into one line and break it out again.
Also a "code" we guess is "1", or "2" which forms one of the "19 items". We would tackle the problem as is thus: split the line; go through the resulting array from first element up till isnumeric; get the next 18 elements; do the rest. Cheers "Bruce" <oleexpres.at.johnsonclan.net wrote in message ... I am not sure which way would be best for this... worksheet functions of VBA Here is the situation, I have a large text file that I need to break info out of.. This is not comma, or easyily delimited files, so I need to figure out how to do this (I have kludged something, but it takes so long and I get error messages due to memory. I KNOW there has to be a cleaner way to handle this. The data comes from a combination of 2 or 3 rows, has a varible length name at the beginning, a "code", then the next 19 items are varible length and seperated by spaces (these are the only spaces in this portion of the data, so space could be used to delimit in this section), then the rest of the string is again varible length, open text). I have a routine that assembles the data into one line. (I am not sure if it is best to extract the data, and then rejoin them in to one row, or to join the 2-3 rows of raw data and then parse. joining the lines seems to make the most sense... Here are a few samples of the data: BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31 2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK 27 BHAM REALTY COS ADD N O 4 BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00 345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000 0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31 351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000 S 90 FT LOT 17 BLK 7 OLLINGER & STE Would it be best to use a function or use programing? I am thinking it would be best to seperate the name from the rest - (I have a routine that seems to do this ok, it is based on he fact of the number right after the name, and a corralating code that I manually paste in to a seperate column (this code is based on what file the records come from) Any ideas? Thanks! Bruce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting data....
"PY & Associates" wrote in message ... Not sure why you assemble the data into one line and break it out again. I feel it would be easer to parse data from one element then 2 or 3) and then break it out from that one Also a "code" we guess is "1", or "2" which forms one of the "19 items". The "code" could be between 1 and 65 We would tackle the problem as is thus: split the line; go through the resulting array from first element up till isnumeric; I did not use isnumaric because sometimes names (could be company names) have numeric in them) get the next 18 elements; What would be the best way to do this? I am thinking may be sumthing like MID(A1, find(A1," ",column()),A1," ",column()+1) which is a LOT cleaner than what I already have do the rest. Cheers "Bruce" <oleexpres.at.johnsonclan.net wrote in message ... I am not sure which way would be best for this... worksheet functions of VBA Here is the situation, I have a large text file that I need to break info out of.. This is not comma, or easyily delimited files, so I need to figure out how to do this (I have kludged something, but it takes so long and I get error messages due to memory. I KNOW there has to be a cleaner way to handle this. The data comes from a combination of 2 or 3 rows, has a varible length name at the beginning, a "code", then the next 19 items are varible length and seperated by spaces (these are the only spaces in this portion of the data, so space could be used to delimit in this section), then the rest of the string is again varible length, open text). I have a routine that assembles the data into one line. (I am not sure if it is best to extract the data, and then rejoin them in to one row, or to join the 2-3 rows of raw data and then parse. joining the lines seems to make the most sense... Here are a few samples of the data: BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31 2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK 27 BHAM REALTY COS ADD N O 4 BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00 345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000 0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31 351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000 S 90 FT LOT 17 BLK 7 OLLINGER & STE Would it be best to use a function or use programing? I am thinking it would be best to seperate the name from the rest - (I have a routine that seems to do this ok, it is based on he fact of the number right after the name, and a corralating code that I manually paste in to a seperate column (this code is based on what file the records come from) Any ideas? Thanks! Bruce |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting data....
You obviously have valid reason to combine names and codes together.
However, since you know names (could be company names) have numeric in them, and you know they are names, you can exclude them in the first instance. Alternatively, can we see more of your data please? -- Regards "Bruce" <oleexpres.at.johnsonclan.net wrote in message . .. "PY & Associates" wrote in message ... Not sure why you assemble the data into one line and break it out again. I feel it would be easer to parse data from one element then 2 or 3) and then break it out from that one Also a "code" we guess is "1", or "2" which forms one of the "19 items". The "code" could be between 1 and 65 We would tackle the problem as is thus: split the line; go through the resulting array from first element up till isnumeric; I did not use isnumaric because sometimes names (could be company names) have numeric in them) get the next 18 elements; What would be the best way to do this? I am thinking may be sumthing like MID(A1, find(A1," ",column()),A1," ",column()+1) which is a LOT cleaner than what I already have do the rest. Cheers "Bruce" <oleexpres.at.johnsonclan.net wrote in message ... I am not sure which way would be best for this... worksheet functions of VBA Here is the situation, I have a large text file that I need to break info out of.. This is not comma, or easyily delimited files, so I need to figure out how to do this (I have kludged something, but it takes so long and I get error messages due to memory. I KNOW there has to be a cleaner way to handle this. The data comes from a combination of 2 or 3 rows, has a varible length name at the beginning, a "code", then the next 19 items are varible length and seperated by spaces (these are the only spaces in this portion of the data, so space could be used to delimit in this section), then the rest of the string is again varible length, open text). I have a routine that assembles the data into one line. (I am not sure if it is best to extract the data, and then rejoin them in to one row, or to join the 2-3 rows of raw data and then parse. joining the lines seems to make the most sense... Here are a few samples of the data: BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31 2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK 27 BHAM REALTY COS ADD N O 4 BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00 345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000 0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31 351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000 S 90 FT LOT 17 BLK 7 OLLINGER & STE Would it be best to use a function or use programing? I am thinking it would be best to seperate the name from the rest - (I have a routine that seems to do this ok, it is based on he fact of the number right after the name, and a corralating code that I manually paste in to a seperate column (this code is based on what file the records come from) Any ideas? Thanks! Bruce |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help extracting data....
Bruce,
Depends what you mean by large. If <~5MB, then you read the whole file into a variable Dim FileNum As Long FileNum = FreeFile Open "C:\Multiline.txt" For Input As #FileNum Dim AllText As String AllText = Input(LOF(FileNum), #FileNum) Close #FileNum 'Assuming there is blank line between between entries, Split the records Dim AllRecords As Variant AllRecords = Split(AllText, vbNewLine & vbNewLine) 'Open a file to save the fixed output FileNum= FreeFile Open "C:\Multiline-Out.txt" For OutPut As #FileNum 'So now each element of the allrecords array contains the required data. Combine all into 1 row Dim RecordCount As Long RecordCount = UBound(AllRecords) + 1 Dim i As Long For i = 0 To RecordCount - 1 AllRecords(i) = Replace(AllRecords(i), vbNewLine, "") Debug.Print AllRecords(i) Print #FileNum, AllRecords(i) Next Close #FileNum Instead of the Debug, you would probably want a FixUp function to quote out the first (and last ?) text entries, as you seem to have already. Then you can open the file in Excel. NickHK "Bruce" <oleexpres.at.johnsonclan.net wrote in message ... I am not sure which way would be best for this... worksheet functions of VBA Here is the situation, I have a large text file that I need to break info out of.. This is not comma, or easyily delimited files, so I need to figure out how to do this (I have kludged something, but it takes so long and I get error messages due to memory. I KNOW there has to be a cleaner way to handle this. The data comes from a combination of 2 or 3 rows, has a varible length name at the beginning, a "code", then the next 19 items are varible length and seperated by spaces (these are the only spaces in this portion of the data, so space could be used to delimit in this section), then the rest of the string is again varible length, open text). I have a routine that assembles the data into one line. (I am not sure if it is best to extract the data, and then rejoin them in to one row, or to join the 2-3 rows of raw data and then parse. joining the lines seems to make the most sense... Here are a few samples of the data: BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31 2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK 27 BHAM REALTY COS ADD N O 4 BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00 345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000 0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31 351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000 S 90 FT LOT 17 BLK 7 OLLINGER & STE Would it be best to use a function or use programing? I am thinking it would be best to seperate the name from the rest - (I have a routine that seems to do this ok, it is based on he fact of the number right after the name, and a corralating code that I manually paste in to a seperate column (this code is based on what file the records come from) Any ideas? Thanks! Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Extracting data from the current date | Excel Worksheet Functions |