| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing a multiline .txt file
I want to import about 1000 records ( like the 2 below) into excel to sort. What is the most efficient way to to import these to parse / format these records to have one row as a record (vba ?? ) ? I know how to import text files but it doesn't give the formatting options needed for this file. I can provide details, if you need them. Product Claim -------------------------------------------------------------------------------- ITEM DETAILS 1 MODEL NUMBER: E34FCGHJR465G CODE NUMBER: 45657878345342679 NUMBER: 004753Z MODEL: 3W45 PROD. ATTRIBUTE 3345678346 MODEL YEAR: 2002 PNC DESCRIPTION 456 4567278 PFP DESCRIPTION: MOUNT CURRENT SERV. LIFE: 15789 MILES REPAIR DATE: 20060123 PRODUCTION DATE: 20031002 SOLD DATE: 20050307 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT: 10.36 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT: 4.356 PRR NUMBER: 1 TROUBLE CODE/DESC. NOISE PRR NUMBER: 2 TROUBLE CODE/DESC. PLAY HAYON N OUVRE PAS COMPLETEMENT REMPLACER REPAIR ACTION#: 1 PART NO: 904507S000 PART QUANTITY: 1 UNIT PRICE: 18.71 REPAIR ACTION#: 2 PART NO: 904517S000 PART QUANTITY: 1 UNIT PRICE: 11.89 REPAIR ACTION#: 3 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 26.01 RECEIVER CLAIM NO: 015314060 BUSINESS TYPE PRODUCTION PART STATE CODE NY ADJSTMNT MEMO NO: 008677465 -------------------------------------------------------------------------------- ITEM DETAILS 2 MODEL NUMBER: BLJALGA327EU5A---- CODE NUMBER: 1N4BA41E34C852439 NUMBER: 118340Z MODEL: 6ZV2 PROD. ATTRIBUTE 3566892138 MODEL YEAR: 2004 PNC DESCRIPTION 84532 CASING LID TORS BAR/TAIL PFP DESCRIPTION: LID ASSY-CASING CURRENT SERV. LIFE: 6464 MILES REPAIR DATE: 20060216 PRODUCTION DATE: 20030906 SOLD DATE: 20050331 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT: 4.14 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT: 10.23 PRR NUMBER: 3 TROUBLE CODE/DESC. POOR GENERAL APPEARANCE PRR NUMBER: 4 TROUBLE CODE/DESC. INTERNAL FAILURE CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE SPECIAL ORDER PART THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE BOTTOM REPLACED THE RIGHT REAR CASING STA Y UM14AA 2 REPAIR ACTION#: 4 PART NO: 3566892138 PART QUANTITY: 1 UNIT PRICE: 6.9 REPAIR ACTION#: 5 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 14.37 RECEIVER CLAIM NO: 015316900 BUSINESS TYPE PRODUCTION PART STATE CODE MS ADJSTMNT MEMO NO: 008679905 --------------------------------------- -- schnett ------------------------------------------------------------------------ schnett's Profile: http://www.excelforum.com/member.php...o&userid=12035 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
If your data is all a fixed number of rows as per your sample, and begins in A2, then in B2 put the formula: =IF(LEFT($A2,12)="ITEM DETAILS",IF(COLUMN()=2,$A2,INDIRECT("$A"&ROW()+COL UMN()-2)),"") and formula drag that across to column AG, then (whilst still selected) formula drag that (B2:AG2) down to the end of your data. This should provide what you need on the first row of each item, check column AF To extract the new form: -note: To retain your current sequence as the final sequence: in AH1 put 1, hold the CTRL key and formula drag this to the end of your data to number each line-. Select the whole sheet, and Copy, Paste Special, Values back onto itsself Delete column A Select All data and Sort over column B, delete all blank lines. Select All data and Sort over column AH (which is now column AG) Delete columns AF and AG Hope this helps -- schnett Wrote: I want to import about 1000 records ( like the 2 below) into excel to sort. What is the most efficient way to to import these to parse / format these records to have one row as a record (vba ?? ) ? I know how to import text files but it doesn't give the formatting options needed for this file. I can provide details, if you need them. Product Claim -------------------------------------------------------------------------------- ITEM DETAILS 1 MODEL NUMBER: E34FCGHJR465G CODE NUMBER: 45657878345342679 NUMBER: 004753Z MODEL: 3W45 PROD. ATTRIBUTE 3345678346 MODEL YEAR: 2002 PNC DESCRIPTION 456 4567278 PFP DESCRIPTION: MOUNT CURRENT SERV. LIFE: 15789 MILES REPAIR DATE: 20060123 PRODUCTION DATE: 20031002 SOLD DATE: 20050307 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT: 10.36 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT: 4.356 PRR NUMBER: 1 TROUBLE CODE/DESC. NOISE PRR NUMBER: 2 TROUBLE CODE/DESC. PLAY HAYON N OUVRE PAS COMPLETEMENT REMPLACER REPAIR ACTION#: 1 PART NO: 904507S000 PART QUANTITY: 1 UNIT PRICE: 18.71 REPAIR ACTION#: 2 PART NO: 904517S000 PART QUANTITY: 1 UNIT PRICE: 11.89 REPAIR ACTION#: 3 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 26.01 RECEIVER CLAIM NO: 015314060 BUSINESS TYPE PRODUCTION PART STATE CODE NY ADJSTMNT MEMO NO: 008677465 -------------------------------------------------------------------------------- ITEM DETAILS 2 MODEL NUMBER: BLJALGA327EU5A---- CODE NUMBER: 1N4BA41E34C852439 NUMBER: 118340Z MODEL: 6ZV2 PROD. ATTRIBUTE 3566892138 MODEL YEAR: 2004 PNC DESCRIPTION 84532 CASING LID TORS BAR/TAIL PFP DESCRIPTION: LID ASSY-CASING CURRENT SERV. LIFE: 6464 MILES REPAIR DATE: 20060216 PRODUCTION DATE: 20030906 SOLD DATE: 20050331 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT: 4.14 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT: 10.23 PRR NUMBER: 3 TROUBLE CODE/DESC. POOR GENERAL APPEARANCE PRR NUMBER: 4 TROUBLE CODE/DESC. INTERNAL FAILURE CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE SPECIAL ORDER PART THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE BOTTOM REPLACED THE RIGHT REAR CASING STA Y UM14AA 2 REPAIR ACTION#: 4 PART NO: 3566892138 PART QUANTITY: 1 UNIT PRICE: 6.9 REPAIR ACTION#: 5 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 14.37 RECEIVER CLAIM NO: 015316900 BUSINESS TYPE PRODUCTION PART STATE CODE MS ADJSTMNT MEMO NO: 008679905 --------------------------------------- -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
The information provided appears to be one row of data, separated by a blank
row; usually four fields per row except Trouble Code/Desc and the last 5 or 6 rows; 1st row identify as Item Details last row identify as Adjustment Memo No You are wanting to put field names across row 1 and put data from row 2 onwards so that you can sort. We consider you need to use VBA to achieve this. If this is one off job, we believe it is more cost effective to pay some service provider to do. "schnett" wrote in message ... I want to import about 1000 records ( like the 2 below) into excel to sort. What is the most efficient way to to import these to parse / format these records to have one row as a record (vba ?? ) ? I know how to import text files but it doesn't give the formatting options needed for this file. I can provide details, if you need them. Product Claim -------------------------------------------------------------------------- ------ ITEM DETAILS 1 MODEL NUMBER: E34FCGHJR465G CODE NUMBER: 45657878345342679 NUMBER: 004753Z MODEL: 3W45 PROD. ATTRIBUTE 3345678346 MODEL YEAR: 2002 PNC DESCRIPTION 456 4567278 PFP DESCRIPTION: MOUNT CURRENT SERV. LIFE: 15789 MILES REPAIR DATE: 20060123 PRODUCTION DATE: 20031002 SOLD DATE: 20050307 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT: 10.36 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT: 4.356 PRR NUMBER: 1 TROUBLE CODE/DESC. NOISE PRR NUMBER: 2 TROUBLE CODE/DESC. PLAY HAYON N OUVRE PAS COMPLETEMENT REMPLACER REPAIR ACTION#: 1 PART NO: 904507S000 PART QUANTITY: 1 UNIT PRICE: 18.71 REPAIR ACTION#: 2 PART NO: 904517S000 PART QUANTITY: 1 UNIT PRICE: 11.89 REPAIR ACTION#: 3 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 26.01 RECEIVER CLAIM NO: 015314060 BUSINESS TYPE PRODUCTION PART STATE CODE NY ADJSTMNT MEMO NO: 008677465 -------------------------------------------------------------------------- ------ ITEM DETAILS 2 MODEL NUMBER: BLJALGA327EU5A---- CODE NUMBER: 1N4BA41E34C852439 NUMBER: 118340Z MODEL: 6ZV2 PROD. ATTRIBUTE 3566892138 MODEL YEAR: 2004 PNC DESCRIPTION 84532 CASING LID TORS BAR/TAIL PFP DESCRIPTION: LID ASSY-CASING CURRENT SERV. LIFE: 6464 MILES REPAIR DATE: 20060216 PRODUCTION DATE: 20030906 SOLD DATE: 20050331 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT: 4.14 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT: 10.23 PRR NUMBER: 3 TROUBLE CODE/DESC. POOR GENERAL APPEARANCE PRR NUMBER: 4 TROUBLE CODE/DESC. INTERNAL FAILURE CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE SPECIAL ORDER PART THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE BOTTOM REPLACED THE RIGHT REAR CASING STA Y UM14AA 2 REPAIR ACTION#: 4 PART NO: 3566892138 PART QUANTITY: 1 UNIT PRICE: 6.9 REPAIR ACTION#: 5 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 14.37 RECEIVER CLAIM NO: 015316900 BUSINESS TYPE PRODUCTION PART STATE CODE MS ADJSTMNT MEMO NO: 008679905 --------------------------------------- -- schnett ------------------------------------------------------------------------ schnett's Profile: http://www.excelforum.com/member.php...o&userid=12035 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Well spotted, I read the question but not the data. Row 1 appears to be 2 fields, sequence and Model Number but only one ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one, whilst others, like the Vendor rates, have none. Could be an interesting VB parse unless the OP could re-extract dropping the headers, I cannot see that the data will support any form of text-to-columns separation. -- PY & Associates Wrote: The information provided appears to be one row of data, separated by a blank row; usually four fields per row except Trouble Code/Desc and the last 5 or 6 rows; 1st row identify as Item Details last row identify as Adjustment Memo No You are wanting to put field names across row 1 and put data from row 2 onwards so that you can sort. We consider you need to use VBA to achieve this. If this is one off job, we believe it is more cost effective to pay some service provider to do. "schnett" wrote in message ... I want to import about 1000 records ( like the 2 below) into excel to sort. What is the most efficient way to to import these to parse / format these records to have one row as a record (vba ?? ) ? I know how to import text files but it doesn't give the formatting options needed for this file. I can provide details, if you need them. Product Claim -------------------------------------------------------------------------- ------ ITEM DETAILS 1 MODEL NUMBER: E34FCGHJR465G CODE NUMBER: 45657878345342679 NUMBER: 004753Z MODEL: 3W45 PROD. ATTRIBUTE 3345678346 MODEL YEAR: 2002 PNC DESCRIPTION 456 4567278 PFP DESCRIPTION: MOUNT CURRENT SERV. LIFE: 15789 MILES REPAIR DATE: 20060123 PRODUCTION DATE: 20031002 SOLD DATE: 20050307 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT: 10.36 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT: 4.356 PRR NUMBER: 1 TROUBLE CODE/DESC. NOISE PRR NUMBER: 2 TROUBLE CODE/DESC. PLAY HAYON N OUVRE PAS COMPLETEMENT REMPLACER REPAIR ACTION#: 1 PART NO: 904507S000 PART QUANTITY: 1 UNIT PRICE: 18.71 REPAIR ACTION#: 2 PART NO: 904517S000 PART QUANTITY: 1 UNIT PRICE: 11.89 REPAIR ACTION#: 3 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 26.01 RECEIVER CLAIM NO: 015314060 BUSINESS TYPE PRODUCTION PART STATE CODE NY ADJSTMNT MEMO NO: 008677465 -------------------------------------------------------------------------- ------ ITEM DETAILS 2 MODEL NUMBER: BLJALGA327EU5A---- CODE NUMBER: 1N4BA41E34C852439 NUMBER: 118340Z MODEL: 6ZV2 PROD. ATTRIBUTE 3566892138 MODEL YEAR: 2004 PNC DESCRIPTION 84532 CASING LID TORS BAR/TAIL PFP DESCRIPTION: LID ASSY-CASING CURRENT SERV. LIFE: 6464 MILES REPAIR DATE: 20060216 PRODUCTION DATE: 20030906 SOLD DATE: 20050331 CLAIM DATE: 20060301 CHARGE PARTS CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT: 4.14 CHARGE SPECIAL HANDLING VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: VENDOR AMOUNT: CHARGE LABOR CHARGE VENDOR RATE 0.6 TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT: 10.23 PRR NUMBER: 3 TROUBLE CODE/DESC. POOR GENERAL APPEARANCE PRR NUMBER: 4 TROUBLE CODE/DESC. INTERNAL FAILURE CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE SPECIAL ORDER PART THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE BOTTOM REPLACED THE RIGHT REAR CASING STA Y UM14AA 2 REPAIR ACTION#: 4 PART NO: 3566892138 PART QUANTITY: 1 UNIT PRICE: 6.9 REPAIR ACTION#: 5 OPERATION NO: UM14A1 LABOR HOURS: 0.2 TOTAL CHARGEBACK CLAIM AMOUNT: 14.37 RECEIVER CLAIM NO: 015316900 BUSINESS TYPE PRODUCTION PART STATE CODE MS ADJSTMNT MEMO NO: 008679905 --------------------------------------- -- schnett ------------------------------------------------------------------------ schnett's Profile: http://www.excelforum.com/member.php...o&userid=12035 View this thread: http://www.excelforum.com/showthread...hreadid=534393 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Just of interest for schnett, the attached formula (across Row 2) will show roughly what will be required in specs for parsing your data, and I guess highlight the need to, if possible, have the file supplied in a set format. Hope this assists you File:http://www.excelforum.com/attachment...3&d=1145536793 -- Bryan Hessey Wrote: Well spotted, I read the question but not the data. Row 1 appears to be 2 fields, sequence and Model Number but only one ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one, whilst others, like the Vendor rates, have none. Could be an interesting VB parse unless the OP could re-extract dropping the headers, I cannot see that the data will support any form of text-to-columns separation. -- +-------------------------------------------------------------------+ |Filename: Book1v.zip | |Download: http://www.excelforum.com/attachment.php?postid=4663 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
You should note that the field before Repair Action #1 is irregular. Who
knows what surprises lie in other set of data? "Bryan Hessey" wrote in message news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com... Just of interest for schnett, the attached formula (across Row 2) will show roughly what will be required in specs for parsing your data, and I guess highlight the need to, if possible, have the file supplied in a set format. Hope this assists you File:http://www.excelforum.com/attachment...3&d=1145536793 -- Bryan Hessey Wrote: Well spotted, I read the question but not the data. Row 1 appears to be 2 fields, sequence and Model Number but only one ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one, whilst others, like the Vendor rates, have none. Could be an interesting VB parse unless the OP could re-extract dropping the headers, I cannot see that the data will support any form of text-to-columns separation. -- +-------------------------------------------------------------------+ |Filename: Book1v.zip | |Download: http://www.excelforum.com/attachment.php?postid=4663 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Also that one line of data was split over two lines, and the second Set was 2 lines shorter than the first, hence my comment that, if possible, a more standard format would make life easier. -- PY & Associates Wrote: You should note that the field before Repair Action #1 is irregular. Who knows what surprises lie in other set of data? "Bryan Hessey" wrote in message news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com... Just of interest for schnett, the attached formula (across Row 2) will show roughly what will be required in specs for parsing your data, and I guess highlight the need to, if possible, have the file supplied in a set format. Hope this assists you File:http://www.excelforum.com/attachment...3&d=1145536793 -- Bryan Hessey Wrote: Well spotted, I read the question but not the data. Row 1 appears to be 2 fields, sequence and Model Number but only one ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one, whilst others, like the Vendor rates, have none. Could be an interesting VB parse unless the OP could re-extract dropping the headers, I cannot see that the data will support any form of text-to-columns separation. -- +-------------------------------------------------------------------+ |Filename: Book1v.zip | |Download: http://www.excelforum.com/attachment.php?postid=4663 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Also that one line of data was split over two lines, and the second Set was 2 lines shorter than the first, hence my comment that, if possible, a more standard format would make life easier. However, with only 1,000 lines it might be quicker to a 'as-is' job and manually fix it afterwards. -- PY & Associates Wrote: You should note that the field before Repair Action #1 is irregular. Who knows what surprises lie in other set of data? "Bryan Hessey" wrote in message news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com... Just of interest for schnett, the attached formula (across Row 2) will show roughly what will be required in specs for parsing your data, and I guess highlight the need to, if possible, have the file supplied in a set format. Hope this assists you File:http://www.excelforum.com/attachment...3&d=1145536793 -- Bryan Hessey Wrote: Well spotted, I read the question but not the data. Row 1 appears to be 2 fields, sequence and Model Number but only one ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one, whilst others, like the Vendor rates, have none. Could be an interesting VB parse unless the OP could re-extract dropping the headers, I cannot see that the data will support any form of text-to-columns separation. -- +-------------------------------------------------------------------+ |Filename: Book1v.zip | |Download: http://www.excelforum.com/attachment.php?postid=4663 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
I want to thank you for all that formula coding that went into that attachment sheet. This raw attachment is only a sample of the records. The records seem more regular than irregular. I tried what you had suggested and it would have worked except there is a blank space in front of all my characters. Here is the raw data file. Can you teach me what the formulas in order to extract data from these records just like you did before ? Thank you ! +-------------------------------------------------------------------+ |Filename: Spc data.txt | |Download: http://www.excelforum.com/attachment.php?postid=4668 | +-------------------------------------------------------------------+ -- schnett ------------------------------------------------------------------------ schnett's Profile: http://www.excelforum.com/member.php...o&userid=12035 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Hi, A quick play with your file, opened in MS Word, replaced " " (3 spaces) by tab, saveas DOS text. In Excel, Data, import external, in Wizard take Tab and colon : as delimiters, treat consecutive delimiters as one, into A1 That gave A to L in the attached. There were two spaces, not 1, in front of your data. Set the formula in H2 as =IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)=" ",MID(B2,2,999),IF(B2<"",B2,""))) copied across, and bulk copied down. That gave columns H to L With that range still selected, did Copy and Paste Special Values into cell N2 That gave columns N O P and Q as your partially cleaned data. Columns A to M can be deleted, they were just to show. Does this help? will try the new formula n that -- schnett Wrote: I want to thank you for all that formula coding that went into that attachment sheet. This raw attachment is only a sample of the records. The records seem more regular than irregular. I tried what you had suggested and it would have worked except there is a blank space in front of all my characters. Here is the raw data file. Can you teach me what the formulas in order to extract data from these records just like you did before ? Thank you ! +-------------------------------------------------------------------+ |Filename: Spc.zip | |Download: http://www.excelforum.com/attachment.php?postid=4669 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Sample file after following the F2 formula, copy-paste special values to a new sheet, delete columns A to E, sort = header row, column A ascending, remove all non-required rows. As previously stated the bottom portion of your sets is a little non-fixed format and some manual intervention seems required, but it may be more meaningful to you. Hope this helps -- Bryan Hessey Wrote: Hi, A quick play with your file, opened in MS Word, replaced " " (3 spaces) by tab, saveas DOS text. In Excel, Data, import external, in Wizard take Tab and colon : as delimiters, treat consecutive delimiters as one, into A1 That gave A to L in the attached. There were two spaces, not 1, in front of your data. Set the formula in H2 as =IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)=" ",MID(B2,2,999),IF(B2<"",B2,""))) copied across, and bulk copied down. That gave columns H to L With that range still selected, did Copy and Paste Special Values into cell N2 That gave columns N O P and Q as your partially cleaned data. Columns A to M can be deleted, they were just to show. Does this help? -AMENDED POST- The formula to post in F2 is =IF(LEFT($A2,12)="ITEM DETAILS",OFFSET($A$2,(INT(ROW()-2/4)-1)+INT((COLUMN()-6)/4),(MOD((COLUMN()-6),4)),1,1),"") then formula copy that to ED - then, whilst still highlighted, bulk-formula copy to end of data note, ED is one column too many, but where ED does not contain ITEM DESCRIP then an error has occurred and manual adjustment is required, sometimes by deleteing a row after joining data bits, sometimes by inserting a row. -- +-------------------------------------------------------------------+ |Filename: Spc4.zip | |Download: http://www.excelforum.com/attachment.php?postid=4671 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Hi Bryan
I studied your solution carefully, well done. I was considering an alternative approach and wish to share with you. My proposal is: use text to column, delimited with "space" and ":" on the whole file; Heading row cells(1,1)="Item details" cells(1,2)="Model nr" cells(1,3)="Code nr" etc for data row=2 to last data row now Find row number containing word "Item" (as rownr) then refer to relevant data using rownr + "nr of rows down" and colnr (which can be counted easily) cells(2,1)=cells(rownr,3) cells(2,2)=cells(rownr,6) cells(2,3)=cells(rownr+1, 3) etc next row for description field which has been broken into multiple cells, we can concatenate them back "Bryan Hessey" wrote in message news:Bryan.Hessey.26lnxb_1145598602.5195@excelforu m-nospam.com... Sample file after following the F2 formula, copy-paste special values to a new sheet, delete columns A to E, sort = header row, column A ascending, remove all non-required rows. As previously stated the bottom portion of your sets is a little non-fixed format and some manual intervention seems required, but it may be more meaningful to you. Hope this helps -- Bryan Hessey Wrote: Hi, A quick play with your file, opened in MS Word, replaced " " (3 spaces) by tab, saveas DOS text. In Excel, Data, import external, in Wizard take Tab and colon : as delimiters, treat consecutive delimiters as one, into A1 That gave A to L in the attached. There were two spaces, not 1, in front of your data. Set the formula in H2 as =IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)=" ",MID(B2,2,999),IF(B2<"",B2,""))) copied across, and bulk copied down. That gave columns H to L With that range still selected, did Copy and Paste Special Values into cell N2 That gave columns N O P and Q as your partially cleaned data. Columns A to M can be deleted, they were just to show. Does this help? -AMENDED POST- The formula to post in F2 is =IF(LEFT($A2,12)="ITEM DETAILS",OFFSET($A$2,(INT(ROW()-2/4)-1)+INT((COLUMN()-6)/4),(MOD((COLUMN()-6 ),4)),1,1),"") then formula copy that to ED - then, whilst still highlighted, bulk-formula copy to end of data note, ED is one column too many, but where ED does not contain ITEM DESCRIP then an error has occurred and manual adjustment is required, sometimes by deleteing a row after joining data bits, sometimes by inserting a row. -- +-------------------------------------------------------------------+ |Filename: Spc4.zip | |Download: http://www.excelforum.com/attachment.php?postid=4671 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
PY, Thank you for your response. Whichever option the OP chooses I think there will always be a need to manually adjust some data. In the trial data I noticed two occasions where the data had split over two lines, and with no real identifier to detect these splits (ie, the supposed next line may or may not be present) I see no easy way to automate all parts of that concatenation, plus, not all fields seem present on all items. However, with a mere 1,000 or so records it would be more difficult to code than to fix manually. Where 98% of the file is correct the OP could easily do these fixes, The data is meaningful to the OP, more easily read and problems detected, I was trying to produce a worksheet where the OP could see those changes take effect as they were made rather than setting another rule and re-importing the data. Having said that I have no objections whatsoever to other suggestions being made, after all it's the 'hive-mind' coupled with a few experts that makes a forum a successful place, so please, go ahead and make any suggestion. Whatever gets the task completed for the OP is really the only consideration, and that may be either by a more clever solution, or by a more simple solution that can be understood, adapted, and used by the OP. Bryan -- PY & Associates Wrote: Hi Bryan I studied your solution carefully, well done. I was considering an alternative approach and wish to share with you. My proposal is: use text to column, delimited with "space" and ":" on the whole file; Heading row cells(1,1)="Item details" cells(1,2)="Model nr" cells(1,3)="Code nr" etc for data row=2 to last data row now Find row number containing word "Item" (as rownr) then refer to relevant data using rownr + "nr of rows down" and colnr (which can be counted easily) cells(2,1)=cells(rownr,3) cells(2,2)=cells(rownr,6) cells(2,3)=cells(rownr+1, 3) etc next row for description field which has been broken into multiple cells, we can concatenate them back "Bryan Hessey" wrote in message news:Bryan.Hessey.26lnxb_1145598602.5195@excelforu m-nospam.com... Sample file after following the F2 formula, copy-paste special values to a new sheet, delete columns A to E, sort = header row, column A ascending, remove all non-required rows. As previously stated the bottom portion of your sets is a little non-fixed format and some manual intervention seems required, but it may be more meaningful to you. Hope this helps -- Bryan Hessey Wrote: Hi, A quick play with your file, opened in MS Word, replaced " " (3 spaces) by tab, saveas DOS text. In Excel, Data, import external, in Wizard take Tab and colon : as delimiters, treat consecutive delimiters as one, into A1 That gave A to L in the attached. There were two spaces, not 1, in front of your data. Set the formula in H2 as =IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)=" ",MID(B2,2,999),IF(B2<"",B2,""))) copied across, and bulk copied down. That gave columns H to L With that range still selected, did Copy and Paste Special Values into cell N2 That gave columns N O P and Q as your partially cleaned data. Columns A to M can be deleted, they were just to show. Does this help? -AMENDED POST- The formula to post in F2 is =IF(LEFT($A2,12)="ITEM DETAILS",OFFSET($A$2,(INT(ROW()-2/4)-1)+INT((COLUMN()-6)/4),(MOD((COLUMN()-6 ),4)),1,1),"") then formula copy that to ED - then, whilst still highlighted, bulk-formula copy to end of data note, ED is one column too many, but where ED does not contain ITEM DESCRIP then an error has occurred and manual adjustment is required, sometimes by deleteing a row after joining data bits, sometimes by inserting a row. -- +-------------------------------------------------------------------+ |Filename: Spc4.zip | |Download: http://www.excelforum.com/attachment.php?postid=4671 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing a multiline .txt file
Interesting use of the Offset
"Bryan Hessey" wrote: PY, Thank you for your response. Whichever option the OP chooses I think there will always be a need to manually adjust some data. In the trial data I noticed two occasions where the data had split over two lines, and with no real identifier to detect these splits (ie, the supposed next line may or may not be present) I see no easy way to automate all parts of that concatenation, plus, not all fields seem present on all items. However, with a mere 1,000 or so records it would be more difficult to code than to fix manually. Where 98% of the file is correct the OP could easily do these fixes, The data is meaningful to the OP, more easily read and problems detected, I was trying to produce a worksheet where the OP could see those changes take effect as they were made rather than setting another rule and re-importing the data. Having said that I have no objections whatsoever to other suggestions being made, after all it's the 'hive-mind' coupled with a few experts that makes a forum a successful place, so please, go ahead and make any suggestion. Whatever gets the task completed for the OP is really the only consideration, and that may be either by a more clever solution, or by a more simple solution that can be understood, adapted, and used by the OP. Bryan -- PY & Associates Wrote: Hi Bryan I studied your solution carefully, well done. I was considering an alternative approach and wish to share with you. My proposal is: use text to column, delimited with "space" and ":" on the whole file; Heading row cells(1,1)="Item details" cells(1,2)="Model nr" cells(1,3)="Code nr" etc for data row=2 to last data row now Find row number containing word "Item" (as rownr) then refer to relevant data using rownr + "nr of rows down" and colnr (which can be counted easily) cells(2,1)=cells(rownr,3) cells(2,2)=cells(rownr,6) cells(2,3)=cells(rownr+1, 3) etc next row for description field which has been broken into multiple cells, we can concatenate them back "Bryan Hessey" wrote in message news:Bryan.Hessey.26lnxb_1145598602.5195@excelforu m-nospam.com... Sample file after following the F2 formula, copy-paste special values to a new sheet, delete columns A to E, sort = header row, column A ascending, remove all non-required rows. As previously stated the bottom portion of your sets is a little non-fixed format and some manual intervention seems required, but it may be more meaningful to you. Hope this helps -- Bryan Hessey Wrote: Hi, A quick play with your file, opened in MS Word, replaced " " (3 spaces) by tab, saveas DOS text. In Excel, Data, import external, in Wizard take Tab and colon : as delimiters, treat consecutive delimiters as one, into A1 That gave A to L in the attached. There were two spaces, not 1, in front of your data. Set the formula in H2 as =IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)=" ",MID(B2,2,999),IF(B2<"",B2,""))) copied across, and bulk copied down. That gave columns H to L With that range still selected, did Copy and Paste Special Values into cell N2 That gave columns N O P and Q as your partially cleaned data. Columns A to M can be deleted, they were just to show. Does this help? -AMENDED POST- The formula to post in F2 is =IF(LEFT($A2,12)="ITEM DETAILS",OFFSET($A$2,(INT(ROW()-2/4)-1)+INT((COLUMN()-6)/4),(MOD((COLUMN()-6 ),4)),1,1),"") then formula copy that to ED - then, whilst still highlighted, bulk-formula copy to end of data note, ED is one column too many, but where ED does not contain ITEM DESCRIP then an error has occurred and manual adjustment is required, sometimes by deleteing a row after joining data bits, sometimes by inserting a row. -- +-------------------------------------------------------------------+ |Filename: Spc4.zip | |Download: http://www.excelforum.com/attachment.php?postid=4671 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534393 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing MM:SS From Tab Delimited File and Charting | Charts and Charting in Excel | |||
| importing csv file | Excel Discussion (Misc queries) | |||
| Formatting a .txt file / fixed width | Excel Discussion (Misc queries) | |||
| save excel file from a table delimited file (.txt) using macros | New Users to Excel | |||
| Importing .txt data files increases .xls file size | Excel Discussion (Misc queries) | |||
Linear Mode
