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 have the code written and I'm running a test now. Some of the files are pretty big so it is taking quite some time to parse them. It may take a few hours to run. If I understand correctly, in the end what you want to do is add the extended nutrition information to the Abbreviated Excel file? Is this correct? If so, I'll take a look at the results after it has parsed all of the files into the workbook and see how we can merge that data into the abbreviated file. If all goes well, you should be able to download the abbreviated file, add a Module to it and insert the code I will provide into the module, then run the code. The code will download the Zip file, unzip the ASCII files, convert them into something readable, then merge the extended nutrition information into the abbreviated data that is there now.

I have to work tomorrow so I'm off to bed and will let it run tonight. I'll take a look in the morning and see what we have.

 

[DocAElstein]

Hi
. Thanks a lot for all your efforts. I am very grateful.
. Because of my lack of experience I see no way to send you through “MrExcel” any files that I have***** (Typically I always send everything as an attachment in an Email)..... But it sounds as though you are doing fine already and have got the point very quickly…
>>>>>>>>>>>>>” If I understand correctly, in the end what you want to do is add the extended nutrition information to the Abbreviated Excel file? Is this correct? “ . >>>>>> YES I think that is exactly wot I want!! Or very close:-... .... ,According to the info from United States Department of Agriculture (USDA) Agricultural Research Service, internet site, ( see 1) under ), the abbreviated file according to them .... “ file contains data for all food items in SR26, but not all nutrient values “ .. However... If you look at this file there are 7539 entries for foods (with 7540 rows in total including the first Header Row. ). However in the .pdf Document ( that is also obtainable from the same Web site) they are talking from .... “ Number of Foods in the Database (n = 8,463) “ So there is some inconsistency there!?! .. -So the number i guess is somewhere around 8000, but because of this inconsistancy there may be slight differences in the number of foods and / or the order that they are presented in the abbreviated and full file. This could make "merging" a problem !
. I have contacted the USDA many times now but all anybody is able to do is refer me back to the web site.
.
. Incidentally I am not necessarily interested in statistical data etc. - I only need the nutritional value. There are 51 of these in the abbreviated file. (53 Columns in total: 51 nutrients; a Short description of the food ; and a “NDB_No”). The USDA .pdf info is not too clear, but I estimate in the full file that I am trying to achieve there would be about 148 Nutrients, but as I am working on a private project ( to help my Wife with a necessary very strict Diet. .. -I want to check as many of her nutrients as possible as she is only able to eat a very small amount).... it would not be a problem if a few of the more exotic Nutrients were missing!!)

At the end of the day a table in Excel or probably easier (to send because of size) a simple text file (in CSV or similar) with around 8000 rows (foods) and around 100- 150 columns (nutrients) is what I am looking for. Picking out or sorting of the data I am happy to do with Excel and VBA. It would however be very helpful if the first row was a header row to show what is in every column - The order of the columns and rows are not too important as I can through VBA change that (or if I imported it with the Excel Wizzard I could simply cut and paste columns, rows etc. To suit the order I want)

Thanks again
Alan



*****P.s. The files that I have are
1) All the downloads from the USDA web site ( Nutrient Data : SR26 - Download Files )
2) An Excel file (34,45MB) with 19 Tables ( 19 sheets ) which I made from importing all the ACCESS files from USDA site with my Excel using the instructions given to me by Alan Sidman initially ( http://www.mrexcel.com/forum/micros...scii-file-text-excel-file-without-access.html ). This file “looks” Ok, but appears to be only a very small sample of what I need.
It also has lots of additional statistical and file organising info that I don’t need (or rather don’t understand!!) (In addition there is also there in one sheet an extra copy of the abbreviate file)
3) Various files sent to me by Alan Sidman in an attempt to give me a full list. Unfortunately they all look similar to what I achieved in 2)
4) Alan Sidman’s last attempt (which may be still available in the on this link ( FileSnack | Easy file sharing ) ). Unfortunately this is just a 270KB excel file with only one Nutrient (vitamine K) for only 4957 Foods



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

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

 

[mjbeam]

I looked through the PDF file and it provides enough info about each text file that it should not be a problem to merge the data as you described above. The format of the text files is not as complicated as it appears. It is similar to a csv file except it uses (^) as a delimiter instead of a comma (,). Each field (if it is not empty) has a leading and trailing tilde character (~). So the code I wrote parses each line into columns using (^) as a field delimiter, then strips off the leading and training tilde (~) if they exist. I'll also add some code to add column headers based on the info in the PDF file.

It might be best if I get the data into an Excel file and put it on Google Drive where you can access it as an .xlsx file. That way you won't have to wait several hours for the code to parse everything. I'll post the code here in the forum for those that are interested.

 

[mjbeam]

I added each of the text files to the abbreviated sheet, converted them into columns, and added the column header descriptions from the PDF file. I emailed you a link to the Excel file that I put on a Google drive. The file is .xlsx with no macros.

The method I used to convert the files is:

1. Open the file in Excel.
2. Run a macro to convert the data to columns.
3. Delete column A
4. Insert a row and add the column descriptions from the PDF file.

The macro I used to convert the data into columns is:

rc = ActiveSheet.UsedRange.Rows.Count
r = 1
Do
    Application.StatusBar = "Processing row: " & r & " of " & rc
    t = Cells(r, 1)
    c = 2
    Do
        If InStr(t, "^") > 0 Then
            s = Left(t, InStr(t, "^") - 1)
            t = Mid(t, InStr(t, "^") + 1)
        Else
            s = t
            t = ""
        End If
        If Left(s, 1) = "~" Then s = Mid(s, 2)
        If Right(s, 1) = "~" Then s = Left(s, Len(s) - 1)
        Cells(r, c) = s
        c = c + 1
        DoEvents
    Loop Until t = ""
    r = r + 1
    DoEvents
Loop Until Cells(r, 1) = ""

Application.StatusBar = False

 

[DocAElstein]

Hi Mike,

I’ve had a good look now at everything that you sent me. The code is great. Thanks. I learnt a lot from it. As a practice, I used it along with approximately your Method to produce a file:-

1. The Code!!!!

Firstly I imported the USDA sr26abbr_ASCII 1.0Mb ABBREV Text file with the Excel Wizzard ( >> data >> from Text ..etc ). I put the data in the first column, then a ran your code and... Brilliant!!! -- It worked perfectly and I had a file like the abbreviated USDA Excel file sr26abxl_Excel 3.2Mb. I was dead impressed. I sorted it a bit differently simply using the excel sort function and I was still not sure how you got the headers, but they are the same as in all the abbreviated files so that’s no problem

1. Your link https://drive.google.com/file/d/0B_GymH4iZibVV1BubEFEVFFUZG8/edit?usp=sharing ( File ABBREV_Extra.xlsx ). This gives me a file similar to the file that I produced with the excel Wizard following Alan Sidman’s Method as I explained previously. It is a big file with lots of info in it but I cannot do much with it as I only am interested in the nutrition Values for every food. These files have lots of nutritional information and statistics etc, but only for a small sample of the food and nutrition values.

1. Your Link https://drive.google.com/file/d/0B_GymH4iZibVbDRKdnBUSHEzbmc/edit?usp=sharing ( File Full.xlsx ) It took me a while before I got the point on this one!! Sorry! The problem was that it comes up by me in a strange Google Window format and you only see a small bit of info displayed in a number of peculiar looking sheets (with only a small bit of the information in them). It is only after I saved it and re-opened it that it “transformed” into a normal Excel file. I then saw that it was indeed in exactly the format I need with approximately 150 column with the headings of all the nutrition values in the first row, and all the Food names listed in the first ( or second ) column. The only problem is... most of the data is missing!? That is to say most of the columns are empty. Possibly again I’ve missed something obvious?

But I’ll keep at it and have another good look tomorrow to see if I missed anything, and thanks again for all your efforts.

Alan and Petra
Hof,
Germany


P.s I will try to send you the files that I have over Email. But some are very large and I think the German Telekom sticks them in some intermediate place where you have to access them.. But I practiced by sending them to my Wife’s Email address. She got them after a bit of playing about.. But she’s only in the next room.. And it might be a bit different going from this remote part of Bavaria all the way over to you!! I’ll send one at a time to increase the chance of success!!!

 

[mjbeam]

Hello again Alan.

Columns H:FA contain all 150 of the possible nutrition items (from the file NUTR_DEF.txt). The foods that don't have a particular nutrition item in them will not have a value in that column. For example, Fluoride. You will find it in the NUTR_DEF.txt file which defines all of the nutrition items, does have Fluoride(nutrition ID number 313), but you won't find it anywhere in the NUT_DATA.txt file. Therefore, that column is empty in the final compiled workbook.

 

[DocAElstein]

Hi Mike,
. I think I’ve finally got it!!
. Pages 9 – 11 of the PDF File from USDA ( sr26_doc_PDF File.pdf or sr26_doc.pdf ) explains that for most food products all of the important Nutritional values that I need ( The usual Kcalories, Fat, Protein, Carbohydrate, Sugar, Fiber, and most of the Vitamins and Minerals ) are in fact given. I just did not see them at first glance, because there is so much other information that I do not need. They are all there in the either the NUT_DATA.txt file, or in the sheet with that name NUT_DATA in the Excel File that you made for me ( ABBREV_Extra from Mike.xlsx or ABBREV_Extra.xlsx ). (Also the same information is in one of the sheets that I made from the ACCESS data using Alan Sidman’s first instructions to me.!!!)
. So the final File I need would look just like last one you sent me ( Full from Mike.xlsx or Full.xlsx ) but with all the data in it, that is to say it would be mostly full.
. So I need to get my head down and practice my (poor!) VBA skills and write a code to sort that data from NUT_DATA into a Table that has the same form (or Similar – I might change the Column order to suit all of my earlier work) to that in your Excel Full file ( Full from Mike.xlsx or Full.xlsx ).
. I’m dead keen to do that now, but unfortunately I have to do something else for a few days. When I’ve done it, I’ll send you a copy, just for fun.
. I think I’m almost there now and I would probably still be at it for a few months without your help. So thanks again. Sorry if I was a bit slow at getting the point sometimes

Alan.

 

[mjbeam]

I just finished it this morning and thought I saved it to the Google drive before I ran out the door to go to work, but I just logged in here at work and the file is not there. I'll send it to you when I get home tonight.

 

[DocAElstein]

Wow!!, Thanks for that download Mike!!! (In my day as an Physics Engineer we’d have said “ That’s the Dog’s Bo**oc*s!!” !!) .... It’s exactly the end result that I’ve been trying to get at for some time now. I thought I’d still be at it for a while! (I can practice my VBA on something else now.!) .................Thanks, Danke. Alan, Hof, Germany