Importing/reading only a single line of a text file as a field value or parameter to compare to field value in table.
through a stored procedure I need to read in only the first line of a very large text file, assign the substring to a variable and store into a temp table so I can compare it to a real table. If a dts package can be created to only read in a single line thats fine, I'll do it that way but I don't have experience with activex.
widgets 15 Buffalo NY 12312004
I want to read 12312004 into a temp table.
The text file I am reading from will always be in the same location and same file name
widgets 15 Buffalo NY 12312004
I want to read 12312004 into a temp table.
The text file I am reading from will always be in the same location and same file name
Anthony Perkins
There are some undocumented (we are all adults and we should know the potential consequences of that) SQL Server functions you can use to do this. But for the most part this is best handled in the front-end. SQL Server was not designed for file access.
ASKER
so how do other people test the validity of the data that is scheduled to be imported? It is all automated right now within sql server so it would be cumbersome to write a front-end to verify the data before sql imports it into production tables.
I think BULK INSERT should be able to handle that, and it's a standard part of SQL :-) . Something like this maybe ...:
CREATE TABLE testData (testRow VARCHAR(200))
BULK INSERT 'testData' FROM 'd:\full\path\and\data_fil
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '~', --or some other char *not* in the data
LASTROW = 1,
ROWTERMINATOR = '\r\n'
)
CREATE TABLE testData (testRow VARCHAR(200))
BULK INSERT 'testData' FROM 'd:\full\path\and\data_fil
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '~', --or some other char *not* in the data
LASTROW = 1,
ROWTERMINATOR = '\r\n'
)
>>so how do other people test the validity of the data that is scheduled to be imported?<<
You should never import directly into production tables, but rather import into temporary tables first, so the data can be verified.
>>It is all automated right now within sql server so it would be cumbersome to write a front-end to verify the data before sql imports it into production tables.<<
This is how you can do it using the undocumented function xp_ReadErrorLog:
Declare @FilePath sysname,
@YourValue int
Create Table #Temp (
ID integer IDENTITY,
Line varchar(256),
ContinuationRow bit)
Set @FilePath = 'c:\temp\temp.txt' -- File path relative to SQL Server
Insert #Temp (Line, ContinuationRow)
exec master..xp_ReadErrorLog 1, @FilePath
Select @YourValue = CAST(SubString(Line, 33, 8) As Integer) from #Temp Where ID = 1
Drop Table #Temp
I am assuming that the position is fixed and the value is integer, otherwise yoiu will have to modify as appropriate.
You should never import directly into production tables, but rather import into temporary tables first, so the data can be verified.
>>It is all automated right now within sql server so it would be cumbersome to write a front-end to verify the data before sql imports it into production tables.<<
This is how you can do it using the undocumented function xp_ReadErrorLog:
Declare @FilePath sysname,
@YourValue int
Create Table #Temp (
ID integer IDENTITY,
Line varchar(256),
ContinuationRow bit)
Set @FilePath = 'c:\temp\temp.txt' -- File path relative to SQL Server
Insert #Temp (Line, ContinuationRow)
exec master..xp_ReadErrorLog 1, @FilePath
Select @YourValue = CAST(SubString(Line, 33, 8) As Integer) from #Temp Where ID = 1
Drop Table #Temp
I am assuming that the position is fixed and the value is integer, otherwise yoiu will have to modify as appropriate.
Scott's approach is safer.
OOPS, that's not working, will try to correct it ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was my first question so if this comes up a couple times sorry.
Scott,
THANKS, worked perfect! got an eof error but I can deal with that easy enough....
Scott,
THANKS, worked perfect! got an eof error but I can deal with that easy enough....
>> got an eof error but I can deal with that easy enough.... <<
Hmm, interesting, I wouldn't expect that if there was more than one row in the file ...
Hmm, interesting, I wouldn't expect that if there was more than one row in the file ...