Import fixed width text to SQL

Question

We have records in this format:

99 0882300 25 YATES ANTHONY V MAY 01 12 04 123456 12345678

The width is fixed and we need to import it into SQL. We tried bulk import, but it didn't work because it's not ',' or '\t' separated. It's separated by individual spaces, of various lengths, in the text file, which is where our dilemma is located.

Any suggestions on how to handle this? Thanks!

Answer 1

question is pretty old but might still be relevant.

I had exactly the same problem as you. My solution was to use BULK INSERT, together with a FORMAT file.

This would allow you to:

1. keep the code much leaner
2. have the mapping for the text file to upload in a separate file that you can easy tweak
3. skip columns if you fancy

To cut to the chase, here is my data format (that is one line)

608054000500SS001 ST00BP0000276AC024   19980530G10379    00048134501283404051N02912WAC 0024 04527N05580WAC 0024 1998062520011228E04ST 04856      -94.769323       26.954832     
-94.761114       26.953626G10379    183    1

And here is my SQL code:

BULK INSERT dbo.TARGET_TABLE
   FROM 'file_to_upload.dat' 
   WITH (
            BATCHSIZE = 2000,
            FIRSTROW = 1,
            DATAFILETYPE = 'char',
            ROWTERMINATOR = '\r\n',
            FORMATFILE = 'formatfile.Fmt'

        );

Please note the ROWTERMINATOR parameter set there, and the DATAFILETYPE.

And here is the format file

11.0
6
1   SQLCHAR 0   12  ""  1   WELL_API    SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR 0   19  ""  2   SPACER1     SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR 0   8   ""  3   FIELD_CODE  SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR 0   95  ""  4   SPACER2     SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR 0   5   ""  5   WATER_DEPTH SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR 0   93  ""  6   SPACER3     SQL_Latin1_General_CP1_CI_AS

I put documentation links below, but what you must note is the following:

1. the ""s in the 5th column, which indicates the separator (for a .csv would be obviously ","), which in our case is set to just "";
2. column 2 is fully "SQLCHAR", as it's a text file. This must stay so even if the destination field in the data table is for example an integer (it is my case)

Bonus note: in my case I only needed three fields, so the stuff in the middle I just called "spacer", and in my format file gets ignored (you change numbers in column 6, see documentation).

Hope it answers your needs, works fine for me. Cheers

Documentation here: https://msdn.microsoft.com/en-us/library/ms178129.aspx https://msdn.microsoft.com/en-us/library/ms187908.aspx

Answer 2

When you feel more at home with SQL than importing tools, you could bulk import the file into a single VARCHAR(255) column in a staging table. Then process all the records with SQL and transform them to your destination table:

CREATE TABLE #DaTable(MyString VARCHAR(255)) 
INSERT INTO #DaTable(MyString) VALUES ('99 0882300 25 YATES ANTHONY V MAY 01 12 04 123456 12345678')

INSERT INTO FInalTable(Col1, Col2, Col3, Name)
SELECT CAST(SUBSTRINg(MyString, 1, 3) AS INT) as Col1,
    CAST(SUBSTRING(MyString, 4, 7) AS INT) as Col2,
    CAST(SUBSTRING(MyString, 12, 3) AS INT) as Col3,
    SUBSTRING(MyString, 15, 6) as Name
FROM #DaTable

result: 99  882300  25  YATES