Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
How about using SSIS ? If you are going to import the files frequently, SSIS would be the best solution to put in place. –  Kin May 14 '13 at 13:35
    
I'd like to, but again, I'm looking to see if there is a scripting solution. SSIS is probably not an option. –  webdad3 May 14 '13 at 13:36
1  
@JeffV - You could of course cursor through the records, but any DBA would recommend strongly against that approach when you could rather just do one set based statement. –  RoKa May 14 '13 at 14:23
1  
OK, so in other words, line-by-line is not an option at all. Use a set based statement then like this : insert into #t (...<fields>...) select left(fld, 3) as id, substring(fld, 4, 5) as name ... from mySingleFieldTable –  RoKa May 14 '13 at 14:28
2  
@JeffV This isn't an issue of "rudimentary" versus "elegant", this is an issue of using the wrong tool for the job. You really should be looking at BCP or SSIS because those tools were designed exactly for tasks like this. Using things like OPENROWSET are cases of fitting the proverbial square peg in a round hole. –  Mike Fal May 14 '13 at 16:03
show 7 more comments

3 Answers

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.

share|improve this answer
    
Using substring() to split millions of rows is a performance disaster. Take a look at my answer using FORMAT FILE, isn't it a better solution for his situation? –  Justicator May 16 '13 at 8:01
    
In my very limited experience, SQL isn't as bad at string manipulation as everyone says--this is how I'd try it if SSIS vaporized itself, and if the performance wasn't acceptable, I'd try something else. –  SQLFox May 16 '13 at 13:27
    
Thanks SQLFox I appreciate the help –  webdad3 May 22 '13 at 14:38
add comment

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

share|improve this answer
    
Not having experience with FORMAT FILE, I've looked into it briefly, but I haven't seen an example that addresses files like the example, where the header has one set of columns, the data rows another, and the footers another. Can you suggest a source for info? –  SQLFox May 16 '13 at 13:41
    
@SQLFox I will search for the sources that helped me do it, it was a long time ago... I can also write an example for Jeff, I just need a few more lines in his example, and the structure of his destination table. –  Justicator May 16 '13 at 14:48
    
Jusiticator - my example is very rudimentory. The actual data I have is proprietary so I can't put that here. I'd love to see a working example though. –  webdad3 May 22 '13 at 14:37
add comment

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.

share|improve this answer
    
MS-Access uses a row-by-row method when working with SQL Server. For 100's of files with 1000's or more lines that will be devestating. –  Justicator May 16 '13 at 7:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.