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?

Vikas Reddy 0
Viewed 19

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?

load-data-infile importdata mariadb datetime mysql
1 Answers

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.

O. Jones0
Related