Importing a text file

[spaulding]

Need a bit of help here, I'm moderately new to Sql Server running Sql Server 2000 and am trying to import a long text file into several tables. Each record in the text file is 3000 characters and the fields are of varying length and not delimited. I've searched through the forum and found an old thread (

http://www.tek-tips.com/viewthread.cfm?qid=942610)

that is pretty helpful, but I'm still doing something wrong. I'm trying to follow the advice EdwinGene (still out there?)suggested about using inserts and selects, but when I try to run the stored procedure(code below) I only get one record inserted, although it's parsed correctly. I'm sure that's because there's no looping, but isn't that the Cursor the previous thread warned against using?

Anyhow I'd appreciate any help.

Code

CREATE PROCEDURE dbo.usp_INS_TAKSData
AS
Declare
@AdminDate Char(4),
@Grade char(2),
@ESCRegion char(2),
@Campus char(3),
@LastName char(15),
@FirstName char(10),
@MI char(1),
@StudentSS char(9),
@Sex char(1),
@DOB char(8),
@StudentID char(6)

select @AdminDate=substring(col001,1,4),
@Grade=Substring(col001,5,2),
@ESCRegion=substring(col001,7,2),
@Campus=substring(col001,123,3),
@LastName=substring(col001,48,15),
@FirstName=substring(col001,63,10),
@MI=substring(col001,73,1),
@StudentSS=substring(col001,74,9),
@Sex=substring(col001,83,1),
@DOB=substring(col001,84,8),
@StudentID=substring(col001,126,6)
from tblTempData

Insert into tblTAKSAdminInfo
(AdminDate, Grade, ESCRegion, Campus, LastName, FirstName, MI, StudentSS, Sex, DOB, StudentID)
Values
(@AdminDate, @Grade, @ESCRegion, @Campus, @LastName, @FirstName, @MI,
@StudentSS, @Sex, @DOB, @StudentID)
GO

 

[AlexCuse]

Spaulding - are you talking about a fixed width text file? Like this:

col1           col2              col3
asdlfadfa      3/18/07           SomeData
asfd           3/19/06           SomeMoreData
adasfadfasfafa 11/28/06          SomeMoreData

Because you can set up import specifications to break a file like this into columns, and then use your normal data pump task to move it around.

It could also be a tab-delimited file. If you are getting a file that is not adhering to any layout, I truly feel for you. Can you post a little bit of what this file looks like? There has got to be a better way than looping and using T-SQL's string manipulation functions I think. Maybe use vbScript to parse the rows as the file is brought in (in the very worst case).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[spaulding]

Thanks for the response. I can't post any of the file due to privacy constraints, but I can heres some of the data file format. (The full help file is here:

http://www.tea.state.tx.us/student.assessment/reporting/formats/index.html)

In this 3000 character string, the first 4 columns are the test date, columns 5 and 6 are the grade, 7-8 are the testing region, columns 48-62 are last name, 63-72 are first name and so on. I've got several thousand kids' records that I've normalized into 11 data tables. I'm trying to find the easiest way to do this since I'll have to do this 3-5 times per year

 

[AlexCuse]

Ok, I just looked at a random layout from 2007, but it looks like the files are in the first layout I mentioned.

When you set up a text file source in the DTS editor, choose the 'fixed field' option. Then, when you hit next, you will be able to set your column boundaries. Once this is broken in to different columns, you can set up your data pump task to pull straight from this text file (now broken up by columns) into your database.

The layout gives you starting position and length, so it shouldn't be too hard to set up.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[spaulding]

Thanks, I had started that way before I had really gotten an understanding of the data. Got frustrated (and crosseyed) when I was on my ~200th slice of 1 character datafields. Then those immortal programmer words "There has to be a better way" flashed through my mind. Oh well, I learned a lot about multi-statement stored procedures I didn't know before.

Appreciate the help, have a good weekend.

 

[AlexCuse]

You too, and good luck. Remember, better to set up your file layout once than write code to split the string into 200+ columns.

Ignorance of certain subjects is a great part of wisdom