A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Importing into Excel from a Text file



 
 
Thread Tools Display Modes
  #1  
Old September 7th, 2004, 02:27 AM
Greg Gates
external usenet poster
 
Posts: n/a
Default 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  
Old September 7th, 2004, 01:36 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.