September 9, 2005 at 10:42 am
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 13, 2005 at 2:59 am
Hi,
U can use dts package and u can set parameter by using bulk insert task.
hope thus help u.
from'
Killer
September 13, 2005 at 5:41 pm
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
September 14, 2005 at 9:32 am
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.
September 14, 2005 at 10:38 am
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy