March 31, 2008 at 10:06 am
I want to open a txt file and read a specific line. Where do I specify the Line Number.
Here is my code:
--Create Object
exec @HR = sp_OACreate "Scripting.FileSystemObject",@ObjectId OUT
--Create File Object from "FileSystemObject"
exec @HR = sp_OAMethod @ObjectID,'OpenTextFile',@FileObjectID out,@FilePath
--Create ReadAll Object From "File Object"
exec @HR = sp_OAMethod @FileObjectId,'ReadLine',@Body out
--Destroy "FileSystemObject" Object
exec @HR = sp_OADestroy @ObjectID
--Destroy "File Object"
exec @HR = sp_OADestroy @FileObjectID
select @Body
This works and will display the first line in the recordset.
I'm not really familiar with VBScript so reading thru BOL and some Dev forums is a little confusing.
Anyone have a suggestions to this problem or some good references on how to use VSBScript with the OACreate and OAMethod?
Thanks.
March 31, 2008 at 10:43 am
What do you plan on doing with this? What kind of file are you going up against? Is the file structure consistent (meaning - same layout for each record/row)?
ReadLine is a procedural concept to pull in one row at a time from a file. Assuming the file is written the right way - reading one line at a time is a painfully inefficient way to load a file into a database table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 31, 2008 at 11:14 am
Hey Matt,
If we keep running into each other like this your just going to have to give me your phone number!
It's a txt file. It's a little different than a conventional delimited file because it has terminators for almost each row.
I posted last week about this file and using Bulk Insert for it along with a Format file. http://www.sqlservercentral.com/Forums/Topic475437-8-1.aspx#bm475608
The contents would look something like this:
GS*FA*ILS
ST*997*0001^
AK1*IM*275^
AK9*A*1*1*1^
SE*4*0001^
GE*1*272^
IEA*00001*000000272^
[EOB]
Ideally what I want to do is look at the rows AK1 and AK9 and get only one field from each. In the AK1 row I'd get the '275' which is a batch control number.
In the AK9 row I'd get the second field which would indicate "A" for accepted or another letter that means something else.
I can use the 'ReadAll' method set that to a variable then perform some kind of string function to find the correct fields. Not sure if this is the best way to do it.
March 31, 2008 at 11:41 am
You don't want my phone number - it would have to come with a request for your billing info:).
I kind of get the idea, but can you mock up a file (not just one "record", a series of them like the file would have), and go over how the file is structured?
There are quite a few ways to tackle something like this, but it takes some specifics.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 31, 2008 at 11:52 am
I edited my post so the data would appear on each line instead of looking like it was tab delimited.
Actually this file would only contain that amount of data. It won't be repeated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply