Re :- Convert Access or ASCII File to Text or Excel File( Without ACCESS???)??

[DocAElstein]

---o00o---`(_)`---o00o---
'' An / To :- Mr Excel Access 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 Excel expert I’ve posted it in both
'' the Access and Excel 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

 

[alansidman]

After downloading the Access (sr26.mdb) file and unzipping it, open Excel and on the ribbon, select Data. External and select From Access. Select the sr26.mdb file and then select the table or query you wish to see. You should be able to see the data in Excel and save it as an excel file. Repeat for as many queries or tables you wish to view.

Alan

 

[DocAElstein]

'' An / To :- alansidman
'' Von / From :- Alan Elston
Hi, Thanks Alan,
I’m not sure what “unzipping” is, but I noticed when I copied the Access sr26 file from in the Folder with the Zip on it to outside this folder, it took a while , so maybe it was being “unzipped”!! Automatically then!?
Anyways I then followed your instructions:- accesed this copied file by the method you said, and selected the entire 19 “Queir”things and Tabels one after the other and they all came up and look normal. So thanks.
Although it looks normal I only seem to have a small sample of the entire data? – Each Tabel looks like just one typical example of what, should be, a total of about 4000 Food products.......?! ... maybe you only get the entire data with Access and with Excel you just get a quick “snap shot” of one product???

 

[alansidman]

You should be getting the entire table or the results of the query if you should choose that. Excel is actually reading the Access file that you downloaded.

 

[alansidman]

I have exported all the tables and queries from Access to Excel for you. I have put them in a zipped file and posted them to a file sharing site. Here is the link to that folder

FileSnack | Easy file sharing

Some of the tables may appear to have little data in them as this is how RDBMS systems are set up. Look at this link on Fundamentals of relational databases. It will help you to understand. You probably really don't need to look at the tables, but rather look at the files that are indicated as queries.

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf


This link will show relationships between the tables used in the data base.

FileSnack | Easy file sharing

EDIT: I just opened one of the queries in Access and now see what your issue is. The query was filtered for only one item. So my downloads will not help you. I will attempt to clear the parameters for one query and see if I can upload that for you later.

 

[alansidman]

Here is one full query: FileSnack | Easy file sharing

 

[DocAElstein]

From Alan
To Alan

. Thanks for all your efforts, I appreciate it.

. I’ll sit down later tonight (I’m in Germany and on a different time scale to you) and carefully go through everything you’ve said and sent.

. 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 VBA, but I know nothing about relational databases, RDBMS, .mdb files, Access and Access files. (In my day we would have said to that:- “ wot a load of old bol****s!” . – I’m stuck in the time when it would just of looked like a big Table, with lots Rows and Columns (remember them?) - basically just like the abbreviated table , ABBREV that you get from the United States Department of Agriculture download site ( Nutrient Data : SR26 - Download Files ) when you click on “ ...... Excel (3.2Mb)…. .....” )


. But I’m not lazy and I’m keen to learn so I’ll get me head down on it later

. Thanks again, I’ll keep at it

Alan

(P.s. I don’t even understand exactly what is meant in Computing these days by a “ query “ , we just used to “ look at it “ with our eyes ( And a query used to mean something else as well! )







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

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

 

[DocAElstein]

Hi Alan,
I’ve downloaded now everything you sent, thanks. And had just a quick look at everything. Many of the first Excel files look similar to what I obtained yesterday following your instructions then.
I’m going to print out the pdf files and have a good read of them.
The last Excel file you sent ( Food Item Query by Nutrient-ALL.xlsx ) has one nutrition ( vitamin K) for a lot of food items, but not all, and in fact has less food items than the US Department of Agriculture’s abbreviated list, ABBREV.xlsx (You also sent this list and I have downloaded that now myself from the US Department of Agriculture many times ( As I said that’s exactly the form I want to get at but just bigger with lots more food items and also the missing nutritions.) )
I’ve got no, idea how to get at all the food items or to “change the query” ( if that’s what it’s called?) to get at the other nutrition values.
But maybe I’ll get the point once I’ve read the pdf files. I’ll take them to bed with me now and try again tomorrow!!
Thanks Again
Alan

 

[alansidman]

What may be the best result for you is to find someone or a library that has a copy of Access so that you can look at the files directly. In the meantime, I will open up each of the queries that they have provided and clear the parameters and export them for you as I did with the last one. I will double check the last one to make sure I cleared all criteria (filters).

 

[alansidman]

Alan
Here are the rest of the full queries from the database
Each file has about 600,000 records

I hope this is what you need.

FileSnack | Easy file sharing

Good luck with your project,
Alan