Best way to parse text into SQL statements?-CodePudding

I'm working on a web app, and I only have console access to MySQL. I have a textfile, I want to insert the data into DB, but to do that will I need to make an interpreter first to convert lines from textfile into insert statement? or is there an easier way I'm overlooking?

I have a text file like this (the format is at the bottom of this post)

I want to take each line from the file, do some string manipulation, and insert it into database.

DB looks like this

CREATE TABLE website.Categories (
    ID int NOT NULL,
    CategoryName varchar(500) NOT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE website.Questions(
ID int NOT NULL,
Category int,
QuestionText varchar(5000) NOT NULL,
AnswerText varchar(5000) NOT NULL,
PRIMARY KEY (ID)
);

I can make a JavaScript interpreter that "compiles" the text file into a bunch of MySQL insert statements, and I can then just run those statements, can someone please verify if that's the easiest way to go about this?

Example in pseudocode of what I will have to do:

- go one line at a time
- initialize counter
- take first line, surround it with "INSERT INTO 'website.categories' VALUES (_____)
- WHILE line not blank, take every line after that, split string using '?' as separator
- "INSERT INTO 'website.questions' VALUES (counter, __question___, __answer___)
- when blank increment counter
- insert next line into categories
- repeat

This is what the text file looks like:

chemistry
what is the formula for hydrogen peroxide?h2o2
what is the state of matter of water at room temperature?liquid
what is the lightest element?hydrogen
which silvery element was used in early thermometers?mercury
what does water turn into when boils?Steam
what element is glass made from?Silicon

geography
what is the capital of Austrailia?Canberra
what is the capital of Turkey?Ankara
what is the capital of Malaysia?Kuala Lumpur
what ocean is to the west of the United States?pacific
what ocean is to the east of Canada?atlantic
Which is the largest country in the world?Russia
Which river flows through Rome?Tiber
In which country is Mount Kilimanjaro?Tanzania
what is the longest river in the world?Nile
on which continent will you find Brazil?South America
On which continent are the Atlas Mountains?Africa

animals
which country is known for having pandas?China
what animal is known for eating bamboo?panda
which animal carries babies in its pouch?kangaroo
what are eggs of salmon called?roe
which animal has stripes?zebra
what is the fastest land animal?Cheetah
how many hearts do octopuses have?3
what is the baby goat called?kid
what do you call an animal that eats plants?herbivore
what is the longest snake in the world?Python
what is a female donkey called?Jenny
Which animal is the Florida Cracker?A Sheep

instruments
which device is used to measure temperature?thermometer
who invented radio?marconi
who invented waste dumpsters?demptser
what gas is usually filled in lighters?butane
what does a manometer measure?Pressure

physics
what unit is sound loudness measured in?decibel
what is the unit of power?watt
which circuit component is known for storing charge?capacitor
what is the unit of electrical resistance?ohm

CodePudding user response：

I was able to easily accomplish this with Java, I thought it would take hours but no only took less than 10 minutes.

  Scanner myReader = new Scanner(myObj);
  while (myReader.hasNextLine()) {
    String data = myReader.nextLine();
    if(data.equals("")) {
        counter  ;
    }else if(data.contains("?")){
        String lol[] = data.split("\\?");
        System.out.println("INSERT INTO website.Questions (ID,Category,QuestionText,AnswerText) Values ("  counter2 ","  counter   ",'" lol[0] "', '"  lol[1] "');");
        counter2  ;
    }else{
        System.out.println("INSERT INTO website.Categories (ID, CategoryName) Values ("  counter  ", '"   data  "');");
    }