Import fixed-width text file into sqlite

Question

What is a good way to import fixed-width text files into sqlite tables, preferably without using peripheral software?

E.g. specifying the width of each field

Field 1: 10 characters starting with the second character
Field 2: 5  characters starting with the twelfth character
Field 3: 7  characters starting with the eighteenth character

The line

AABCDEFGHIJABCDEAABCDEFGA

would be imported as:

Field 1     Field 2  Field 3
ABCDEFGHIJ  ABCDE    ABCDEFG

Thanks

score 8 · Answer 1 · answered Sep 27 '14 at 19:45

8

The link in the answer above is for generic SQL. Here is one way to do it in SQLite:

CREATE TABLE fixed_width_table (full_string CHAR);
.import fixed_width_file.txt fixed_width_table

CREATE TABLE tmp AS
Select
    SUBSTR(full_string,1,11) AS field1
    ,SUBSTR(full_string,2,5) AS field2
    ,SUBSTR(full_string,2,7) AS field3
FROM fixed_width_table

answered Sep 27 '14 at 19:45

user4086833

529
5
3

2

Thanks! This saved me a lot of time. Just for completeness if anyone is trying to import a fixed-width text file with a variable length last column, then just omit the 3rd parameter in the corresponding `SUBSTR` (e.g. `,SUBSTR(full_string, 10) AS variable_length_last_column`. That will return all of the remaining text on that line. – perNalin Sep 08 '15 at 11:28

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

The sqlite3 tools imports only CSV files.

There are third-party tools that can import fixed-width files, but this answer shows how to do this inside SQLite with string functions.

edited May 23 '17 at 12:31

Community

1
1

answered Sep 14 '12 at 12:34

CL.

165,803
15
203
239

Import fixed-width text file into sqlite

2 Answers2

Linked