Raster Catalog: file

Showing posts with label file. Show all posts

Friday, March 30, 2012

reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I import data by omporting a txt file that is tab delimited.
>> On of the fileds in the txt file has a date 08/06/2007 (for example)
>> I need to read that into my field in my table that is set as datetime.
>> Is there somethign special that need to get done? When I try, i get teh
>> following error:
>> Conversion invalid for datatypes on column pair 1 (source column
>> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> You should change the source data type to datetime or use text for
> both.
>|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>
> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> I import data by omporting a txt file that is tab delimited.
> >> On of the fileds in the txt file has a date 08/06/2007 (for example)
> >> I need to read that into my field in my table that is set as datetime.
> >> Is there somethign special that need to get done? When I try, i get teh
> >> following error:
> >> Conversion invalid for datatypes on column pair 1 (source column
> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> > You should change the source data type to datetime or use text for
> > both.- Hide quoted text -
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> it's a text file.
>> Before I export it to a text file, I have it formated as datetime in
>> excel.
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>>
>> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> I import data by omporting a txt file that is tab delimited.
>> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> example)
>> >> I need to read that into my field in my table that is set as datetime.
>> >> Is there somethign special that need to get done? When I try, i get
>> >> teh
>> >> following error:
>> >> Conversion invalid for datatypes on column pair 1 (source column
>> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
>> > You should change the source data type to datetime or use text for
>> > both.- Hide quoted text -
>> - Show quoted text -
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>
> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> >> it's a text file.
> >> Before I export it to a text file, I have it formated as datetime in
> >> excel.
> >> "SB" <othell...@.yahoo.com> wrote in message
> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> >> I import data by omporting a txt file that is tab delimited.
> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
> >> >> example)
> >> >> I need to read that into my field in my table that is set as datetime.
> >> >> Is there somethign special that need to get done? When I try, i get
> >> >> teh
> >> >> following error:
> >> >> Conversion invalid for datatypes on column pair 1 (source column
> >> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> >> > You should change the source data type to datetime or use text for
> >> > both.- Hide quoted text -
> >> - Show quoted text -
> > Hi,
> > I think it be easier if you import the text file AS IS that is all
> > columns as text (including the column with datetime) you can call it
> > Excel_to_text_TEMP. Once you have this table imported in the server
> > you can populate your main table from this temp table and use a
> > conversion function for datetime etc. HTH.- Hide quoted text -
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB|||SB <othellomy@.yahoo.com> wrote in
news:1186631817.654696.162750@.19g2000hsx.googlegroups.com:
> On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I do intailly load it into a tmp table, as after its in teh temps
>> table, I have it go and only copy new original records into the main
>> table, I also have it update exsisting records. so with that
>> said...
>> How do I convert it to datetime as it goes from the tmp table to teh
>> main table?
>> I am not an sql master or anythign, heck, i'm not ever a sql novice
>> lol
>> so please have your example as clear as possible..
>> THANK YOU
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>>
>> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> >> it's a text file.
>> >> Before I export it to a text file, I have it formated as datetime
>> >> in excel.
>> >> "SB" <othell...@.yahoo.com> wrote in message
>> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> >> I import data by omporting a txt file that is tab delimited.
>> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> >> example)
>> >> >> I need to read that into my field in my table that is set as
>> >> >> datetime.
>> >> >> Is there somethign special that need to get done? When I try,
>> >> >> i get teh
>> >> >> following error:
>> >> >> Conversion invalid for datatypes on column pair 1 (source
>> >> >> column 'DoRecd'(dbstype_str), destination column
>> >> >> 'doRecd'(DBTYPE_DBtimestamp)
>> >> > You should change the source data type to datetime or use text
>> >> > for both.- Hide quoted text -
>> >> - Show quoted text -
>> > Hi,
>> > I think it be easier if you import the text file AS IS that is all
>> > columns as text (including the column with datetime) you can call
>> > it Excel_to_text_TEMP. Once you have this table imported in the
>> > server you can populate your main table from this temp table and
>> > use a conversion function for datetime etc. HTH.- Hide quoted text
>> > -
>> - Show quoted text -
> Hi,
> Then you should drop the TEMP table and re-create it where all columns
> should be varchar(255) or varchar(1000) etc. Then once the temp table
> is loaded you should run a query such as (preferably in a sored
> procedure, see Books online for syntax):
> insert Excel_To_Text (
> column1,
> column2,
> etc...,
> date_Column1,
> etc...)
> select column1,
> column2,
> etc...,
> convert(datetime,Date_Column1),
> etc...
> from Excel_To_Text_TEMP
> Let me know if you have questions. Regards,
> SB
You might eliminate the text file and instead:
SELECT F1 AS [Col1], ... CAST(Fn AS datetime) AS [DateCol], ...
INTO [DestinationTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=full_path_to\filename.xls',
'SELECT * FROM [SheetName$]')
Use the Microsoft.ACE.OLEDB.12.0 provider if you don't have Jet or if the
Excel version is 2007 (and, for Excel 2007, change Excel 8.0 to Excel
12.0).
You will probably find this a little tricky to get working (I surely
did!) so build up from a simple import, e.g. of a single integer column.sql

Reading a transaction log file

I'm having alot of trouble reading a SQL 2000 transaction log backup file.
As far as I can see, Microsoft do not provide such a tool and so far I've
tried a product called "SLR" and "ApexSQL Log". Both look OK and work OK with
"small" log files.
My problem is that the file I need to look at is 581MB and both products
just fail miserably when trying to look at these files (I think there are
about 1.3 million transcations in this file).
Before asking why I need to look at this file, I must stress that its
because of problem (server running out of disk space, so log backups were not
happening) that the log grew so big. Unfortunately, the site does not have a
DBA and I've been called in, as this all came to light when 5 important
tables appeared to have been "re-created" (dropped and created empty, with no
indexes, etc...). That has all been recovered, but they want to know how this
happened!!
I know for a fact that this 581mb log backup contains the transactions that
did the damage.
Any other ideas will be welcome!!
Is there not a command line utility, such simpy dumps all the info in these
file to a nice readable text file?? Or should I just persevere with the 2
products mentioned above?
Thanks
Hi,
There is no command line utility to convert the transaction log to text
file. Probably you could try the Logexplorer from Lumigent.
www.lumigent.com
Thanks
Hari
SQL Server MVP
"Jason Harrington" <Jason Harrington@.discussions.microsoft.com> wrote in
message news:A0EAB1FD-BAC2-4201-8153-695DEEF11210@.microsoft.com...
> I'm having alot of trouble reading a SQL 2000 transaction log backup file.
> As far as I can see, Microsoft do not provide such a tool and so far I've
> tried a product called "SLR" and "ApexSQL Log". Both look OK and work OK
> with
> "small" log files.
> My problem is that the file I need to look at is 581MB and both products
> just fail miserably when trying to look at these files (I think there are
> about 1.3 million transcations in this file).
> Before asking why I need to look at this file, I must stress that its
> because of problem (server running out of disk space, so log backups were
> not
> happening) that the log grew so big. Unfortunately, the site does not have
> a
> DBA and I've been called in, as this all came to light when 5 important
> tables appeared to have been "re-created" (dropped and created empty, with
> no
> indexes, etc...). That has all been recovered, but they want to know how
> this
> happened!!
> I know for a fact that this 581mb log backup contains the transactions
> that
> did the damage.
> Any other ideas will be welcome!!
> Is there not a command line utility, such simpy dumps all the info in
> these
> file to a nice readable text file?? Or should I just persevere with the
> 2
> products mentioned above?
> Thanks
|||I'm currently in discussion with a UK reseller of this product. The
evaluation version of this product only allows you to run against "northwind"
database and one of their own.
I asked if it would be able to read a 581mb file and they said if we were
interested in buying the product, they would test it for us!!!
Not really what I wanted to hear!!
I've been running SLR all day since 9.30am this morning, its now 3.30pm and
its about 75% of the way through reading it!!! We 'll see.....
Thanks for your response.
Jason
"Hari Prasad" wrote:

> Hi,
> There is no command line utility to convert the transaction log to text
> file. Probably you could try the Logexplorer from Lumigent.
> www.lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Jason Harrington" <Jason Harrington@.discussions.microsoft.com> wrote in
> message news:A0EAB1FD-BAC2-4201-8153-695DEEF11210@.microsoft.com...
>
>
|||We use Log Explorer and I've used it on much bigger log files than that with
no real issues.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jason Harrington" <JasonHarrington@.discussions.microsoft.com> wrote in
message news:907814B5-F9D3-492A-B024-E8DC00EFF538@.microsoft.com...[vbcol=seagreen]
> I'm currently in discussion with a UK reseller of this product. The
> evaluation version of this product only allows you to run against
> "northwind"
> database and one of their own.
> I asked if it would be able to read a 581mb file and they said if we were
> interested in buying the product, they would test it for us!!!
> Not really what I wanted to hear!!
> I've been running SLR all day since 9.30am this morning, its now 3.30pm
> and
> its about 75% of the way through reading it!!! We 'll see.....
> Thanks for your response.
> Jason
> "Hari Prasad" wrote:

Reading a text file from a stored procedure.

A rookie question - all I want to do is open a text file x.txt and read each line - no bcp or bulk insert required.
Is there a simple way to do this ?
Thanks in advance to all who reply !Not with ANSI-92 syntax. You'd have to use specific DBMS extensions for it. Pick the engine (Oracle, SQL Server, UDB, etc.) and post accordingly.|||I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply !|||I use sp_OAxxx with FileSystemObject.|||Originally posted by BrutusBuckeye
I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply ! In my opinion, this is one of the fundamental design flaws in Oracle. They are attempting to make PL/SQL a programming language instead of a data[base] manipulation language.

If you stop and think about it, reading text the way that you want to do it is a client side activity. Using BCP or BULK INSERT are server side activities. There is a fundamental difference between them (which machine the code actually runs on)!

Any solution you find for MS-SQL will involve server side activity. Sybase (now Microsoft) never intended for Transact-SQL scripts to run on the client, they always assumed that those Transact-SQL scripts would run on the server. That is exactly why user interface code, file access, etc are absent from Transact-SQL... The absence is by design.

Using Microsoft Transact-SQL, you'll need to either adopt a server centric point of view, or write your client side code using the client language. There is a clear distinction between the client and server in Transact-SQL.

-PatP|||BrutusBuckeye, Pat has a very strong opinion about all this ;)

I'd still use sp_OAxxx if you insist on reading a text file one line at a time, but why bother? Use BULK INSERT and then deal with it in a recordset-based fashion!|||UNCLE !!!! :)

Thanks for all your help. I am indeed going the Bulk Insert route.

Thanks again from the school of hard knocks.. :)|||Originally posted by rdjabarov
BrutusBuckeye, Pat has a very strong opinion about all this ;) Dang! Did I let that secret out again ?!?!

-PatP

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
Greg Stigers, MCSA
remember to vote for the answers you like
NT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/de...classtopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
--
Greg Stigers, MCSA
remember to vote for the answers you likeNT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/d...rclasstopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>sql

Reading a log file

Hi all,
Is it possible to read a log file to see what changes have been done on a
particular table at a particular time? And if yes, how?
Thanks,
Ivan> Is it possible to read a log file to see what changes have been done on a
> particular table at a particular time? And if yes, how?
http://www.aspfaq.com/2449|||Keep in mind that transaction logs are truncated during backups.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OsmBwZfjFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Is it possible to read a log file to see what changes have been done on a
> particular table at a particular time? And if yes, how?
> Thanks,
> Ivan
>

Reading a flat file

I get the following error when reading a flat file : [Credit Information 1 [1]] Error: Data conversion failed. The data conversion for column "AccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I did check all the mappings, and everything seems to be fine, the field is read in as a string. I also check for any strange characters that can possibly cause this error but the value of the field only contains a person's name and spaces at the end.

Does anyone have any ideas what might be the cause of the error?What is the source data type of the AccountName field?

What is the data type of the mapped field in SSIS?

What code page are you working with? 1252?sql

Reading a Filename which has a Wildcard

Hi - I need to read a file but I do not know the whole filename!

For example, I need to read a file whose name is:- Finance_200510.xls

I know the prefix is "Finance" and I know the suffix is ".xls", but the bit in the middle could be anything. In the above example, it is a date which will vary from month to month.

I could achieve this as follows:-

1. I already need a .CMD file which contains a DTEXEC command.
2. So, I could insert the following line before the DTEXEC command:-
DIR Finance*.xls > ListFile.txt
(Alternatively, this command could be invoked from within the Package)
3. I could read the file: ListFile.txt using a SSIS Script Component
and extract the first occurrence of the string starting with "Finance"
and ending with ".xls" and save the filename to a package variable: @.[user::filename]
4. The package variable @.[user::filename] could be used in the expression property of a Connection Manager
5. The Connection Manager could then read the file.
6. Optionally, I could generate a RENAME command using a Script Component to rename Finance_200510.xls to Finance_200510.done so that it is not re-processed.

Can anybody suggest a simpler solution, please?!

Thanks.You can use multiFlatfile connection to provide file name with wildcard. In your case, you will have to make sure that there are no other unwanted files in that folder which might match the wildcard you are using. This will eliminate the need for listing all the files in the folder, and use script component to get the file whose name starts with "Finance". In your multiflatfile connection, you can just use Finance*.xls, and it will catch your needed file.

See BOL topic
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/31fc3f7a-d323-44f5-a907-1fa3de66631a.htm
for more on Multiple Flat File Connection Manager. The ideal use for Multiple flat file connection manager is to process more than one file which are of the same format (instead of using For Each Loop), but it will also solve your purpose of choosing file with Name that matches a wildcard.

HTH,
Ranjeeta Nanda|||Or similarly, you could use the Foreach Loop with the For Each File enumerator and a file pattern of "Finance*.xls."

If you really really wanted the exact name of the file before you started processing, or needed to check for other conditions, you could use the System.IO namespace within a Script Task to retrieve files that match the pattern, make sure there's only one, and pop the filename into a package variable for use by the next task.

-Doug

Reading a file using UTL_FILE.

Hai,

I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.

So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.

Do we need to restart oracle ?
will be happy if anyone can advice.Is your UTL_FILE_DIR parameter correct in init.ora ?

If not add it and restart database

Originally posted by Anandraj
Hai,

I am working in an IBM AIX machine, with Oracle 8i.
I am trying to read a file through UTL_FILE read.
The directory which has the file does not have any permisions for the
others. It is restricted to 750.
The file belongs to a local user and group.

So, as workaround we added our local user to the secondary group of oracle, so that oracle user also has the same access permissions as the local user. But still we were not able to read the file from the procedure.

Do we need to restart oracle ?
will be happy if anyone can advice.|||Originally posted by ndu35
Is your UTL_FILE_DIR parameter correct in init.ora ?

If not add it and restart database

Hai ndu35,

Thanks for that suggestion. Anyway the entry is already present in init.ora. Also the database was restarted immediately.

Reading a file name that changes

Brand new to SSIS so bear with me, if something is obvious.

I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day.

Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt

Thanks in advance

Try using the ForEach Loop Container with a file enumerator. That will store the file name in a variable you specify. Set the connect string on the File connection manager by using an expression (@.User:<name of your variable>).

Normally the Loop container is used when you have a number of files, but it works just fine with one.

Reading a directory

I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.

The pivotal step in this is that i run a command that looks like:

CREATE TABLE #DIR (FileName varchar(100))

DECLARE @.Cmd varchar(1050)
SET@.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'

INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!

...Until I try to put that information into a table while calling that
stored procedure:

CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'

When I run this I get:

Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.

Because I use an INSERT EXEC to with the results from the @.Cmd.

Anybody have any ideas how I can get that information into a table?

I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.

So, I would appreciate anybody who can help.

Thanks!

-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||Maybe I'm bot understanding your problem correctly ,, but if you did
CREATE TABLE #DIR (FileName varchar(100))

Quote:

Originally Posted by

>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

INSERT INTO myTABLE
SELECT filename FROM #DIR

would that not do the job?

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

|||utah,

You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.

-- Bill

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

|||(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'

Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.

Quote:

Originally Posted by

When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?

I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Reading .Txt file informtion by SSIS

Hi,

I want to read and get information of Database name, userid, password from a text file by Integration services 2005.

Please let me know..

Thanks

Hi Anurag,

For that you need to use Package configuration. Add the desired configuration managers (eg. an ole db connection manager). Then from the menu "SSIS" choose "Package Configrations..." and add a new XML configuration file. Choose which proporties you want to be able to configure and click finish. Now you have a XML file that you easily can change the settings from.

Regards
Simon
sql

Wednesday, March 28, 2012

Read xml file, store data in db. What could be easier?

OK guys,
I am going to receive an xml doc and am going to have to parse the data
values and store them in the database. About 10 db tables are targets for the
data, with repeating groups of data and such. I have not done this with Sql
Server before. I need to be pointed in the right direction. So start
pointing!
Thanks,
Michael
These xpath query samples are suitable for COM and
for .NET.
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:58E7DD5B-CEAF-4677-902A-0A7BBBF0DA30@.microsoft.com...
> OK guys,
> I am going to receive an xml doc and am going to have to parse the data
> values and store them in the database. About 10 db tables are targets for
> the
> data, with repeating groups of data and such. I have not done this with
> Sql
> Server before. I need to be pointed in the right direction. So start
> pointing!
> Thanks,
> Michael
sql

read xml file content - sql server 2005

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

--SQL Server 2005

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Create table #xmlcontent

(

Xml varchar(max)

)

Insert into #xmlcontent

Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')

Select XML From #XMLContent

Code Snippet

--SQL Server 2000

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Set @.Path = 'Type ' + @.Path

Create Table #XMLLines

(

Line Varchar(8000)

)

Insert Into #XMLLines

Exec xp_cmdshell @.Path

Select * from #XMLLines

|||

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function

Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.

So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.

|||Many thanks|||

This sounds like a perfect job for SSIS!!

read xml file content

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

|||

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

|||Many thanks|||

This sounds like a perfect job for SSIS!!

Read Write Variable Cannot access before PostExecute

I have a for each loop on a directory of files, each file has to be imported with a unique surrogate key added. For this I am selecting the max id that exists in the target table and assigning that value to a variable.

Within a script transformation I am copying this value to a variable declared in a script task, and incrementing it on each row processed. Obviously I now want to write this value back out to the higher scoped variable so it is available for the next file.

If I make the variable read write it is not even available for reading until the PostExecute. Is this correct or have I missed something?

To work round this I have created a second higher scoped variable that I can write to in the PostExecute and the other variable is passed in as read only and added another script task to update the variable values.Philip is a colleague of mine and we've just been taking a look at this.

The workaround is to use a multiflatfile adapter because the metadata of the files is identical.

Philip's requirement to be able to read a ReadWriteVariable in PreExecute() is, I feel, a valid one. Is there a reason that this cannot be done?

-Jamie|||You can actually access write-able variables anywhere you want just not the ones on the read/write list. The component has a VariableDispenser that you can lock variables for read and/or write and use them as you will. The limitation we place is only for the ones you specify in the ReadWriteVariables line and this was done to keep locking to a minimum. If we gave access during row processing then because we don't know the usage we would need to keep the variable locked during the entire ProcessInput call. If some other transform needed this variable as well then we have concurrency issues. This way the user can lock variables for write but has to do it explicitly so that it can be unlocked explicitly as well and hopefully the locking time can be kept to a minimum since the script author is controlling the locking.

HTH,
Matt

Read UNIX file from SQL Server

Is it possibel to run a DTS job to read a file from unix server?It sure is, if the file is on NFS partition.|||And you should install the UNIX drive on Windows machine.|||How do I get the unix drivers for Windows?|||Check the website of UNIX operating system to download these drives. Or, call UNIX operating system maker to ask them. Maybe this paper is useful for you. http://www.databasejournal.com/features/mssql/article.php/1756161

read trn log backup files

What product is out there that can read a Transaction Backup file?
A restore was done but they didn't apply a couple of transaction logs. I
need to see what data is in these backup logs. If there is a product that
can do this can it apply the data from the logs or dump it to a file?
Thanks.I've listed some on my links page:
http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message news:OS70VXD3HHA.212@.TK2MSFTNGP05.phx.gbl...
> What product is out there that can read a Transaction Backup file?
> A restore was done but they didn't apply a couple of transaction logs. I
> need to see what data is in these backup logs. If there is a product that
> can do this can it apply the data from the logs or dump it to a file?
>
> Thanks.
>sql

Friday, March 30, 2012

reading a txt file

reading a txt file

reading a txt file

Reading a transaction log file

Reading a text file from a stored procedure.

reading a text file as it is written

reading a text file as it is written

Reading a log file

Reading a flat file

Reading a Filename which has a Wildcard

Reading a file using UTL_FILE.

Reading a file name that changes

Reading a directory

Reading .Txt file informtion by SSIS

Wednesday, March 28, 2012

Read xml file, store data in db. What could be easier?

read xml file content - sql server 2005

read xml file content

Read Write Variable Cannot access before PostExecute

Read UNIX file from SQL Server

read trn log backup files

Raster Catalog

Blog Archive

About Me