Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 2

According to the article, download the PDF files ‘1299012_1.pdf’ to ‘1299012_18.pdf’. Corresponding to each PDF file in PDF files, copy all text from one file and option paste to one worksheet. As a result, you would make 18 worksheets in a book. In the first tab of ‘Text File Wizard’, select option ‘The data field separated by delimiters such as comma or tab’. Go to the last tab without any change in second tab. In the last tab, change option data type of the first column to ‘String’. Mainly in column A of all worksheets, you have to fix cell value by yourself. Save the book as ‘Category.xlsm’. Furthermore, download the EXCEL book from this site, copy worksheet from it to ‘Category.xlsm’ which you previously prepared, and change the sheet name to ‘Sheet0’.

Copy or move the worksheet, which you made at Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 1, to ‘Category.xlsm’. As a result, ‘Category.xlsm’ book has 20 worksheets. Press ‘Alt’ key and ‘F11’ key to launch VBE, insert module and run the code below. The code makes ‘M_CATEGORY’ sheet.

I have counted number of modified cells. It was more than 2400. I could not write complete code without manual processing. It is the responsibility of the Ministry of Education, Culture, Sports, Science & Technology in Japan (MEXT).

References:
CSV file of the ‘Standard Tables of Food Composition in Japan 2010′
Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 1

CSV file of the ‘Standard Tables of Food Composition in Japan 2010’

I had posted the article which contains ‘M_FOODS.txt’, which is derived from the ‘Standard Tables of Food Composition in Japan 2010’, on January 12, 2012. The employee of the Ministry of Education, Culture, Sports, Science & Technology in Japan (MEXT) has asked me to inform on my blog of name and unit of each component of ‘M_FOODS.txt’ file. I have added name and unit to table head of CSV file. Then I post it on my blog.

In original PDF files, comma character is used in component as character rather than as delimiter and space character is used as delimiter. Because I could not use comma character as delimiter, I had to use tab character as delimiter. Although we use double quotes as quote with EXCEL, such other program as relational database management software may use single quotes as quote. Therefore, I didn’t use quote at all. When you open this file, don’t double click to open, please. Please use ‘Text file wizard’. In the last tab of wizard, you would had to select ‘string’ data type of first column. If you would not have followed this warning, you could not open correctly and you would be confused why the length of item number is 4 and the first ‘0’ is missing.

This file has 54 columns and 1,881 rows. The first three lines show data structure, following lines from the fourth line show data itself. Japanese name of components in the first line, English name of components in the second line and unit in the third line. It means ‘g’ as gram, ‘mg’ as milligram and ‘microgram’ as micro-gram.

Please note following:

1. I have replaced the strings ‘(0)’, ‘Tr’, ‘(Tr)’ and ‘-‘ with ‘0’.

2. The text file is derived from ‘Standard Tables of Food Composition in Japan 2010’, published by Report of the Subdivision on Resources The Council for Science and Technology, MEXT, JAPAN. Please contact MEXT to obtain application or notification before duplication or reproduction.

E-mail: kagseis@mext.go.jp

M_FOODS.csv

References:
Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 1
Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 2

Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 1

I have posted the article Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′ that caused incomplete result. After post it, I have found good article in Japanese. Therefore, I gave it additional value in English.

Standard Tables of Food Composition in Japan 2010

Make a new EXCEL Book. Copy text from the PDF files (‘1299012_1.pdf’ to ‘1299012_18.pdf’) and option paste to EXCEL worksheet ‘Sheet1’ continuously without blank line between the last line of previous text and the first line of next text. With ‘Text File Wizard’, change option data type of the first column to ‘String’. Download Academic name of food materials, select all text and paste to Sheet2. In the first tab of ‘Text File Wizard’, select option ‘The data field separated by delimiters such as comma or tab’. In second tab, remove check mark ‘Consider continuous delimiters as one’. In the last tab, change option data type of the first column to ‘String’. Save as ‘Sample.xlsm’.

Press ‘Alt’ key and ‘F11’ key to launch VBE. Insert module and paste following code. Run ‘Separate_by_Parent’ procedure.

References:
CSV file of the ‘Standard Tables of Food Composition in Japan 2010′
Classify the Item_Number of the ‘Standard Tables of Food Composition in Japan 2010′, Part 2

The text file, ‘Standard Tables of Food Composition in Japan 2010’

I requested Office for Resources Policy Division Science and Technology Policy
Bureau, Ministry of Education, Culture, Sports, Science and Technology (MEXT) to approve that I publish the text file, ‘Standard Tables of Food Composition in Japan 2010’ at the end of 2011. On January 13, 2012, I have received the e-mail from staff that the settlement has been completed. Therefore, I have published the text file, ‘Standard Tables of Food Composition in Japan 2010’. Please note the following:

1. I have replaced the strings ‘(0)’, ‘Tr’, ‘(Tr)’ and ‘-‘ with ‘0’.

2. The text file is derived from ‘Standard Tables of Food Composition in Japan 2010’, published by Report of the Subdivision on Resources The Council for Science and Technology, MEXT, JAPAN. Please contact MEXT to obtain application or notification before duplication or reproduction.

E-mail: kagseis@mext.go.jp

M_FOODS

Reference:
CSV file of the ‘Standard Tables of Food Composition in Japan 2010′

How to input PDF files of ‘Standard Tables of Food Composition in Japan 2010’ and output text file?

MEXT, Ministry of Education,Culture,Sports,Science & Technology in Japan has published PDF files called ‘Standard Tables of Food Composition in Japan 2010’. I’m interest in those files, so I have converted them to text file.

Standard Tables of Food Composition in Japan 2010

The procedure is as follows:

  1. Download PDF files
  2. Open a file, Copy all text
  3. Paste to EXCEL
  4. Process with VBA

1. Download PDF files

Download the PDF files on the above link. Top of the list is 1299012_1. Last of the list is 1299012_18.

2. Open a file, Copy all text

Open a file with Adobe Reader. In order to select all text in document, you have to select ‘View’ -> ‘Page view’ -> ‘NOT Single page’. Select All, copy.

3. Paste to EXCEL

‘Paste option’ -> ‘Use text file wizard’. Select option ‘The data field separated by delimiters such as comma or tab’. Change option data type of the first column to ‘String’, click ‘Finish’.

4. Process with VBA

Press ‘Alt’ key and ‘F11’ key to launch VBE. ‘Insert’ -> ‘Standard Module’. Paste the code below.