Is there anything in mariadb to import data from .txt file or different format where i can define a filed value length from position to position?

Question

Trying to import the data in maridadb and my data will look like

"20220910SATURDAY" "20220911MON" "20220912TUE" "20220913THUR"

represents the data. My database structured columns are : column1:Year, column2:Month, Column3:Day, Column4:DayInLetters. Need to define a configuration like in every line first 4 letters i.e from 0 to 3 is year field , 4-5 is Mon, 6-7 is Day, 8-last is DayInLetters. Is there any solution?

That is bad design: 1 column defined as date is enough, so you can retrieve information with YEAR(), MONTH(),DAY() and DAYNAME() functions to get the values. — Georg Richter, Sep 10 '22 at 14:13
@GeorgRichter, that is not the actual data. That is the sample data — Vikas Reddy, Sep 11 '22 at 02:54

score 0 · Answer 1 · answered Sep 10 '22 at 10:38

This is a job for LOAD DATA INFILE. Because the fields in your input file are not delimited by commas, tabs, or similar you need to use an uncommon feature called input preprocessing.

Giving this SQL statement to a command-line mysql client will work.

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE tbl (@line)
      SET year =           SUBSTRING(@line, 1, 4),
          month =          SUBSTRING(@line, 5, 2),
          day =            SUBSTRING(@line, 7, 2),
          day_in_letters = SUBSTRING(@line, 9);

This loads each row in turn of the named file into a temporary variable @line, then extracts a substring for each column.

But, Pro tip, it is far better data design practice to put calendar dates into DATE columns (or TIMESTAMP or DATETIME columns) rather than designing your own way of representing calendar dates. MariaDB and MySQL have comprehensive and completely debugged date-arithmetic features.

instead of writing a query above is there any other solution like defining all the field positions in one file . And the follow-up question is this is just one of the format and we do have many different formats like HEAD>>> DATA TRAIL And we need to read only the data — Vikas Reddy, Sep 11 '22 at 02:57

Is there anything in mariadb to import data from .txt file or different format where i can define a filed value length from position to position?

1 Answers1