0

I am trying to reference the textfile from the assets folder which is a preloaded database. I need help fixing the text file so I can put it into assets folder to reference them within SQLite. I have followed a tutorial to reference the data in the assets folder. If I can get the columns correct I believe I will be able to finish. Language, Java.

https://www.ars.usda.gov/Services/docs.htm?docid=23634
This is the website I received the Data. I am using the Abbreviated version of ASCII. I got the titles from the Excel file rite below the ASCII download.

These are the column names I got from the excel file which I want to label the data from the snippet below.

"-" = separator for each title/label.

-NDB_No -Shrt_Desc -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

This is a snippet of how each entry is formatted.
This is the first line of code in the text file. I dont know how to give each category its title in this format/ in general. 

~16200~^~CAMPBELL'S BRN SUGAR&BACON FLAV BKD BNS~^69.40^123^3.85^1.92^1.75^23.08^6.2^10.00^31^1.11^^^^362^^^^^0.0^^^^^^^^^^^^0^^^^^^^^^^^^0.385^^^4^130^~.5 cup~^130^~1 serving~^0

If you need more information, I will get back as fast as I can. appreciate the help.

Eugene H
  • 3,520
  • 3
  • 22
  • 39
  • In databases, the "titles"/"labels" are called "column names". Are you asking how to create a database table? – CL. Aug 08 '14 at 19:10
  • @CL. How do I create Columns within the pre-loaded database? I have not worked with preloaded databases before and trying to get this right. – Eugene H Aug 08 '14 at 19:24
  • It appears you are lacking basic knowledge about databases (and how to create one on the desktop, or how to access it from your app). All this would be too broad to teach you in a single answer on this site. – CL. Aug 08 '14 at 19:58
  • I undersatnd. I obvously still have a lot of learning to do. As for databases, I have worked with simple data. I will work on leaning more about creating databases on the desktop. – Eugene H Aug 08 '14 at 20:18
  • 1
    The answer to your question would probably be "click it together in any GUI tool, such as SQLite Manager". – CL. Aug 08 '14 at 20:25
  • @CL. I have turned it into a .sqlite file using the excel provided. I also used SQLite Manager. It was actually quite simple. Thank you. – Eugene H Aug 08 '14 at 22:30

1 Answers1

1

Personally I think your question is too broad for a good answer especially as the format of the delimited files is possibly the most bizarre I've ever seen.

The only way I can suggest is to do something similar to the way I use CSV files and simply add the column names to the file as the very first line. Example...

Suppose you want to have a CSV file of contacts with uniquie id, first and last name and telephone number, you would create the file as follows...

_id,first_name,last_name,phone_num
1,John,Smith,12345678
2,Bill,Jones,23456789

When processing the CSV file you would then simply treat the first line separately, split it into the column names and add those as columns in your SQLite DB table. From then on you treat each line as the data for each row.

CSV traditionally stood for comma-separated variable which is what I've shown, but these days it is often used to refer to character-separated variable with any character possible for the delimiter. This is what you have with fields (columns) separated by carets (^). When adding the first line for the column names it is usual to use the same delimiter character so it can be parsed with the same code.

Squonk
  • 48,735
  • 19
  • 103
  • 135
  • The text format was already handled in [an earlier question](http://stackoverflow.com/q/25187900/11654). This question appears to be simply about how to create a database. – CL. Aug 08 '14 at 19:54
  • I agree. It is quite bizarre. I tried to be specific as I possibly can. I have never seen this type of code before and never worked with preloaded data. – Eugene H Aug 08 '14 at 19:58
  • Got it to work. Used the excel file converted it to csv. Then used sqlmanager. – Eugene H Aug 09 '14 at 00:44