Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com