If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing into Excel from a Text file
We have a text database of about 7,000 rings. It is in a fixed format.
Unfortunately some where we have about 4 or five rings whose format is screwed up. We figured by importing to Excel, we should be able to find where the records are to fix them. But, we just can't get the following format to import into excel. If anyone can help us, we sure would appreciate it! Thank you Greg Gates www.ringdesigner.com Each record begins with Begin Product Each record endswith EndProduct a semicolin seperates each record Also there are para symbols at the end of every line Herer are a few sample records: egin Product 10091108018kybs2VSu 18K Yellow Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds under 1.01CT 2033.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 100912859Pbs2VSu Platinum Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds under 1.01CT 2900.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108014kybs2VSo 14K Gold Two Tone Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2299.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108014kwbs2VSo 14K White Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2399.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108018kybs2VSo 18K Yellow Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2599.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009128590Pbs2VSo Platinum Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 3199.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpFinD1 Add a custom Dimpled-finish to the entire surface of a band (the band is purchased separately) 275.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes/dimpled01.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpFinD2 Add a custom Dimpled-finish to the entire surface of a band not as deep as FinD1 (the band is purchased separately) 275.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes/FinD2.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpmilFinD2 Add a custom Dimpled-finish to the entire surface of a band. Also add a Milgrain outer edge to the band (the band is purchased separately) 345.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...grain-edge.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009Ldimpmil2FinD3 Add a custom Dimpled-larger style finish to the entire surface of the band bounded by two Milgrain interior edges while finishing each exterior edge in high polish (the band is purchased separately) 375.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...il-poledge.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH1 Add a custom Hammered-finish to the entire surface of a band (the band is purchased separately) 330.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...-finish399.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH2 Add a custom Hammered-finish with the center a hammered finish with 2 Milgrain rows bounded by high polished edges (the band is purchased separately) 375.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/bands/ba...ilgrain-ed.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH4 Add a custom Hammered-finish with the center a hammered finish bounded by 2 Milgrain rows (the band is purchased separately) 345.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/bands/ba...ilgrain-ed.gif SoftGoodControl: ::0:0 End Product ; |
#2
|
|||
|
|||
Your sample data got hit by linewrap, but I'm guessing that those long
description lines actually appear in only one line in your text file. If that's the case, it looks like there are 10 rows per grouping. Begin Product 7 detail lines End Product ; And the pattern repeats. If this is the case, then I'd import the data into column A1:A???? Just file|open and select your file, but shiftclick on the open button and you won't see that text to columns wizard. Then put this formula into B1: =TRIM(INDEX($A:$A,(10*(ROW()-1)+COLUMN()-1))) Drag across to column I (8 columns--but I skipped the "end product" and semicolon line. Then drag down until you run out of data. If the results of the formulas look good, Select B:I edit|copy Edit|paste special|values delete column A And start fixing up the data. select column A and Edit|replace what: begin_product_ -- _ means space character (watch the trailing space!) with: (leave empty) replace all It looks like 2033.00 0.00 0.00 0.00 Kinds of cells should really be two cells each) Select column D and insert a new column D Select column C and data|text to columns delimited (by space) and plop down into C and the new column D (Format|cells|Number tab if you want to make them pretty). Now insert a new column F and do the same thing with column E. Another Edit|Replace for Column G? what: _;_Tracking_Inventory? -- _ means space character with: (leave blank) replace all Column I was empty with my test data. I put this formula in I1 and dragged down: =hyperlink(H1) (and I hid column H) And I'm not sure what's in column J. Edit|replace followed by data|text to columns??? Greg Gates wrote: We have a text database of about 7,000 rings. It is in a fixed format. Unfortunately some where we have about 4 or five rings whose format is screwed up. We figured by importing to Excel, we should be able to find where the records are to fix them. But, we just can't get the following format to import into excel. If anyone can help us, we sure would appreciate it! Thank you Greg Gates www.ringdesigner.com Each record begins with Begin Product Each record endswith EndProduct a semicolin seperates each record Also there are para symbols at the end of every line Herer are a few sample records: egin Product 10091108018kybs2VSu 18K Yellow Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds under 1.01CT 2033.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 100912859Pbs2VSu Platinum Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds under 1.01CT 2900.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108014kybs2VSo 14K Gold Two Tone Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2299.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108014kwbs2VSo 14K White Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2399.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 10091108018kybs2VSo 18K Yellow Gold Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 2599.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/shopping...es/11080sg.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009128590Pbs2VSo Platinum Bridal Set Engagement Ring & two matching bands with 16 Additional High Quality VS Accent Diamonds totaling 0.76CT for round center diamonds over 1.01CT 3199.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/images/P...8014kw300w.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpFinD1 Add a custom Dimpled-finish to the entire surface of a band (the band is purchased separately) 275.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes/dimpled01.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpFinD2 Add a custom Dimpled-finish to the entire surface of a band not as deep as FinD1 (the band is purchased separately) 275.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes/FinD2.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009dimpmilFinD2 Add a custom Dimpled-finish to the entire surface of a band. Also add a Milgrain outer edge to the band (the band is purchased separately) 345.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...grain-edge.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009Ldimpmil2FinD3 Add a custom Dimpled-larger style finish to the entire surface of the band bounded by two Milgrain interior edges while finishing each exterior edge in high polish (the band is purchased separately) 375.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...il-poledge.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH1 Add a custom Hammered-finish to the entire surface of a band (the band is purchased separately) 330.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/finishes...-finish399.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH2 Add a custom Hammered-finish with the center a hammered finish with 2 Milgrain rows bounded by high polished edges (the band is purchased separately) 375.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/bands/ba...ilgrain-ed.gif SoftGoodControl: ::0:0 End Product ; Begin Product 1009hammerFinH4 Add a custom Hammered-finish with the center a hammered finish bounded by 2 Milgrain rows (the band is purchased separately) 345.00 0.00 0.00 0.00 No ; Tracking Inventory? http://www.ringdesigner.com/bands/ba...ilgrain-ed.gif SoftGoodControl: ::0:0 End Product ; -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
importing text file into excel file using macro | annsmjarm | New Users | 1 | August 27th, 2004 04:33 PM |
Importing contacts from Excel gives only part of file | Mike Brantjes | Contacts | 1 | April 27th, 2004 01:11 PM |
Importing a text file to excel | Steve | Worksheet Functions | 1 | April 6th, 2004 04:15 PM |
converting text file to excel without delimiters | peaceful | Worksheet Functions | 2 | December 27th, 2003 04:30 PM |