| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem Importing Fixed Width Data
I have a data set of about 700 entries from an old Pascal program that
was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
What column is the data in?
Can the number of sets vary or is it always 700? What is the first row of actual data? Does that vary? Are there always four rows of data from each set? Or is it based on the leading label? Is there a header row? Is/are there a/many blank row(s) separating the data sets? If so, how many? Are there always 7 numbers in each of the scores? How many spaces between numbers? Are they really spaces or another ASCII character? Post back with the answers, and I will whip you up a quicky macro that will put your data in order... or maybe request a file that you could send to me.... HTH, Bernie MS Excel MVP wrote in message ups.com... I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
As this is a text file, you could open it in Word and then apply Find
and Replace four times. For the first time you need to: Find what: <space<manual line break<space<spaceSex<space:<space Replace with: leave blank then click Replace All. <space means that you type a space, and is based on me copying your sample data into Word. The <manual line break will show as ^| and can be obtained on the Find panel by clicking "more" and then "special". Do Find and Replace again (CTRL-H) and this time just change the word "Sex" with "Raw scores", leaving the other things the same, then click Replace All. CTRL-H again, this time changing "Raw" with "T" and clicking Replace All. CTRL-H once more, this time choosing to: Find what: ID<space#<space:<space Replace with: leave blank and clicking Replace All. Doing this with your sample data gave: 10106 Female 1 4 1 0 3 6 3 53 114 65 43 64 83 42 so I would expect you to have 700 lines like this. You can do Find & Replace a final time to change double spaces to single spaces (between the numbers). Use File | Save As to save the file with a different name, but still as a text file. Start Excel and use File | Open (with "All files *.*" as the file type) and select your amended text file - in the Data Import Wizard you can specify <space as a delimiter and that should split your data as you wish. Insert a new row 1 and add your column headings. Hope this helps. Pete wrote: I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
Bernie:
Thanks for the help. It might just be easier if I can send you the file, since it is quite small. The format is consistent as follows: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 ID # : 10102 Sex : Female Raw Scores : 0 0 0 10 4 0 14 T Scores : 41 43 44 56 64 41 52 ID # : 10104 Sex : Female Raw Scores : 0 0 0 11 8 0 19 T Scores : 41 43 44 58 89 41 60 ID # : 11082 Sex : Female Raw Scores : 0 0 0 8 3 0 11 T Scores : 44 47 46 66 64 42 63 ID # : 11084 Sex : Female Raw Scores : 0 0 0 3 1 0 4 T Scores : 44 47 46 52 58 42 45 ID # : 11086 Sex : Female Raw Scores : 0 0 1 3 4 1 7 T Scores : 44 47 65 52 66 49 54 ID # : 11262 Sex : Female Raw Scores : 6 4 7 0 6 17 6 T Scores : 75 84 106 35 77 96 40 We need to pick up the ID#, Sex, each of the seven Raw Scores and each of the seven T Scores. There are always seven of each, but as you can see, they can vary from a single digit up to theree digits. I don't have an exact count on the number of data sets in this file, but the sets will vary depending on the file we are exporting. There are always four rows, with one row separating the sets. I don't know if the single space separating the scores is a true space or an ASCII character. There seems to be a couple of spaces leading up to each row, before ID#, Sex, Raw Scores and T Scores also. I hope this is the info you need, and please let me know if you would prefer to see the entire file. I appreciate the time you have taken to help me!! Ron Bernie Deitrick wrote: What column is the data in? Can the number of sets vary or is it always 700? What is the first row of actual data? Does that vary? Are there always four rows of data from each set? Or is it based on the leading label? Is there a header row? Is/are there a/many blank row(s) separating the data sets? If so, how many? Are there always 7 numbers in each of the scores? How many spaces between numbers? Are they really spaces or another ASCII character? Post back with the answers, and I will whip you up a quicky macro that will put your data in order... or maybe request a file that you could send to me.... HTH, Bernie MS Excel MVP wrote in message ups.com... I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
Your latest sample data produces the following using multiple Find and
Replace in Word: 10106 Female 1 4 1 0 3 6 3 53 114 65 43 64 83 42 10102 Female 0 0 0 10 4 0 14 41 43 44 56 64 41 52 10104 Female 0 0 0 11 8 0 19 41 43 44 58 89 41 60 11082 Female 0 0 0 8 3 0 11 44 47 46 66 64 42 63 11084 Female 0 0 0 3 1 0 4 44 47 46 52 58 42 45 11086 Female 0 0 1 3 4 1 7 44 47 65 52 66 49 54 11262 Female 6 4 7 0 6 17 6 75 84 106 35 77 96 40 This can be copied directly into column A of an Excel worksheet, then you can use Data | Text-to-columns to parse it. Hope this helps. Pete Pete_UK wrote: As this is a text file, you could open it in Word and then apply Find and Replace four times. For the first time you need to: Find what: <space<manual line break<space<spaceSex<space:<space Replace with: leave blank then click Replace All. <space means that you type a space, and is based on me copying your sample data into Word. The <manual line break will show as ^| and can be obtained on the Find panel by clicking "more" and then "special". Do Find and Replace again (CTRL-H) and this time just change the word "Sex" with "Raw scores", leaving the other things the same, then click Replace All. CTRL-H again, this time changing "Raw" with "T" and clicking Replace All. CTRL-H once more, this time choosing to: Find what: ID<space#<space:<space Replace with: leave blank and clicking Replace All. Doing this with your sample data gave: 10106 Female 1 4 1 0 3 6 3 53 114 65 43 64 83 42 so I would expect you to have 700 lines like this. You can do Find & Replace a final time to change double spaces to single spaces (between the numbers). Use File | Save As to save the file with a different name, but still as a text file. Start Excel and use File | Open (with "All files *.*" as the file type) and select your amended text file - in the Data Import Wizard you can specify <space as a delimiter and that should split your data as you wish. Insert a new row 1 and add your column headings. Hope this helps. Pete wrote: I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
Ron,
Put your list into column A of one sheet. On another sheet, starting in Cell A1 and going across Row 1, put the identifiers for the data that you want to extract/remove from your datatable: ID # : Sex : Raw Scores : T Scores : These whould be entered into A1, B1, C1, D1. Note the inclusion of the colon. Then select those cells (you can have as many as you want, as long as the strings appear within your database) and name them "Headers" (Select them, then use Insert / Names... Define...) Then select your sheet with the database of values, and run the macro below. If you cannot get it to work, I will send you a working example. HTH, Bernie MS Excel MVP Sub MakeDataBase() Dim myData As Range Dim myArea As Range Dim myCell As Range Dim i As Integer Dim myRow As Long myRow = 2 Set myData = ActiveSheet.Range("A:A"). _ SpecialCells(xlCellTypeConstants, 2) For Each myArea In myData.Areas For Each myCell In myArea For i = 1 To Range("Headers").Cells.Count If InStr(1, myCell.Value, Range("Headers"). _ Cells(1, i).Value) 0 Then Range("Headers").Parent.Cells(myRow, i).Value = _ Application.Trim(Replace(myCell.Value, _ Range("Headers").Cells(1, i).Value, "")) End If Next i Next myCell myRow = myRow + 1 Next myArea With Range("Headers").Parent ..Range("D:I").EntireColumn.Insert ..Range("C2", .Cells(Rows.Count, 3).End(xlUp)).TextToColumns _ Destination:=.Range("C2"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, _ Space:=True .Range("C1:I1").Formula = "=""Raw Score "" & Column()-2" .Range("C1:I1").Value = .Range("C1:I1").Value .Range("J2", .Cells(Rows.Count, 10).End(xlUp)).TextToColumns _ Destination:=.Range("J2"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, _ Space:=True .Range("J1:P1").Formula = "=""T Score "" & Column()-9" .Range("J1:P1").Value = .Range("J1:P1").Value End With End Sub wrote in message s.com... Bernie: Thanks for the help. It might just be easier if I can send you the file, since it is quite small. The format is consistent as follows: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 ID # : 10102 Sex : Female Raw Scores : 0 0 0 10 4 0 14 T Scores : 41 43 44 56 64 41 52 ID # : 10104 Sex : Female Raw Scores : 0 0 0 11 8 0 19 T Scores : 41 43 44 58 89 41 60 ID # : 11082 Sex : Female Raw Scores : 0 0 0 8 3 0 11 T Scores : 44 47 46 66 64 42 63 ID # : 11084 Sex : Female Raw Scores : 0 0 0 3 1 0 4 T Scores : 44 47 46 52 58 42 45 ID # : 11086 Sex : Female Raw Scores : 0 0 1 3 4 1 7 T Scores : 44 47 65 52 66 49 54 ID # : 11262 Sex : Female Raw Scores : 6 4 7 0 6 17 6 T Scores : 75 84 106 35 77 96 40 We need to pick up the ID#, Sex, each of the seven Raw Scores and each of the seven T Scores. There are always seven of each, but as you can see, they can vary from a single digit up to theree digits. I don't have an exact count on the number of data sets in this file, but the sets will vary depending on the file we are exporting. There are always four rows, with one row separating the sets. I don't know if the single space separating the scores is a true space or an ASCII character. There seems to be a couple of spaces leading up to each row, before ID#, Sex, Raw Scores and T Scores also. I hope this is the info you need, and please let me know if you would prefer to see the entire file. I appreciate the time you have taken to help me!! Ron Bernie Deitrick wrote: What column is the data in? Can the number of sets vary or is it always 700? What is the first row of actual data? Does that vary? Are there always four rows of data from each set? Or is it based on the leading label? Is there a header row? Is/are there a/many blank row(s) separating the data sets? If so, how many? Are there always 7 numbers in each of the scores? How many spaces between numbers? Are they really spaces or another ASCII character? Post back with the answers, and I will whip you up a quicky macro that will put your data in order... or maybe request a file that you could send to me.... HTH, Bernie MS Excel MVP wrote in message ups.com... I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
Thanks guys for your help!! I tried both ways and I was only able to
get Bernie's to work. But I didn't spend too much time once I had a working version. I think there are differences in the spacing in my original file and what I pasted into the newsgroup. I will keep trying for curiosity sake in my free time adjusting the Find setting. I appreciate all the time you've invested to solve my problem!! Bernie Deitrick wrote: Ron, Put your list into column A of one sheet. On another sheet, starting in Cell A1 and going across Row 1, put the identifiers for the data that you want to extract/remove from your datatable: ID # : Sex : Raw Scores : T Scores : These whould be entered into A1, B1, C1, D1. Note the inclusion of the colon. Then select those cells (you can have as many as you want, as long as the strings appear within your database) and name them "Headers" (Select them, then use Insert / Names... Define...) Then select your sheet with the database of values, and run the macro below. If you cannot get it to work, I will send you a working example. HTH, Bernie MS Excel MVP Sub MakeDataBase() Dim myData As Range Dim myArea As Range Dim myCell As Range Dim i As Integer Dim myRow As Long myRow = 2 Set myData = ActiveSheet.Range("A:A"). _ SpecialCells(xlCellTypeConstants, 2) For Each myArea In myData.Areas For Each myCell In myArea For i = 1 To Range("Headers").Cells.Count If InStr(1, myCell.Value, Range("Headers"). _ Cells(1, i).Value) 0 Then Range("Headers").Parent.Cells(myRow, i).Value = _ Application.Trim(Replace(myCell.Value, _ Range("Headers").Cells(1, i).Value, "")) End If Next i Next myCell myRow = myRow + 1 Next myArea With Range("Headers").Parent .Range("D:I").EntireColumn.Insert .Range("C2", .Cells(Rows.Count, 3).End(xlUp)).TextToColumns _ Destination:=.Range("C2"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, _ Space:=True .Range("C1:I1").Formula = "=""Raw Score "" & Column()-2" .Range("C1:I1").Value = .Range("C1:I1").Value .Range("J2", .Cells(Rows.Count, 10).End(xlUp)).TextToColumns _ Destination:=.Range("J2"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, _ Space:=True .Range("J1:P1").Formula = "=""T Score "" & Column()-9" .Range("J1:P1").Value = .Range("J1:P1").Value End With End Sub wrote in message s.com... Bernie: Thanks for the help. It might just be easier if I can send you the file, since it is quite small. The format is consistent as follows: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 ID # : 10102 Sex : Female Raw Scores : 0 0 0 10 4 0 14 T Scores : 41 43 44 56 64 41 52 ID # : 10104 Sex : Female Raw Scores : 0 0 0 11 8 0 19 T Scores : 41 43 44 58 89 41 60 ID # : 11082 Sex : Female Raw Scores : 0 0 0 8 3 0 11 T Scores : 44 47 46 66 64 42 63 ID # : 11084 Sex : Female Raw Scores : 0 0 0 3 1 0 4 T Scores : 44 47 46 52 58 42 45 ID # : 11086 Sex : Female Raw Scores : 0 0 1 3 4 1 7 T Scores : 44 47 65 52 66 49 54 ID # : 11262 Sex : Female Raw Scores : 6 4 7 0 6 17 6 T Scores : 75 84 106 35 77 96 40 We need to pick up the ID#, Sex, each of the seven Raw Scores and each of the seven T Scores. There are always seven of each, but as you can see, they can vary from a single digit up to theree digits. I don't have an exact count on the number of data sets in this file, but the sets will vary depending on the file we are exporting. There are always four rows, with one row separating the sets. I don't know if the single space separating the scores is a true space or an ASCII character. There seems to be a couple of spaces leading up to each row, before ID#, Sex, Raw Scores and T Scores also. I hope this is the info you need, and please let me know if you would prefer to see the entire file. I appreciate the time you have taken to help me!! Ron Bernie Deitrick wrote: What column is the data in? Can the number of sets vary or is it always 700? What is the first row of actual data? Does that vary? Are there always four rows of data from each set? Or is it based on the leading label? Is there a header row? Is/are there a/many blank row(s) separating the data sets? If so, how many? Are there always 7 numbers in each of the scores? How many spaces between numbers? Are they really spaces or another ASCII character? Post back with the answers, and I will whip you up a quicky macro that will put your data in order... or maybe request a file that you could send to me.... HTH, Bernie MS Excel MVP wrote in message ups.com... I have a data set of about 700 entries from an old Pascal program that was exported to a text file in this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Since the format is inverted, Excel cannot import is correctly. Is there a script that will accomplish this? Thank you for your help!! |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Problem Importing Fixed Width Data
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro question | Excel Worksheet Functions | |||
| Changing fixed width (column break) defaults when importing text | Excel Discussion (Misc queries) | |||
| Importing file with fixed width, multi-line records | Excel Discussion (Misc queries) | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| Problem with external data | Excel Discussion (Misc queries) | |||
Linear Mode
