Re :- Import or Open Access and / or ASCII File with Excel??

[DocAElstein]

. ---o00o---`(_)`---o00o---
'' An / To :- Mr Excel Excel Forum
'' Von / From :- Alan Elston
Re:- Import Access or ASCII File with Excel???
'' Hallo, ‹(•¿•)›
'' I `ve been getting into Excel for about a year ,and recently also into VBA for
'' a private project (calculating Daily Nutrition values to help my Wife with her Diets)
'' I’m an old Physicist, and a bit new to Computers all together, - bit of a “late starter”!!
'' I sometimes need to get at big Published data files. For me is Excel
'' good enough, and I don’t want to start learning (or buying!!) Access (Yet)........
''
'' I’m trying to get the Nutritional databank sr26 from
'' United States Department of Agriculture, which is free
'' as described in their link:
Nutrient Data : SR26 - Download Files
'' They have an abbreviated Excel download. I have
'' downloaded this and I can Open it in Excel or as a text Document
'' and I can manipulate it with the Excel Wizard and also by using VBA.
''
'' The problem is they only have the Full version as an ASCII or
'' Access file. They all download very easily. Also, (although it takes
'' very long), I can open these downloads with a text editor
'' But the resulting files look very weird,
'' I’ve tried all the options in EXCEL for importing or opening this data,
'' (Even the one option shown as “US-ASCII” ),
'' but nothing seems to give me sensible looking results.
'' (It’s probably down to my lack of experience)
'' Excel Wizard doesn’t seem to be able to do anything
'' except import it in the same weird Text form!!
''
'' Can anyone help me and suggest how I can get a sensible looking Full version
'' as a text or Excel file, without having to buy and learn Microsoft Access.
'' Ideally the final form should look like the Abbreviated Excel form
'' - just lots bigger!.
'' If that’s not possible, then any Format, that is “readable to the naked eye”
'' will do.. It will then be good practice for me to sort it out in the
'' exact Format I want using VBA !!!!
'' As this might be a question for an Access expert I’ve posted it in both
'' the Excel and Access forums.
'' (Sorry if that breaks any rules?? – I’m new here!!)
'' Thanks
'' Alan Elston, Email:- Doc.AElstein@t-online.de
'' Baveria

'' Ps.1) I’ve contacted the United States Department of Agriculture
'' but I just get the standard reply...
Nutrient Data : SR26 - Download Files
'' Ps.2) Maybe someone with Access who has a minute or two spare
'' could download it in Access and then if possible
'' use Access to make a text or Excel file and send it to me.??
'' P.s. 3) I have Excel 2007 and 2010 but no Access versions

 

[mjbeam]

I checked out the website and what I saw was that the files are zipped. I downloaded one and unzipped it and was able to open the files inside fine. Are you trying to open the zip file with a text editor?

 

[DocAElstein]

Hello mjbeam, thanks for the speedy response!


The Abbreviated Excel file comes up fine by me in Excel or in text editor...
they look “normal” :- something like this:-

NDB_No Shrt_Desc Water_(g) Energ_Kcal Protein_(g) Lipid_Tot_(g) Ash_(g) Carbohydrt_(g) Fiber_TD_(g) Sugar_Tot_(g) Calcium_(mg) Iron_(mg) Magnesium_(mg) Phosphorus_(mg) Potassium_(mg) Sodium_(mg) Zinc_(mg) Copper_(mg) Manganese_(mg) Selenium_(µg) Vit_C_(mg) Thiamin_(mg) Riboflavin_(mg) Niacin_(mg) Panto_Acid_(mg) Vit_B6_(mg) Folate_Tot_(µg) Folic_Acid_(µg) Food_Folate_(µg) Folate_DFE_(µg) Choline_Tot_(mg) Vit_B12_(µg) Vit_A_IU Vit_A_RAE_(µg)_ Retinol_(µg) Alpha_Carot_(µg) Beta_Carot_(µg) Beta_Crypt_(µg) Lycopene_(µg) Lut+Zea_(µg) Vit_E_(mg) Vit_D_µg Vit_D_IU Vit_K_(µg) FA_Sat_(g) FA_Mono_(g) FA_Poly_(g) Cholestrl_(mg) GmWt_1 GmWt_Desc1 GmWt_2 GmWt_Desc2 Refuse_Pct
01001 BUTTER,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,02 2 24 24 643 0,09 0 0 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,17 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 51,368 21,021 3,043 215 5 "1 pat, (1"" sq, 1/3"" high)" 14,2 1 tbsp 0
01002 BUTTER,WHIPPED,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,16 2 23 26 659 0,05 0,016 0,004 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,13 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 50,489 23,426 3,012 219 3,8 "1 pat, (1"" sq, 1/3"" high)" 9,4 1 tbsp 0

.... that above is for example the first 3 lines of a tab delimited file and I have no problem with that in Excel. The first line is the Headings, so I know what it all means as well

....... the other Files ASCII and ACCESS look something like this

~01001~^~203~^0.85^16^0.074^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~204~^81.11^580^0.065^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~205~^0.06^0^^~4~^~NC~^~~^~~^^^^^^^~~^11/1976^


... Theres lots of them, some take ages by me to open with a text editor. I’ve no idea how I Handel that with excel (Excell doesn’t recognise all this funny stuff “ ~^~~^~~^~~^^^^^^^~~^ “ -- for examle there are no delimiter options that look remotely like that!!) and I Cannot see any clear headings.. Maybe that comes up automatically in Access.. but I don’t have Access

.... and take a look at this:- this is the "sr26" download opende in text editor:

Standard Jet DB
µnb` ÂUé©gr@? œ~Ÿ�ÿ…š1Åyºí0¼ßÌ�cÙäßFûŠ¼NîYì75æœú®ü(æ{Š`<6{6�Ðß±VC§
±3\Ãy[ú|*£à|™˜ýv€øbzs„f_•øЉ$…gÆ'DÒîÏeíÿÇF¡xíé-bÔT 4.0 *
)































































































































































































































































d
 »v }
} 
Þ  Y <
 S     
 < < Y    \ Y     h Y 
 
 Y 
    Y      
Y
   Uþ
Y    Y
   Y 
  Y    U Y     h
Y    uþ
Y 
 2 ]þ
Y
    Y     : Y     dþ
Y 
     C o n n e c t  D a t a b a s e  D a t e C r e a t e  D a t e U p d a t e
F l a g s  F o r e i g n N a m e  I d  L v  L v E x t r a  L v M o d u l e  L v P r o p  N a m e
O w n e r  P a r e n t I d  R m t I n f o L o n g  R m t I n f o S h o r t  T y p e ƒ


ÿÿ1ÿÿ¶ÿÿhÿÿÿÿ0ÿÿÿÿÀÿÿ  Z1� ƒ
ÿÿ ÿÿeÿÿ ÿÿ\ÿÿ ÿÿiÿÿ ÿÿdÿÿ   A N � Y

ÿÿÿÿ 
Y ÿÿÿÿ   I d  P a r e n t I d N a m e     
      
 
     ÿÿ 
© O
Y µ
 S



< Y 
   
Y 
 
Y    Y
 2 þ
 A C M  F I n h e r i t a b l e  O b j e c t I d  S I D ƒ
ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ 
ˆ Y ÿÿÿÿ   O b j e c t I d ÿÿ 
ë

 Y w

 S  


 w Y
  
Y   


 ??????, -- it's probably just down to my lack of computing experience.
.. I just need a simple file in Excel or Text that looks "normal"!!

Thanks
Alan

 

[alansidman]

See my response in the Access Forum.

BTW, it is easier on the eyes if you kept all your forum posting in one font and one color. It is distracting and hard to read in your current manner.

 

[DocAElstein]

Hello, Yeah I've replied in the Access Forum.
….I'm still "at it". It looks like a more complicated problem. The sr26 Access or ASCII File is some monster of a multidimensional thing that probably only Access and an Access Expert can navigate through. In Excel I still only seem to have a small "snap-shot" of the File???
…All I want is a much bigger version of the simple 2 dimensional abbreviated Excel File. Clearly that's not that simple.
But it's probably just down to me and my lack of experience.... or I'm too far away in this Cold corner of Germany....
-- it takes me about 20 attempts to reply to your replies , and it’s Pot luck which of the Reply box , (with or without quotes) which I have to randomly pick before one of them works..
--- In the meantime I have to keep logging in time and time again!
.
--- I’ll try again to contact the United States Department of Agriculture,

( NDLInfo@ARS.USDA.GOV )

As their Excel file download they have

( Nutrient Data : SR26 - Download Files )

is in exactly the form I want (But it’s a shortened version)… I simply want the full version in the same (but bigger) form. That should not be too much to ask for ?!?
Thanks again, Hope you get my replies to your replies!!!!!

P.s. Hope the dark Blue / black colors are OK… I mean , I hope it’s a good compromise….I have a bit of a “thing with writing multicolored…. Sorry!!

 

[DocAElstein]

.............................................................................................

Hello (Again????),
. I’m replying (again??) to you as I think I’m just getting the hang of this Forum, and I’m not sure if you got my reply yesterday... I may have clicked on the wrong Reply Box!??
. in the meant time I got a bit further with a reply on the Access Forum

( http://www.mrexcel.com/forum/micros...scii-file-text-excel-file-without-access.html )

. Thanks again, sorry if you did not get my original attempt to reply to your reply..

Alan Elston





Dr. (Physik) Alan Elston
Schleizerstrasse 111
Studentenberg.
95028/Hof
Germany

Email:- The Elstons.. Doc.AElstein@t-online.de or Fahrradprinzessin@t-online.de


................................................................................................

Here is my original attempt to reply to you yesterday
Hello,
The Abbreviated Excel file comes up fine in excel or in text editor...
. they look “normal” :- something like this:-

NDB_No Shrt_Desc Water_(g) Energ_Kcal Protein_(g) Lipid_Tot_(g) Ash_(g) Carbohydrt_(g) Fiber_TD_(g) Sugar_Tot_(g) Calcium_(mg) Iron_(mg) Magnesium_(mg) Phosphorus_(mg) Potassium_(mg) Sodium_(mg) Zinc_(mg) Copper_(mg) Manganese_(mg) Selenium_(µg) Vit_C_(mg) Thiamin_(mg) Riboflavin_(mg) Niacin_(mg) Panto_Acid_(mg) Vit_B6_(mg) Folate_Tot_(µg) Folic_Acid_(µg) Food_Folate_(µg) Folate_DFE_(µg) Choline_Tot_(mg) Vit_B12_(µg) Vit_A_IU Vit_A_RAE_(µg)_ Retinol_(µg) Alpha_Carot_(µg) Beta_Carot_(µg) Beta_Crypt_(µg) Lycopene_(µg) Lut+Zea_(µg) Vit_E_(mg) Vit_D_µg Vit_D_IU Vit_K_(µg) FA_Sat_(g) FA_Mono_(g) FA_Poly_(g) Cholestrl_(mg) GmWt_1 GmWt_Desc1 GmWt_2 GmWt_Desc2 Refuse_Pct
01001 BUTTER,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,02 2 24 24 643 0,09 0 0 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,17 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 51,368 21,021 3,043 215 5 "1 pat, (1"" sq, 1/3"" high)" 14,2 1 tbsp 0
01002 BUTTER,WHIPPED,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,16 2 23 26 659 0,05 0,016 0,004 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,13 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 50,489 23,426 3,012 219 3,8 "1 pat, (1"" sq, 1/3"" high)" 9,4 1 tbsp 0

.... that’s the first 3 lines of a tab delimited file and I have no problem with that in Excel. The first line is the Headings, so I know what it all means

....... the other Files ASCII and ACCESS look something like this

~01001~^~203~^0.85^16^0.074^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~204~^81.11^580^0.065^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~205~^0.06^0^^~4~^~NC~^~~^~~^^^^^^^~~^11/1976^


... I’ve no idea how I Handel that with excel (Excell doesn’t recognise all this stuff “ ~^~~^~~^~~^^^^^^^~~^ “ as a delimiter ) and I Cannot see any clear headings.. Maybe that comes up automatically in Acces.. but I don’t have Access

 

[DocAElstein]

Hi mjbean...
when I first wrote this reply I may of not klicked in the correct reply box as I was new to Mr Excel, and had a few problems with the format etc. So just to be on the safe side, I send it agein
Alan
........

Re: Re :- Import or Open Access and / or ASCII File with Excel??

Hello mjbeam, thanks for the speedy response!


The Abbreviated Excel file comes up fine by me in Excel or in text editor...
they look “normal” :- something like this:-

NDB_No Shrt_Desc Water_(g) Energ_Kcal Protein_(g) Lipid_Tot_(g) Ash_(g) Carbohydrt_(g) Fiber_TD_(g) Sugar_Tot_(g) Calcium_(mg) Iron_(mg) Magnesium_(mg) Phosphorus_(mg) Potassium_(mg) Sodium_(mg) Zinc_(mg) Copper_(mg) Manganese_(mg) Selenium_(µg) Vit_C_(mg) Thiamin_(mg) Riboflavin_(mg) Niacin_(mg) Panto_Acid_(mg) Vit_B6_(mg) Folate_Tot_(µg) Folic_Acid_(µg) Food_Folate_(µg) Folate_DFE_(µg) Choline_Tot_(mg) Vit_B12_(µg) Vit_A_IU Vit_A_RAE_(µg)_ Retinol_(µg) Alpha_Carot_(µg) Beta_Carot_(µg) Beta_Crypt_(µg) Lycopene_(µg) Lut+Zea_(µg) Vit_E_(mg) Vit_D_µg Vit_D_IU Vit_K_(µg) FA_Sat_(g) FA_Mono_(g) FA_Poly_(g) Cholestrl_(mg) GmWt_1 GmWt_Desc1 GmWt_2 GmWt_Desc2 Refuse_Pct
01001 BUTTER,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,02 2 24 24 643 0,09 0 0 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,17 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 51,368 21,021 3,043 215 5 "1 pat, (1"" sq, 1/3"" high)" 14,2 1 tbsp 0
01002 BUTTER,WHIPPED,WITH SALT 15,87 717 0,85 81,11 2,11 0,06 0 0,06 24 0,16 2 23 26 659 0,05 0,016 0,004 1 0 0,005 0,034 0,042 0,11 0,003 3 0 3 3 18,8 0,13 2499 684 671 0 158 0 0 0 2,32 1,5 60 7 50,489 23,426 3,012 219 3,8 "1 pat, (1"" sq, 1/3"" high)" 9,4 1 tbsp 0

.... that above is for example the first 3 lines of a tab delimited file and I have no problem with that in Excel. The first line is the Headings, so I know what it all means as well

....... the other Files ASCII and ACCESS look something like this

~01001~^~203~^0.85^16^0.074^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~204~^81.11^580^0.065^~1~^~~^~~^~~^^^^^^^~~^11/1976^
~01001~^~205~^0.06^0^^~4~^~NC~^~~^~~^^^^^^^~~^11/1976^


... Theres lots of them, some take ages by me to open with a text editor. I’ve no idea how I Handel that with excel (Excell doesn’t recognise all this funny stuff “ ~^~~^~~^~~^^^^^^^~~^ “ -- for examle there are no delimiter options that look remotely like that!!) and I Cannot see any clear headings.. Maybe that comes up automatically in Access.. but I don’t have Access

.... and take a look at this:- this is the "sr26" download opende in text editor:

Standard Jet DB
µnb` ÂUé©gr@? œ~Ÿ�ÿ…š1Åyºí0¼ßÌ�cÙäßFûŠ¼NîYì75æœú®ü(æ{Š`<6{6�Ðß±VC§
±3\Ãy[ú|*£à|™˜ýv€øbzs„f_•øЉ$…gÆ'DÒîÏeíÿÇF¡xíé-bÔT 4.0 *
)































































































































































































































































d
 »v }
} 
Þ  Y <
 S     
 < < Y    \ Y     h Y 
 
 Y 
    Y      
Y
   Uþ
Y    Y
   Y 
  Y    U Y     h
Y    uþ
Y 
 2 ]þ
Y
    Y     : Y     dþ
Y 
     C o n n e c t  D a t a b a s e  D a t e C r e a t e  D a t e U p d a t e
F l a g s  F o r e i g n N a m e  I d  L v  L v E x t r a  L v M o d u l e  L v P r o p  N a m e
O w n e r  P a r e n t I d  R m t I n f o L o n g  R m t I n f o S h o r t  T y p e ƒ


ÿÿ1ÿÿ¶ÿÿhÿÿÿÿ0ÿÿÿÿÀÿÿ  Z1� ƒ
ÿÿ ÿÿeÿÿ ÿÿ\ÿÿ ÿÿiÿÿ ÿÿdÿÿ   A N � Y

ÿÿÿÿ 
Y ÿÿÿÿ   I d  P a r e n t I d N a m e     
      
 
     ÿÿ 
© O
Y µ
 S



< Y 
   
Y 
 
Y    Y
 2 þ
 A C M  F I n h e r i t a b l e  O b j e c t I d  S I D ƒ
ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ ÿÿ 
ˆ Y ÿÿÿÿ   O b j e c t I d ÿÿ 
ë

 Y w

 S  


 w Y
  
Y   


??????, -- it's probably just down to my lack of computing experience.
.. I just need a simple file in Excel or Text that looks "normal"!!

Thanks
Alan​

 

[mjbeam]

I can't reproduce your problem. The files open fine for me. Could you give me one specific file that looks like this for you so I can check it out?

 

[DocAElstein]

Hi
. Thanks for the reply
. I think the main problem is that I’m a bit old and had a long break in Computing. I'm just about getting competent in Excel and <acronym>VBA</acronym>, but I know nothing about relational databases, RDBMS, .mdb files, Access and Access files
. – I’m stuck in the time when everything just looked like a big Table, with lots Rows and Columns (remember them?) - basically just like the excel “abbreviated table” , ABBREV.xlsx that you get from the United States Department of Agriculture download site ( Nutrient Data : SR26 - Download Files) when you click on“ ...... Excel (3.2Mb)…. .....” )
So basically I just want the full version in the same form as the abbreviated ABRREV file. (But it will have a lot more food products in the first column , and there will be the entire range of Minerals, sugars , vitamins etc etc in the other columns)

. I have a thread with an Access expert on

http://www.mrexcel.com/forum/micros...scii-file-text-excel-file-without-access.html

But he hasn’t been able to help me yet.

I’ll try tomorrow (It is very late here now in Germany) to send you some files... but I’m not sure yet how I can do that yet !!!!

Alan





Dr. (Physik) Alan Elston
Schleizerstrasse 111
Studentenberg.
95028/Hof
Germany

Email:- The Elstons.. Doc.AElstein@t-online.de or Fahrradprinzessin@t-online.de

 

[mjbeam]

I took another look at the site and now I see what you are talking about. Sorry, sometimes I'm a little slow. I'll whip you up some code that will download that zip file that has the ASCII files in it, then extract each of the files into a separate sheet. It might take me a few hours but I'll post the code here when I'm done.