MariaDB - TEXT File Import (Paragraphs w/ commas & * coding)

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

MariaDB - TEXT File Import (Paragraphs w/ commas & * coding)

Postby TruthSword » 23. January 2022 00:33

MariaDB - TEXT File Import (Paragraphs w/ commas & * coding)

I am learning how to import .txt files directly into MariaDB (Version: 10.4.x) on this machine (Debian).

The blogs, tutorials, stacks, etc that I have been researching on google don't seem to answer my questions.

Here my database:
Code: Select all
DB Name: LOADFILE_TXT
Table Name: demo_paragraphs

Column #1: id (Primary Key + Auto_Increment)
Column #2: fulltext (My Only Field for Importing Text) [LONGTEXT]
Column #3: fulltext_entry_timestamp [TIMESTAMP + CURRENT_TIMESTAMP]


Here is a sample of my text file (I created it for learning; the big one I need to do is much more complicated; However I believe if I can learn this, I should be able to import the other):

Code: Select all
PROCLAMATION ANNOUNCING ADMISSION OF WASHINGTON - 1889

    BY THE PRESIDENT OF THE UNITED STATES OF AMERICA             
    A PROCLAMATION                           

Whereas the Congress of the United States did by an act approved on the twenty-second day of
February one thousand eight hundred and eighty-nine, provide that  the inhabitants of the
Territory of Washington might, upon the conditions prescribed in said act, become the State of
Washington;
And whereas it was provided by said act that delegates elected as therein provided, to a
Constitutional convention in the Territory of Washington, should meet at the seat of government
of said Territory; and that, after they had met and organized they should declare on behalf of the
people of  Washington that they adopt the Constitution of the United States; whereupon the said
convention should be authorized to form a State Government for the proposed State of
Washington;
And whereas it was provided by said act that the Constitution so adopted should be republican
in form and make no distinction in civil or political rights on account of race or color, except as
to Indians not taxed, and not be repugnant to the Constitution of the United States and the
principles of the Declaration of Independence; and that the Convention should by an ordinance
irrevocable without the consent of the United States and the people of said State make certain
provisions prescribed in said act;
And whereas it was provided by said act that the Constitution thus formed for the people of
Washington should, by an ordinary of the Convention forming the same, be submitted to the
people of Washington at an election to be held therein on the first Tuesday in October, eighteen
hundred and eighty-nine, for ratification or rejection by the qualified voters of said proposed
State; and that the returns of said election should be made to the Secretary of said Territory, who,
with the Governor and Chief justice thereof, or any two of them, should canvass the same; and if
a majority of the legal votes cast should be for the Constitution, the Governor should certify the
result to the President of the United States, together with a statement of the votes cast thereon,
and upon separate articles or propositions and a copy of said Constitution, articles, propositions
and ordinances;
And whereas it has been certified to me by the Governor of said Territory that within the time
prescribed by said act of Congress a Constitution for the proposed State of Washington has been
adopted and that the same, has been ratified by a majority of the qualified voters of said proposed
State in accordance with the conditions prescribed in said act;
And whereas it is also certified to me by the said Governor that at the same time the body of
said Constitution was submitted to a vote of the people two separate articles entitled "Woman
Suffrage" and "Prohibition" were likewise submitted, which said separate articles did not receive
a majority of the votes cast thereon or upon the Constitution and were rejected; also that at the
same election the question of the location of a permanent seat of government was so submitted
and that no place receive a majority of all the votes cast upon said question;
And whereas a duly authenticated copy of said Constitution and articles, as required by said
act, has been received by me:
Now, therefore, I, Benjamin Harrison, President of the United States of America, do, in
accordance with the provisions of the act of Congress aforesaid, declare and proclaim the fact
that the conditions imposed by Congress on the State of Washington to entitle that State to
admission to the Union have been ratified and accepted and that the admission of the said State
into the Union is now complete.
In testimony whereof, I have hereunto set my hand and caused the seal of the United States to
be affixed.
 Done at the City of Washington this eleventh (11th) day of November in the year of our Lord
 one thousand eight hundred [SEAL.] and eighty-nine, and of the Independence of the United
 States of America the one hundred and fourteenth.
                                BENJ. HARRISON.
 By the President:
    JAMES G. BLAINE,
       Secretary of State.
*
PREAMBLE

We, the people of the State of Washington, grateful to the Supreme Ruler of the Universe for
our liberties, do ordain this constitution.
*


As you can see there are 2 (two) paragraphs that I want to be imported into "fulltext" column in MariaDB separated by a single "*" aster-ix character.

MariaDB 10.4.x Input:

Code: Select all
MariaDB [LOADFILE_TXT]> LOAD DATA LOCAL INFILE  '/home/brandon/Desktop/EXODUS/LOADFILE_TEXT/table.demo_paragraphs/demo_paragraphs.txt' INTO TABLE demo_paragraphs;


MariaDB 10.4.x Output:

Code: Select all
Query OK, 63 rows affected, 189 warnings (0.202 sec)
Records: 63  Deleted: 0  Skipped: 0  Warnings: 189

MariaDB [LOADFILE_TXT]>


This imported 63 null value rows.

What I was hoping to do was import exact formatting of full paragraphs as individual "fulltext" column entries.

What do I need to do to achieve this? The original .txt that I am working on getting fully imported starts with those two paragraphs (However there are several *** START *** & *** SEND *** coded lines; besides that, the text formatting is identical regarding the text paragraphs). I know that CSV is field separated by "," which is used throughout all my paragraphs. I am unsure how to proceed.

Any help would be greatly appreciated!

Best Regards,

TruthSword
TruthSword
 
Posts: 2
Joined: 23. January 2022 00:18
XAMPP version: N/A
Operating System: Linux-Debian

Re: MariaDB - TEXT File Import (Paragraphs w/ commas & * cod

Postby Nobbie » 23. January 2022 11:39

Read the Mysql (or MariaDB) documentation about LOAD DATA, especially the keywords FIELDS and LINES and how to specify the termination characters.

I dont know what you mean by 'paragraph', that used to be a Word keyword and is represented by a newline character in WordDocuments, but in plain text files, there is no such thing as a paragraph. As it looks, you are using newlines within your text, therefore you have to specify any different character for the LINES than newline (what is default). You may use any character which is NOT used within your text. For example @ or # or so. Finally add that character to the end of each Line (where Line means a row of full data, if i am right, there is only one Line in your text file currently, so simply add the line termination character at the end only.

As FIELD terminator you are using * instead of comma, you have also to apply that information into your LOAD DATA command. The doc will tell you the syntax.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: MariaDB - TEXT File Import (Paragraphs w/ commas & * cod

Postby TruthSword » 24. January 2022 05:57

Nobbie,

Thank you for this. I believe I understand what you are explaining. Make sure that the text that I am calling "paragraphs"; fulltext, has a special character not contained within the fulltext and append to the end of each line or set of lines (that I would call a paragraph). Then a delimiter and apply to LOAD DATA command!

10-4! Appreciate this!

Best Regards,

Brandon
TruthSword
 
Posts: 2
Joined: 23. January 2022 00:18
XAMPP version: N/A
Operating System: Linux-Debian

Re: MariaDB - TEXT File Import (Paragraphs w/ commas & * cod

Postby Nobbie » 24. January 2022 11:41

Yes, exactly.

Finally i think (not sure about) you also should provide the key in your text file, the table declaration starts with the primary key followed by two columns, so three columns at all. But your import file currently only holds two columns.

I would simply add a uniq number (starting from 1) in front of each data line, delimited by your delimiter *. Instead of a number you can also try to apply an empty value instead, what means that you simply preceed each data line with * only, but without a number. The key field has an AUTO_INCREMENT attribute, it should count automatically.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 11 guests