Importing into Excel from a Text file

[Greg Gates]

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/shoppingcartimages/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/PCAs/10091108014kw300w.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/shoppingcartimages/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/PCAs/10091108014kw300w.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/shoppingcartimages/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/PCAs/10091108014kw300w.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/dimpled-w-milgrain-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/dimpled-int-mil-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/hammered-tight-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/band-finishes/hammered-center-milgrain-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/band-finishes/hammered-center-milgrain-ed.gif

SoftGoodControl: ::0:0
End Product
;

 

[Dave Peterson]

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???