I am helping a client who needs a nutrition facts database so I am trying to insert a text file (FOOD_DESC.txt) into a database, but I just don't know enough mySQL to do it myself. All fields are delimited by '^'s and values are delimited by '~'s - and the database is available at http://www.ars.usda.gov/Services/docs.htm?docid=13746. could anyone help me out?

If I understand you correctly, you need import the files into mySQL. Which files in particular do you need? Do you need the full or abbreviated version? If you still need help I may be able to help you.

if you download the ASCII version of the USDA National Nutrient Database for Standard Reference, you will get several .txt files - for example, here are two lines from the FOOD_DESC.txt file which unfortunately I can't attach, however I will attach one of the smaller files which is formatted exactly the same as FOOD_DESC.txt which I also need to be imported.

(~01001~^~0100~^~Butter, salted~^~BUTTER,WITH SALT~^~~^~~^~Y~^~~^0^~~^6.38^4.27^8.79^3.87)
(~01002~^~0100~^~Butter, whipped, with salt~^~BUTTER,WHIPPED,WITH SALT~^~~^~~^~Y~^~~^0^~~^6.38^4.27^8.79^3.87)

*I inserted the parenthesis to show line breaks*

And yeah I need to import the full versions of FOOD_DESC.txt, NUTR_DEF.txt, FOOTNOTE.txt, FD_GROUP.txt, WEIGHT.txt, DERIV_CD.txt, and NUT_DATA.txt - if you could help me I would be so grateful, thanks a lot!

OK, this is definitely doable. First thing is to understand what the delimiters mean. I am a bit confused by the two delimiters and why they are both there. If there are two ~ next to each other does this mean that the field has two different numbers? Or is the ~^~ actually the delimiter between the two fields?

Second thing we need to establish is what each field is named, because you need that to create your mySQL table.

Once we have that info, we can go ahead to create a sql dump file.

Let me know the answers to the above and we can work through it. I'm not yet advanced enough to write a script that will do it automatically but there are other methods, such as using PHPMyAdmin, that I can walk you through, if you're interested. I could also just do it for you if you want to discuss that.

k - when there are two ~ next to each other it means the value is empty - I will do a bit of research later today and by tomorrow morning I will tell you what I will name the fields. (i'm out of state today or I would get it to you more quickly). From what I understand after reading a .pdf explaining the database on the USDA site, the ^ is the field delimiter while ~ delimits values... I should be able to explain more clearly later after i get the chance to read up a bit. Thanks a lot!

Originally Posted by claye

k - when there are two ~ next to each other it means the value is empty - I will do a bit of research later today and by tomorrow morning I will tell you what I will name the fields. (i'm out of state today or I would get it to you more quickly). From what I understand after reading a .pdf explaining the database on the USDA site, the ^ is the field delimiter while ~ delimits values... I should be able to explain more clearly later after i get the chance to read up a bit. Thanks a lot!

No worries. Let me know when you figure out the titles and all that, then we'll start on it. I will be travelling myself a bit this week, but I should be able to get online pretty regularly and we can work on it.

Downloaded the Access and exported the tables to .csv files

zip file attached or not 'cos it's too big


import the .csv into whatever MySql tool you use.

http://www.candsdesign.co.uk/download/sr19.zip

Originally Posted by chrishirst

Downloaded the Access and exported the tables to .csv files

zip file attached or not 'cos it's too big


import the .csv into whatever MySql tool you use.

That's cool . I think OP wanted to learn how to do it, and needed to rename the field titles, and then import into mysql, so I was going to walk him through it so that he could do it himself in the future, or do it for him at no cost, but this might be enough, so some talkuptation for you chrishirst.

@claye, if you still want to learn how to do it post here and we can walk you through it.

You can still do all that with the .csv files and it will be simpler to import and easier to explain than with the strangely delimited files which had NO column names at all.

Ideally it needs the csv files converting into SQL scripts to create the table structure on import, so there is plenty of educational work to be done

Originally Posted by chrishirst

You can still do all that with the .csv files and it will be simpler to import and easier to explain than with the strangely delimited files which had NO column names at all.

Ideally it needs the csv files converting into SQL scripts to create the table structure on import, so there is plenty of educational work to be done

True true

hey thanks for the info - I'm really just looking for the simplest, quickest way to import that info so if converting it to a .csv file and then importing is the best way (and it doesn't sound too difficult) then thanks for the idea and the help - now i just need to figure out how to put that into mySQL and I'll be good - sorry about the long time it has taken to respond - I was in Idaho for the weekend with the in-laws - and their DIAL UP CONNECTION! Wow. i didn't even try to connect and there are no wi-fi areas for my laptop either... anyways I appreciate both of your help and look forward to learning what else it will take to create that database - thanks a lot guys, you rock