Importing certain Text rcds into SQL

Question

Post reply

Importing certain Text rcds into SQL

homebrew01

SSC Guru

Points: 55548
More actions
September 9, 2005 at 10:42 am

#69637

I want to import only certain records from a text file into a SQL table. I have a non-SQL process that creates a text file with different types of records. For instance,
Record #1 might contain just short beginning batch control info.
Record #2 Contains batch DETAIL info, with 24 delimited fields (This is what I want)
Record #3 contains just short ending batch control info.
Example of data in text file, delimited with vertical bars (truncated for brevity):
(Rcd #1)    BATCH|20050901999|20050901120000000
(Rcd #2)    DETAIL|1|00IPB||1000|||||MA100010||||Mary|Smith||||123 Main St||Brookfield|CT|06804|
(Rcd #3)    BCHEND|20050901999|20050901120000000|1000|1
I don't have a problem figuring out how to import just the DETAIL record, but how do I omit the other records in my DTS so that it doesn't fail on them ? I'm not VB or ActiveX savvy, so the more detail the better if that's part of the solution.
This is the DDL of my SQL table
CREATE TABLE [dbo].[BatchDetail] (
[LineId] [char] (6) ,
[DocNumber] [int] NULL ,
[DocType] [char] (5) ,
[PayAmount] [float] NULL ,
[PayDue] [float] NULL ,
[CustNumber] [char] (12) ,
[ThreeYearPrice] [float] NULL ,
[TwoYearPrice] [float] NULL ,
[OneYearPrice] [float] NULL ,
[SourceCode] [char] (8) ,
[OrderNumber] [char] (8) ,
[FinderNumber] [char] (8) ,
[Title] [char] (8) ,
[FirstName] [char] (20) ,
[LastName] [char] (30) ,
[NameSuffix] [char] (8) ,
[MiddleInitial] [char] (1) ,
[CompanyName] [char] (50) ,
[Street1] [char] (40) ,
[Street2] [char] (40) ,
[City] [char] (25) ,
[State] [char] (2) ,
[Zip] [char] (9)
September 12, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

raj-247939 SSCertifiable Points: 5999 More actions · Answer 1

Hi,

U can use dts package and u can set parameter by using bulk insert task.

hope thus help u.

from'

Killer

Richard Kemp SSC Journeyman Points: 76 More actions · Answer 2

I just had to deal with this myself. I find that a lot of simple text-based manipulation and filtering is most easily done with batch scripts BEFORE I bring them into the database.

I like to use Unix-ish commands myself, but using the FIND.EXE command works just as well. For instance, to get just the detail record you would do this:

FIND.EXE "DETAIL" extract_filename.txt >just_the_detail.txt

Then just bcp or bulk insert the just_the_detail.txt file into your table.

- Rick

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

That sounds like what I need ! Where do you run the FIND command from ?

As a test, I'm trying it from the Windows command line, but I can't get it working. I tried:

D:\Training\Test> FIND "DETAIL" TestData.txt > TestData_Detail.txt

It says

FIND: unable to access "DETAIL": The system cannot find the file specified

Even though I'm in the right directory, and a DIR command shows that TestData.txt is there. I'll fiddle around a bit more.

Thanks

>>> EDIT: Looks as though I needed to be in the WINNT\SYSTEM32 folder to run it.

It outputs now, but it puts a blank line and a "header" record in before my "DETAIL" records like so:

---------------------- D:\Training\Test\TestData.txt

DETAIL|Joe|Smith|123 Main St.

So I still don't have just "DETAIL" records. I don't see any switches that would prevent the header record. I guess I can tell my DTS to start with line 3.

Richard Kemp SSC Journeyman Points: 76 More actions · Answer 4

that's why i use unix shell commands to do pre-parsing, its a lot more flexible. but try this to get what you want:

type TestData.txt | find "DETAIL" >TestData_Detail.txt

i don't know why you need to be in system32, that should be in your path anyway.