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 9 · Answer 1 · answered Sep 27 '14 at 19:45

9

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

549
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.

173,858
17
217
259

surfmuggle · Answer 3 · 2023-04-18T04:23:10.113

0

To import a text file with a fixed length

Import the whole file in a table TestImport with 1 column extract (see import from file)
Write the sql statements you need to query or data-transform the data
Do additional work for all etl needs.

Step 1: Import from your text-file and save it to a db-file.

sqlite> .import C:/yourFolder/text_flat.txt TestImport
sqlite> .save C:/yourFolder/text_flat_out.db

And now you can do all sorts of etl with it (step 2 and 3).

edited Apr 18 '23 at 04:23

answered Sep 14 '22 at 11:06

surfmuggle

5,527
7
48
77

To import a csv file see [sqlite cli import files as csv or other formats](https://sqlite.com/cli.html#importing_files_as_csv_or_other_formats) – surfmuggle Sep 21 '22 at 16:09
I did run into trouble importing the file. The cause was that every line did contain the Replacement Character `�`. The file was exported from excel. My quess is that it was a `€`-sign that caused the `�` in the text file. – surfmuggle Sep 21 '22 at 17:49

Import fixed-width text file into sqlite

3 Answers3

Linked