Fixed length text file insert into SQL table

Question

I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.

I have 100's of text files that contain 1000's or lines of data. Each line has a fixed length data format.

For example:

001     abc     test file ***
row 1     Test Data is here!       001
footerId 123 Footer for 001

Then I have a file format of

• recordId starts at 1 length 8
• recordName starts at 9 length 8
• headerDesc starts at 17 length 10
• rownumber starts starts at 27 length 3
• rowNumber starts at 1 length 10
• rowData starts at 11 length 25
• rowId starts at 36 length 3
• footerId starts at 1 length 12
• footerref starts at 13 length 14

I hope that makes sense... Obviously that is not real data. Basically I need to load these files into a table (or many tables). What would be the most basic way to do this in SQL?

I was playing around with the OPENROWSET command but that jammed everything into 1 row. If there was a way to insert a temp table with each line of data I could maybe parse that out into more permanent tables. But it would be great if there was a way to insert the data into an auto generated table.

Is there anyway to do this?

Answer 1

As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.

Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.

Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.

This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.

Answer 2

Your best option (considering your limitations) is probably using OPENROWSET with a FORMAT FILE

Take a look at those links:

Use a Format File to Bulk Import Data
You can use the BCP utility once locally to Create a Format File
I prefer the XML Format Files

Answer 3

Since you are trying to avoid SSIS and BCP: If you don't have a deep-seated hatred of MS Access, you might want to take a brief look at it. It has a pretty slick flat-file import wizard. I prefer it over SSIS for small one-time (manual) jobs with flat-files. MSAccess can link directly into SQL Server (or equiv) tables and import directly into the server (instead of humble MS Access tables).

For bigger jobs or recurring jobs, BCP or SSIS are going to be your best bets.