I'm new to full text catalogs and we have a vendor who's code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute.
Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required.
A SQL job is running the following command every minute.
exec sp_fulltext_catalog 'ResearchCatalog', 'start_incremental'
What is the typical frequency for running an incremental?
Can the messages be suppressed?
Thanks, Davestart_incremental is not a call for a timed indexing of the catalog. It simply starts an incremental population for fulltext_catalog_name based on the last time stamp of the last index to the current time of running the start_incremental. It takes the last timestamped index (there must be a column in the table that has a timestamp) and then indexes everything betwee the two times. It is different from a full index as it only updates that which it is missing instead of the entire table.
There must be something calling the procedure, whether it is a job or some kind of trigger within the software that causes it. You'll have to contact the software vendor to find out more information.
Hope that helps.|||The command is being run every minute by a SQL job. I assumed an incremental would be run perhaps once or twice a day and not every minute. What advantage is there to run this once a minute and is it possible to suppress the message seen in the log?
Dave|||I don't believe there is a way to supress it and you may not want to in case the index fails and the software vendor needs the information the log may provide to them. AFAIK, there is no way to supress indexing logs.
At a previous job, I know that Arcserve's database engine queries and writes every minute and their tech support was adamant about keeping those going. It will just depend on your vendor. I could see the advantage in updating the indexes incrementally if there is a large amount of data being written by the minute, such as in backups. Every file would be cataloged so that you could recall it easily from the backup engine and restore the file quickly. I suppose it depends on the software, but I would suggest contacting the software vendor for that information. There could be a wide range of reasons and I am only speculating based on prior experience.
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
How to deploy SQLProject from VS2005
This is a multi-part message in MIME format.
--=_NextPart_000_00A6_01C61699.4C6A9AA0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Hello,
According to documentation I'm supposed to be able to deploy SQL project = directly from VS2005. I don't have Either Build<project name> under = Build menu, neither I have Deploy <project name> for my SQL project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL Server
1.. Build the project by selecting Build <project name> from the Build = menu.
Note Resolve all build errors prior to deploying.
2.. Select Deploy <project name> from the Build menu.
Note If you receive a message informing you that the project cannot = be deployed, then you may need to manually create the assembly on the = server. This error is typically the result of referenced assemblies in = your project. For more information on manually deploying your assembly, = see the Create Assembly page in the "SQL Books Online."
--=_NextPart_000_00A6_01C61699.4C6A9AA0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
According to documentation I'm supposed = to be able to deploy SQL project directly from VS2005. I don't have Either = Build under Build menu, neither I have Deploy = for my SQL project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL = Server
Build the project by selecting Build from the Build menu.
Note
Resolve all build errors prior to deploying.
Select Deploy from the Build menu.
Note
If you receive a message informing you that the project = cannot be deployed, then you may need to manually create the assembly on = the server. This error is typically the result of referenced = assemblies in your project. For more information on manually deploying your = assembly, see the Create Assembly page in the "SQL Books = Online."=
--=_NextPart_000_00A6_01C61699.4C6A9AA0--This is a multi-part message in MIME format.
--=_NextPart_000_0042_01C617C5.60008200
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Right-click the project in Solution Explorer. There's a deploy option =there.
Most people will do it via scripts though.
HTH,
Greg
"GS" <nospam@.nowhere.com> wrote in message =news:ObaPYusFGHA.3200@.tk2msftngp13.phx.gbl...
Hello,
According to documentation I'm supposed to be able to deploy SQL =project directly from VS2005. I don't have Either Build<project name> =under Build menu, neither I have Deploy <project name> for my SQL =project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL Server
1.. Build the project by selecting Build <project name> from the =Build menu.
Note Resolve all build errors prior to deploying.
2.. Select Deploy <project name> from the Build menu.
Note If you receive a message informing you that the project cannot =be deployed, then you may need to manually create the assembly on the =server. This error is typically the result of referenced assemblies in =your project. For more information on manually deploying your assembly, =see the Create Assembly page in the "SQL Books Online."
--=_NextPart_000_0042_01C617C5.60008200
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML XMLNS:MSHelp =3D "<HEAD>">http://msdn.microsoft.com/mshelp"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dkoi8-r">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Right-click the project in Solution =Explorer. There's a deploy option there.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Most people will do it via scripts though.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>HTH,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Greg</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"GS" <<A =href=3D"mailto:nospam@.nowhere.com">nospam@.nowhere.com</A>> wrote in message <A =href=3D"news:ObaPYusFGHA.3200@.tk2msftngp13.phx.gbl">news:ObaPYusFGHA.3200=@.tk2msftngp13.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Hello,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>According to documentation I'm =supposed to be able to deploy SQL project directly from VS2005. I don't have Either Build<project name> under Build menu, neither I have Deploy =<project name> for my SQL project.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>What am I doing wrong?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>
<H1 class=3Dheading>To deploy items in a SQL Server project to a SQL =Server</H1>
<DIV class=3Dsection id=3DprocedureSection0>
<OL>
<LI>
<P>Build the project by selecting <B>Build <</B><I>project name</I><B>></B> from the <B>Build</B> menu.</P>
<DIV class=3Dalert>
<TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
<TBODY>
<TR>
<TH align=3Dleft><IMG class=3Dnote alt=3DNote =src=3D"ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddat=a/local/note.gif"></IMG>Note </TH></TR>
<TR>
<TD>
<P>Resolve all build errors prior to deploying.</P></TD></TR></TBODY></TABLE></DIV>
<LI>
<P>Select <B>Deploy <</B><I>project name</I><B>></B> from the <B>Build</B> menu.</P>
<DIV class=3Dalert>
<TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
<TBODY>
<TR>
<TH align=3Dleft><IMG class=3Dnote alt=3DNote =src=3D"ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddat=a/local/note.gif"></IMG>Note </TH></TR>
<TR>
<TD>
<P>If you receive a message informing you that the project =cannot be deployed, then you may need to manually create the assembly on =the server. This error is typically the result of referenced =assemblies in your project. For more information on manually deploying your assembly, see the <MSHelp:link tabIndex=3D0 keywords=3D"d8d1d245-c2c3-4325-be52-4fc1122c2079">Create Assembly</MSHelp:link> page in the "SQL Books =Online."</P></TD></TR></TBODY></TABLE></DIV></LI></OL></DIV></DIV></BLOCK=QUOTE></BODY></HTML>
--=_NextPart_000_0042_01C617C5.60008200--
--=_NextPart_000_00A6_01C61699.4C6A9AA0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Hello,
According to documentation I'm supposed to be able to deploy SQL project = directly from VS2005. I don't have Either Build<project name> under = Build menu, neither I have Deploy <project name> for my SQL project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL Server
1.. Build the project by selecting Build <project name> from the Build = menu.
Note Resolve all build errors prior to deploying.
2.. Select Deploy <project name> from the Build menu.
Note If you receive a message informing you that the project cannot = be deployed, then you may need to manually create the assembly on the = server. This error is typically the result of referenced assemblies in = your project. For more information on manually deploying your assembly, = see the Create Assembly page in the "SQL Books Online."
--=_NextPart_000_00A6_01C61699.4C6A9AA0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
According to documentation I'm supposed = to be able to deploy SQL project directly from VS2005. I don't have Either = Build under Build menu, neither I have Deploy = for my SQL project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL = Server
Build the project by selecting Build
Note
Resolve all build errors prior to deploying.
Select Deploy
Note
If you receive a message informing you that the project = cannot be deployed, then you may need to manually create the assembly on = the server. This error is typically the result of referenced = assemblies in your project. For more information on manually deploying your = assembly, see the Create Assembly page in the "SQL Books = Online."=
--=_NextPart_000_00A6_01C61699.4C6A9AA0--This is a multi-part message in MIME format.
--=_NextPart_000_0042_01C617C5.60008200
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Right-click the project in Solution Explorer. There's a deploy option =there.
Most people will do it via scripts though.
HTH,
Greg
"GS" <nospam@.nowhere.com> wrote in message =news:ObaPYusFGHA.3200@.tk2msftngp13.phx.gbl...
Hello,
According to documentation I'm supposed to be able to deploy SQL =project directly from VS2005. I don't have Either Build<project name> =under Build menu, neither I have Deploy <project name> for my SQL =project.
What am I doing wrong?
To deploy items in a SQL Server project to a SQL Server
1.. Build the project by selecting Build <project name> from the =Build menu.
Note Resolve all build errors prior to deploying.
2.. Select Deploy <project name> from the Build menu.
Note If you receive a message informing you that the project cannot =be deployed, then you may need to manually create the assembly on the =server. This error is typically the result of referenced assemblies in =your project. For more information on manually deploying your assembly, =see the Create Assembly page in the "SQL Books Online."
--=_NextPart_000_0042_01C617C5.60008200
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML XMLNS:MSHelp =3D "<HEAD>">http://msdn.microsoft.com/mshelp"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dkoi8-r">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Right-click the project in Solution =Explorer. There's a deploy option there.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Most people will do it via scripts though.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>HTH,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Greg</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"GS" <<A =href=3D"mailto:nospam@.nowhere.com">nospam@.nowhere.com</A>> wrote in message <A =href=3D"news:ObaPYusFGHA.3200@.tk2msftngp13.phx.gbl">news:ObaPYusFGHA.3200=@.tk2msftngp13.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Hello,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>According to documentation I'm =supposed to be able to deploy SQL project directly from VS2005. I don't have Either Build<project name> under Build menu, neither I have Deploy =<project name> for my SQL project.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>What am I doing wrong?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>
<H1 class=3Dheading>To deploy items in a SQL Server project to a SQL =Server</H1>
<DIV class=3Dsection id=3DprocedureSection0>
<OL>
<LI>
<P>Build the project by selecting <B>Build <</B><I>project name</I><B>></B> from the <B>Build</B> menu.</P>
<DIV class=3Dalert>
<TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
<TBODY>
<TR>
<TH align=3Dleft><IMG class=3Dnote alt=3DNote =src=3D"ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddat=a/local/note.gif"></IMG>Note </TH></TR>
<TR>
<TD>
<P>Resolve all build errors prior to deploying.</P></TD></TR></TBODY></TABLE></DIV>
<LI>
<P>Select <B>Deploy <</B><I>project name</I><B>></B> from the <B>Build</B> menu.</P>
<DIV class=3Dalert>
<TABLE cellSpacing=3D0 cellPadding=3D0 width=3D"100%">
<TBODY>
<TR>
<TH align=3Dleft><IMG class=3Dnote alt=3DNote =src=3D"ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddat=a/local/note.gif"></IMG>Note </TH></TR>
<TR>
<TD>
<P>If you receive a message informing you that the project =cannot be deployed, then you may need to manually create the assembly on =the server. This error is typically the result of referenced =assemblies in your project. For more information on manually deploying your assembly, see the <MSHelp:link tabIndex=3D0 keywords=3D"d8d1d245-c2c3-4325-be52-4fc1122c2079">Create Assembly</MSHelp:link> page in the "SQL Books =Online."</P></TD></TR></TBODY></TABLE></DIV></LI></OL></DIV></DIV></BLOCK=QUOTE></BODY></HTML>
--=_NextPart_000_0042_01C617C5.60008200--
Wednesday, March 7, 2012
how to delete text file
hi...
how to delete text files from c drive (ex: c:\sale\300407.txt) using ms sql express edition 2005 ?
plz help me...
how to delete text files from c drive (ex: c:\sale\300407.txt) using ms sql express edition 2005 ?
plz help me...
enable xp_cmdshell from start-- programs -- SQL Server 2005-- SQL Sserver surface Area Configuration tool -- Surface area confiugration for feature --
enable XP_Cmdshell
then run
exec xp_cmdshell 'del c:\yourfilename.txt'
Madhu
|||Be aware that if you are using Windows Authentication, the User who is accessing th e procedure will need the appropiate rights to delete the file. If you are using SQL Server authentication, it has to be the SQL Server Service account, if you don′t won′t either one, you will have to create a proxy account for the XP_CMDSHELL.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||i'm using windows authentication. the command given still error. here the error :
c:\010407_S13103.txt
Access is denied
how about delete multiple text file from folder (ex
its a permission problem. this user do not have permission in the folder. Jens's post already mention how to handle it. Basically, what xp_cmdshell used for is to run a shell command from SQL Platform. Delete multiple files use
xp_cmdshell del *.txt
Madhu
|||the user is admin. there should be no permission problem. but i still get the same error after i run the command above for multiple files. please be remind that i'm using ms sql express edition 2005.|||Are you using WIndows authentication for connecting to the database ?|||yes i'm using windows authentication|||Then you either have a proxy account configured which is not able to access the file, or the user you are currently using for the connection is not allowed, or the usage of the directory (if you using Vista) is required elevation.
|||i try this command in ms sql 2005 express edition but still get the same error. here the command :
declare @.del varchar(1000)
select @.del = 'del c:\Transfer\*.TXT'
exec xp_cmdshell @.del
here the error:
c:\Transfer\010407SALE.131.TXT
Access is denied
i'm using windows authentication and using admin account. so there should be no permission problem. i already enable xp_cmdshell in SQL Server Surface Area Configuration. why this error still occur? plz help me. i'm using ms windows xp.|||Is the folder encrypted ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||nohow to delete lines from text file
i have a text file that is like:
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?|||"billious" <billious_1954@.hotmail.comwrote in message
news:4529c1ac$0$28985$a82e2bb9@.reader.athenanews.c om...
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?
NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
techniques used differ markedly from DOS/9x methods.
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
techniques used differ markedly from DOS/9x methods.
>
For instance, given data like:
>
-- data begins ---
a|b|c
d|e|f
0|start|1
2|3|4
5|6|7
8|end|9
g|h|i
j|k|l
-- data ends ---
>
in a file "psd.txt" then the following will produce "psdout.txt"
>
-- batch begins ---
[1]@.echo off
[2]setlocal enabledelayedexpansion
[3]set yel=Y
[4]for /f %%i in (psd.txt) do call :process "%%i" &if !yel!==Y echo
%%i>>psdout.txt
[5]goto :eof
[6]
[7]:process
[8]if %yel%==L set yel=Y
[9]if %yel%==N for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==end set
yel=L
[10]if %yel%==Y for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==start
set yel=N
[11]goto :eof
-- batch ends ---
>
Lines start [number] - any lines not starting [number] have been wrapped
and should be rejoined. The [number] that starts the line should be removed
>
The label :eof is defined in NT+ to be end-of-file but MUST be expressed
as :eof
>
Without better knowledge of your file's content, refining this is a little
difficult.|||batman <uspensky@.gmail.comwrote:
Are you able to use a SED UNIX port? If yes, then the task is fairly
trivial.
[snip]
>
here is some actual content from the file (shortened ofcourse to just a
few records) ideally i would like to avoid using a batch file and keep
it all on the sql level (sql 2000)... the file pasted in here much
perttier than it looks in notepad (with the squares)
>
thanks for ur help
>
>
-- file starts below
START-OF-FILE
PROGRAMNAME=getdata
[sniparoo]
[more in this vein...]
Without knowing quite where the "squares" are, other than surrounding the
START/END-OF-FILE, and assuming that "10" is 10 decimal, not 10HEX then the
following MAY work. Can't test it without knowing where else these
non-alphamerics are or whether reproducing them is important.
(alt.msdos.batch.nt added and followups redirected)
snip...
--GIVEN this table dbo.ImportMyFile
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ImportMyFile](
[LineID] [int] IDENTITY(1,1) NOT NULL,
[LineText] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
>>
>NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
>techniques used differ markedly from DOS/9x methods.
>>
>For instance, given data like:
>>
>-- data begins ---
>a|b|c
>d|e|f
>0|start|1
>2|3|4
>5|6|7
>8|end|9
>g|h|i
>j|k|l
>-- data ends ---
>>
>in a file "psd.txt" then the following will produce "psdout.txt"
>>
>-- batch begins ---
>[1]@.echo off
>[2]setlocal enabledelayedexpansion
>[3]set yel=Y
>[4]for /f %%i in (psd.txt) do call :process "%%i" &if !yel!==Y echo
>%%i>>psdout.txt
>[5]goto :eof
>[6]
>[7]:process
>[8]if %yel%==L set yel=Y
>[9]if %yel%==N for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==end set
>yel=L
>[10]if %yel%==Y for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==start
>set yel=N
>[11]goto :eof
>-- batch ends ---
>>
>Lines start [number] - any lines not starting [number] have been wrapped
>and should be rejoined. The [number] that starts the line should be removed
>>
>The label :eof is defined in NT+ to be end-of-file but MUST be expressed
>as :eof
>>
>Without better knowledge of your file's content, refining this is a little
>difficult.
awk "{if( $0 ~ /END-OF-DATA/) f=0; if( f ) print $0; if( $0 ~
/START-OF-DATA/) f=1}" infile outfile
Also as below. The first is inclusive, the second exclusive
Tested ok for XP.
MiniTrue :
Sorry, I don't use SQL so I don't know.
date = OCT0606
asdf
sdaf
asdfasdgsdgh
asdfsdfasdg
START-OF-DATA
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
END-OF-DATA
asdfgalsdkdfklmlkm
asdfgasdfg
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
open to suggestions of how to import using bulk insert in sql without
changing the file at all. data is pipe seperated but obvioulsy has
plenty of junk data in it. i have 2 similar files at about 30mb and
60mb in size. thnks everyone"batman" <uspensky@.gmail.comwrote in message
news:1160363613.855662.104440@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
>i have a text file that is like:
>
date = OCT0606
asdf
sdaf
asdfasdgsdgh
asdfsdfasdg
START-OF-DATA
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
END-OF-DATA
asdfgalsdkdfklmlkm
asdfgasdfg
>
>
>
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
open to suggestions of how to import using bulk insert in sql without
changing the file at all. data is pipe seperated but obvioulsy has
plenty of junk data in it. i have 2 similar files at about 30mb and
60mb in size. thnks everyone
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?|||good point...
in fact the file is a text file but contains the infamous blank square
all throughout. end and start of data have the square around them as
well., i looked up the exact character and it seems to be a simple
char(10). so it would be helpful to account for this in the
batchfile/sql.
billious wrote:
Quote:
Originally Posted by
"batman" <uspensky@.gmail.comwrote in message
news:1160363613.855662.104440@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
i have a text file that is like:
date = OCT0606
asdf
sdaf
asdfasdgsdgh
asdfsdfasdg
START-OF-DATA
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
END-OF-DATA
asdfgalsdkdfklmlkm
asdfgasdfg
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
open to suggestions of how to import using bulk insert in sql without
changing the file at all. data is pipe seperated but obvioulsy has
plenty of junk data in it. i have 2 similar files at about 30mb and
60mb in size. thnks everyone
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?|||"billious" <billious_1954@.hotmail.comwrote in message
news:4529c1ac$0$28985$a82e2bb9@.reader.athenanews.c om...
Quote:
Originally Posted by
>
"batman" <uspensky@.gmail.comwrote in message
news:1160363613.855662.104440@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
>>i have a text file that is like:
>>
>date = OCT0606
>asdf
>sdaf
>asdfasdgsdgh
>asdfsdfasdg
>START-OF-DATA
>asdfasdfg
>asdfgdfgsfg
>sadfsdfgsa
>asdfgsdfg
>END-OF-DATA
>asdfgalsdkdfklmlkm
>asdfgasdfg
>>
>>
>>
>i need to clear everything from this file except the data between the
>START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
>open to suggestions of how to import using bulk insert in sql without
>changing the file at all. data is pipe seperated but obvioulsy has
>plenty of junk data in it. i have 2 similar files at about 30mb and
>60mb in size. thnks everyone
>>
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?
NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
techniques used differ markedly from DOS/9x methods.
For instance, given data like:
-- data begins ---
a|b|c
d|e|f
0|start|1
2|3|4
5|6|7
8|end|9
g|h|i
j|k|l
-- data ends ---
in a file "psd.txt" then the following will produce "psdout.txt"
-- batch begins ---
[1]@.echo off
[2]setlocal enabledelayedexpansion
[3]set yel=Y
[4]for /f %%i in (psd.txt) do call :process "%%i" &if !yel!==Y echo
%%i>>psdout.txt
[5]goto :eof
[6]
[7]:process
[8]if %yel%==L set yel=Y
[9]if %yel%==N for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==end set
yel=L
[10]if %yel%==Y for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==start
set yel=N
[11]goto :eof
-- batch ends ---
Lines start [number] - any lines not starting [number] have been wrapped
and should be rejoined. The [number] that starts the line should be removed
The label :eof is defined in NT+ to be end-of-file but MUST be expressed
as :eof
Without better knowledge of your file's content, refining this is a little
difficult.|||here is some actual content from the file (shortened ofcourse to just a
few records) ideally i would like to avoid using a batch file and keep
it all on the sql level (sql 2000)... the file pasted in here much
perttier than it looks in notepad (with the squares)
thanks for ur help
-- file starts below
START-OF-FILE
PROGRAMNAME=getdata
DATEFORMAT=yyyymmdd
START-OF-FIELDS
# Security Description
TICKER
EXCH_CODE
NAME
COUNTRY
CRNCY
SECURITY_TYP
PAR_AMT
EQY_PRIM_EXCH
EQY_PRIM_EXCH_SHRT
# Industry Classification
EQY_SIC_CODE
EQY_SIC_NAME
INDUSTRY_GROUP
INDUSTRY_SUBGROUP
INDUSTRY_SECTOR
# Identifiers
ID_SEDOL1
ID_WERTPAPIER
ID_ISIN
ID_DUTCH
ID_VALOREN
ID_FRENCH
ID_BELGIUM
ID_BB_COMPANY
ID_BB_SECURITY
ID_CUSIP
ID_COMMON
# ADRs
ADR_UNDL_TICKER
ADR_SH_PER_ADR
# Dividend Information
DVD_CRNCY
EQY_DVD_SH_12M_NET
EQY_DVD_SH_12M
EQY_DVD_SH_LAST
EQY_LAST_DPS_GROSS
EQY_DVD_PCT_FRANKED
EQY_DVD_TYP_LAST
EQY_DVD_FREQ
DVD_PAY_DT
DVD_RECORD_DT
DVD_DECLARED_DT
EQY_SPLIT_DT
EQY_SPLIT_RATIO
DVD_EX_DT
EQY_DVD_EX_FLAG
INDUSTRY_SUBGROUP_NUM
CNTRY_ISSUE_ISO
MARKET_STATUS
ID_BB_PARENT_CO
ADR_UNDL_CMPID
ADR_UNDL_SECID
REL_INDEX
PX_TRADE_LOT_SIZE
PARENT_COMP_TICKER
PARENT_COMP_NAME
ID_LOCAL
LONG_COMP_NAME
PARENT_INDUSTRY_GROUP
PARENT_INDUSTRY_SUBGROUP
PARENT_INDUSTRY_SECTOR
VOTING_RIGHTS
ID_BB_PRIM_SECURITY_FLAG
PAR_VAL_CRNCY
EQY_SH_OUT
EQY_SH_OUT_DT
ID_BB_UNIQUE
MARKET_SECTOR_DES
IS_STK_MARGINABLE
144A_FLAG
TRANSFER_AGENT
EQY_PRIM_SECURITY_TICKER
EQY_PRIM_SECURITY_COMP_EXCH
IS_SETS
WHICH_JAPANESE_SECTION
ADR_ADR_PER_SH
EQY_PRIM_SECURITY_PRIM_EXCH
EQY_FUND_CRNCY
WHEN_ISSUED
CDR_COUNTRY_CODE
CDR_EXCH_CODE
CNTRY_OF_INCORPORATION
CNTRY_OF_DOMICILE
SEC_RESTRICT
EQY_SH_OUT_REAL
ADR_UNDL_CRNCY
MULTIPLE_SHARE
PX_QUOTE_LOT_SIZE
PX_ROUND_LOT_SIZE
ID_SEDOL2
SEDOL1_COUNTRY_ISO
SEDOL2_COUNTRY_ISO
ID_MIC_PRIM_EXCH
ID_MIC_LOCAL_EXCH
EQY_SH_OUT_TOT_MULT_SH
SECURITY_TYP2
ID_BB_PRIM_SECURITY
EQY_OPT_AVAIL
EQY_FREE_FLOAT_PCT
END-OF-FIELDS
TIMESTARTED=Tue Sep 26 17:33:28 EDT 2006
START-OF-DATA
AA US Equity|0|95|AA|US|ALCOA INC|US|USD|Common
Stock|1.000000000|New York|UN|3334|ALUMINUM
PROD|Mining|Metal-Aluminum|Basic
Materials|2021805|850206|US0138171014|N.A.|N.A.|90 2130|094464|100046|1000|013817101|009988106|
| |USD|.600|.600|.15|.15| |Regular
Cash|Quarter|20061125|20061103|20060915|20000612|2 for
1|20061101|N|14|US|ACTV| | | |SPX|1| | |N.A.|Alcoa Inc| | |
|1.000|Y|USD|866.888|20060721|EQ0010004600001000|E quity|N.A.|N|EquiServe/First
Chicago Trust Co Div|AA|US|N| | |UN|USD|N|US|EX|US|US|0|866888257|
|N|1.00|100|N.A.|US|N.A.|XNYS| |866.888|Common Stock|1000|Y|99.6964|
AALA US Equity|0|95|AALA|US|AMERALIA INC|US|USD|Common
Stock|.010000000|OTC US|UV|N.A.| |Mining|Quarrying|Basic
Materials|2023588|880772|US0235592062|N.A.|N.A.|N. A.|N.A.|101793|1000|023559206|N.A.|
| | | | | | | | |None| | | |19930119|1 for 40| |N|18|US|ACTV| | |
|SPX|1| | |N.A.|Ameralia Inc| | |
|1.000|Y|USD|16.866|20040901|EQ0010179300001000|Eq uity|N.A.|N|Atlas
Stock Trasfer, Inc.|AALA|US|N| | |UV| |N|US|EX|US|US|0|16866301|
|N|1.00|100|N.A.|US|N.A.|XOTC| |16.866|Common Stock|1000|N|37.4822|
CALS CN Equity|0|95|CALS|CN|ASPIRE CAPITAL INC|CA|CAD|Common
Stock|N.A.|CNQ|CF|N.A.| |Investment Companies|Capital
Pools|Financial|N.A.|N.A.|CA04537P1080|N.A.|N.A.|N .A.|N.A.|1185676|1001|04537P108|N.A.|
|
|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |N.A.|N.A.|N.A.|N.A.|N.A.|702|CA|ACTV|
| | |SPTSX|1| | |N.A.|Aspire Capital Inc| | | |.000|Y|
|N.A.|N.A.|EQ0000000002830848|Equity|N.A.|N|Comput ershare Investor
Services Inc.|CALS|CN|N| | |CF| |N|CA|CX|CA|CA|0|N.A.|
|N|1.00|100|N.A.|N.A.|N.A.|XCNQ| |.000|Common Stock|1001|N|N.A.|
805208Z CN Equity|0|95|805208Z|CN|CUDA CAPITAL CORP|CA|CAD|Common
Stock|N.A.|Venture|CV|N.A.|
|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |11105828|1000|N.A.|N.A.|
|
|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|CA|PEND|
| | |SPTSX|1| | |N.A.|Cuda Capital Corp| | | |.000|Y|
|N.A.|N.A.|EQ0000000002831616|Equity|N.A.|N|N.A.|8 05208Z|CN|N| | |CV|
|N|CA|CX|CA|CA|0|N.A.| |N|1.00|100|N.A.|N.A.|N.A.|XTSX| |.000|Common
Stock|1000|N|N.A.|
END-OF-DATA
DATARECORDS=57129
TIMEFINISHED=Tue Sep 26 17:51:45 EDT 2006
END-OF-FILE
-- file end above
billious wrote:
Quote:
Originally Posted by
"billious" <billious_1954@.hotmail.comwrote in message
news:4529c1ac$0$28985$a82e2bb9@.reader.athenanews.c om...
Quote:
Originally Posted by
"batman" <uspensky@.gmail.comwrote in message
news:1160363613.855662.104440@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
>i have a text file that is like:
>
date = OCT0606
asdf
sdaf
asdfasdgsdgh
asdfsdfasdg
START-OF-DATA
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
END-OF-DATA
asdfgalsdkdfklmlkm
asdfgasdfg
>
>
>
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
open to suggestions of how to import using bulk insert in sql without
changing the file at all. data is pipe seperated but obvioulsy has
plenty of junk data in it. i have 2 similar files at about 30mb and
60mb in size. thnks everyone
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?
>
NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
techniques used differ markedly from DOS/9x methods.
>
For instance, given data like:
>
-- data begins ---
a|b|c
d|e|f
0|start|1
2|3|4
5|6|7
8|end|9
g|h|i
j|k|l
-- data ends ---
>
in a file "psd.txt" then the following will produce "psdout.txt"
>
-- batch begins ---
[1]@.echo off
[2]setlocal enabledelayedexpansion
[3]set yel=Y
[4]for /f %%i in (psd.txt) do call :process "%%i" &if !yel!==Y echo
%%i>>psdout.txt
[5]goto :eof
[6]
[7]:process
[8]if %yel%==L set yel=Y
[9]if %yel%==N for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==end set
yel=L
[10]if %yel%==Y for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==start
set yel=N
[11]goto :eof
-- batch ends ---
>
Lines start [number] - any lines not starting [number] have been wrapped
and should be rejoined. The [number] that starts the line should be removed
>
The label :eof is defined in NT+ to be end-of-file but MUST be expressed
as :eof
>
Without better knowledge of your file's content, refining this is a little
difficult.|||batman <uspensky@.gmail.comwrote:
Quote:
Originally Posted by
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file...
Are you able to use a SED UNIX port? If yes, then the task is fairly
trivial.
878915 Oct 25 2003 ftp://garbo.uwasa.fi/win95/unix/UnxUpdates.zip
UnxUpdates.zip Updates for UnxUtils GNU utilities for native Win32
If not, in XP (the newsgroup for that is alt.msdos.batch.nt) then you'll
first have to find out the line numbers of your delimiter, and the write
out the lines between and including those line numbers. More on that e.g. in
176733 Sep 11 2006 ftp://garbo.uwasa.fi/pc/link/tscmd.zip
tscmd.zip Useful NT/2000/XP script tricks and tips, T.Salmi
Windows Command Line Interface script programming links
http://www.uwasa.fi/~ts/http/http2.html#cmdscript
Followups set to alt.msdos.batch.nt
For all, asking PC batch programming related questions. You'll enhance
your chances and expedite getting answers if you state your OS when asking!
All the best, Timo
--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance ; University of Vaasa
mailto:ts@.uwasa.fi <http://www.uwasa.fi/~ts/ ; FIN-65101, Finland
Timo's FAQ materials at http://www.uwasa.fi/~ts/http/tsfaq.html|||"batman" <uspensky@.gmail.comwrote in message
news:1160368462.065423.142350@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
billious wrote:
Quote:
Originally Posted by
>>i have a text file that is like:
>>
>>date = OCT0606
>>asdf
>>START-OF-DATA
>>asdfasdfg
>>asdfgsdfg
>>END-OF-DATA
>>asdfgalsdkdfklmlkm
>>
>>i need to clear everything from this file except the data between the
>>START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
>>open to suggestions of how to import using bulk insert in sql without
>>changing the file at all. data is pipe seperated but obvioulsy has
>>plenty of junk data in it. i have 2 similar files at about 30mb and
>>60mb in size. thnks everyone
>>
>>
>Since you are posting from XP, do you want an XP solution?
>alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
>9x.
>>
[snip]
Quote:
Originally Posted by
Quote:
Originally Posted by
>Without better knowledge of your file's content, refining this is a
>little
>difficult.
>
here is some actual content from the file (shortened ofcourse to just a
few records) ideally i would like to avoid using a batch file and keep
it all on the sql level (sql 2000)... the file pasted in here much
perttier than it looks in notepad (with the squares)
>
thanks for ur help
>
>
-- file starts below
START-OF-FILE
PROGRAMNAME=getdata
[sniparoo]
Quote:
Originally Posted by
END-OF-FIELDS
>
TIMESTARTED=Tue Sep 26 17:33:28 EDT 2006
START-OF-DATA
AA US Equity|0|95|AA|US|ALCOA INC|US|USD|Common
Stock|1.000000000|New York|UN|3334|ALUMINUM
PROD|Mining|Metal-Aluminum|Basic
[more in this vein...]
Quote:
Originally Posted by
Stock|1000|N|N.A.|
END-OF-DATA
DATARECORDS=57129
TIMEFINISHED=Tue Sep 26 17:51:45 EDT 2006
END-OF-FILE
>
-- file end above
Without knowing quite where the "squares" are, other than surrounding the
START/END-OF-FILE, and assuming that "10" is 10 decimal, not 10HEX then the
following MAY work. Can't test it without knowing where else these
non-alphamerics are or whether reproducing them is important.
-- batch begins ---
[1]@.echo off
[2]setlocal enabledelayedexpansion
[3]set yel=N
[4]for /f "tokens=*" %%i in (psdx.txt) do call :process "%%i" &if !yel!==Y
echo %%i>>psdout.txt
[5]goto :eof
[6]
[7]:process
[8]:: if last-line-processed was "START-OF-DATA" switch ON echoing
[9]if %yel%==F set yel=Y
[10]:: get just first token - removing embarrassing pipes
[11]for /f "tokens=1delims=|" %%j in (%1) do set ytd=%%j
[12]if not defined ytd goto :eof
[13]:: remove nasty squares either end of "START/END-OF-DATA"
[14]::set ytd=%ytd:~1,-1%
[15]if "%ytd%"=="START-OF-DATA" set yel=F
[16]if "%ytd%"=="END-OF-DATA" set yel=N
[17]goto :eof
-- batch ends ---
Lines start [number] - any lines not starting [number] have been wrapped
and should be rejoined. The [number] that starts the line should be removed
The label :eof is defined in NT+ to be end-of-file but MUST be expressed
as :eof
[14] is deliberately commented-out. If the "square" is ^J (10 decimal) then
the process appears to work as-is. If the square is ^P (10 HEX) then you'd
need to remove the colons from [14]
And I obviously haven't checked that the reproduced data is PRECISELEY the
same as the source.
And PLEASE end-post. Top-posting is for emails, not Usenet.|||batman <uspensky@.gmail.comwrote:
Quote:
Originally Posted by
i have a text file that is like:
>
date = OCT0606
asdf
sdaf
asdfasdgsdgh
asdfsdfasdg
START-OF-DATA
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
END-OF-DATA
asdfgalsdkdfklmlkm
asdfgasdfg
>
i need to clear everything from this file except the data between the
START-OF-DATA and END-OF-DATA using a batcj file...
(alt.msdos.batch.nt added and followups redirected)
@.echo off & setlocal enableextensions enabledelayedexpansion
set source=C:\_M\MyData.txt
set flagStart=
set flagEnd=
for /f %%a in ('type "%source%"') do (
echo %%a|find "START-OF-DATA">nul
if !errorlevel! EQU 0 set flagStart=true
echo %%a|find "END-OF-DATA">nul
if !errorlevel! EQU 0 set flagEnd=true
if defined flagStart if not defined FlagEnd (
echo %%a|find /v "START-OF-DATA"
)
)
endlocal & goto :EOF
The output
C:\_D\TEST>cmdfaq
asdfasdfg
asdfgdfgsfg
sadfsdfgsa
asdfgsdfg
All the best, Timo
--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance ; University of Vaasa
mailto:ts@.uwasa.fi <http://www.uwasa.fi/~ts/ ; FIN-65101, Finland
Useful script files and tricks ftp://garbo.uwasa.fi/pc/link/tscmd.zip|||"batman" <uspensky@.gmail.comwrote in message
news:1160368462.065423.142350@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
here is some actual content from the file (shortened ofcourse to just a
few records) ideally i would like to avoid using a batch file and keep
it all on the sql level (sql 2000)... the file pasted in here much
perttier than it looks in notepad (with the squares)
>
thanks for ur help
>
>
-- file starts below
snip...
In Windows 98 I would use the following (tested) batch which uses VBScript
to extract the portion indicated. The extract will be output to a file named
out.txt and opened in notepad for viewing. This batch should work in Windows
XP also (untested).
@.echo off
echo$.vbs filename = "test.txt"
echo>>$.vbs s = "START-OF-DATA"
echo>>$.vbs e = "END-OF-DATA"
echo>>$.vbs Set fso = CreateObject("Scripting.FileSystemObject")
echo>>$.vbs Set f = fso.GetFile(filename)
echo>>$.vbs filesize = f.size
echo>>$.vbs Set f = fso.OpenTextFile (filename, 1, True)
echo>>$.vbs fin = f.Read(filesize)
echo>>$.vbs f.close
echo>>$.vbs s = InStr(fin, s) + 15
echo>>$.vbs e = InStr(fin, e)
echo>>$.vbs clip = Mid(fin, s , e - s - 2)
echo>>$.vbs Wscript.Echo clip
cscript /nologo $.vbs out.txt
del $.vbs
notepad out.txt
--
Todd Vargo
(Post questions to group only. Remove "z" to email personal messages)|||havent gotten a chance to test your replies yet guy but thank you. i
should be able to test them tomorrow. is there a way to do all this
within sql script? this will run using an automated process so i need
to eliminate the number of steps anywhere i can.|||batman wrote:
Quote:
Originally Posted by
i have a text file that is like:
>
--GIVEN this table dbo.ImportMyFile
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ImportMyFile](
[LineID] [int] IDENTITY(1,1) NOT NULL,
[LineText] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
-- and your file example here named 'C:\MyFile.txt'
-- and this simple format file named C:\MyFormatFile.txt
9.0
1
1 SQLCHAR 0 1000 "\r\n" 2 LineText
""
-- execute this T-SQL
BULK INSERT AdventureWorks.dbo.ImportMyFile
FROM 'C:\MyFile.txt' WITH (FORMATFILE='C:\MyFormatFile.txt');
GO
DELETE FROM dbo.ImportMyFile
WHERE LineID < =(SELECT LineID FROM dbo.ImportMyFile WHERE LineText =
'START-OF-FILE ')
OR LineID >=(SELECT LineID FROM dbo.ImportMyFile WHERE LineText =
'END-OF-FILE')
See these topics in Books Online
BULK INSERT (Transact-SQL)
Understanding Non-XML Format Files
Using a Format File to Skip a Table Column
variations are endless.|||On 8 Oct 2006 21:34:22 -0700, "batman" <uspensky@.gmail.comwrote:
Quote:
Originally Posted by
>here is some actual content from the file (shortened ofcourse to just a
>few records) ideally i would like to avoid using a batch file and keep
>it all on the sql level (sql 2000)... the file pasted in here much
>perttier than it looks in notepad (with the squares)
>
>thanks for ur help
>
>
>-- file starts below
>START-OF-FILE
>PROGRAMNAME=getdata
>DATEFORMAT=yyyymmdd
>
>START-OF-FIELDS
># Security Description
>TICKER
>EXCH_CODE
>NAME
>COUNTRY
>CRNCY
>SECURITY_TYP
>PAR_AMT
>EQY_PRIM_EXCH
>EQY_PRIM_EXCH_SHRT
>
># Industry Classification
>EQY_SIC_CODE
>EQY_SIC_NAME
>INDUSTRY_GROUP
>INDUSTRY_SUBGROUP
>INDUSTRY_SECTOR
>
># Identifiers
>ID_SEDOL1
>ID_WERTPAPIER
>ID_ISIN
>ID_DUTCH
>ID_VALOREN
>ID_FRENCH
>ID_BELGIUM
>ID_BB_COMPANY
>ID_BB_SECURITY
>ID_CUSIP
>ID_COMMON
>
># ADRs
>ADR_UNDL_TICKER
>ADR_SH_PER_ADR
>
># Dividend Information
>DVD_CRNCY
>EQY_DVD_SH_12M_NET
>EQY_DVD_SH_12M
>EQY_DVD_SH_LAST
>EQY_LAST_DPS_GROSS
>EQY_DVD_PCT_FRANKED
>EQY_DVD_TYP_LAST
>EQY_DVD_FREQ
>DVD_PAY_DT
>DVD_RECORD_DT
>DVD_DECLARED_DT
>EQY_SPLIT_DT
>EQY_SPLIT_RATIO
>DVD_EX_DT
>EQY_DVD_EX_FLAG
>
>INDUSTRY_SUBGROUP_NUM
>CNTRY_ISSUE_ISO
>MARKET_STATUS
>ID_BB_PARENT_CO
>ADR_UNDL_CMPID
>ADR_UNDL_SECID
>REL_INDEX
>PX_TRADE_LOT_SIZE
>PARENT_COMP_TICKER
>PARENT_COMP_NAME
>ID_LOCAL
>LONG_COMP_NAME
>PARENT_INDUSTRY_GROUP
>PARENT_INDUSTRY_SUBGROUP
>PARENT_INDUSTRY_SECTOR
>VOTING_RIGHTS
>ID_BB_PRIM_SECURITY_FLAG
>PAR_VAL_CRNCY
>EQY_SH_OUT
>EQY_SH_OUT_DT
>ID_BB_UNIQUE
>MARKET_SECTOR_DES
>IS_STK_MARGINABLE
>144A_FLAG
>TRANSFER_AGENT
>EQY_PRIM_SECURITY_TICKER
>EQY_PRIM_SECURITY_COMP_EXCH
>IS_SETS
>WHICH_JAPANESE_SECTION
>ADR_ADR_PER_SH
>EQY_PRIM_SECURITY_PRIM_EXCH
>EQY_FUND_CRNCY
>WHEN_ISSUED
>CDR_COUNTRY_CODE
>CDR_EXCH_CODE
>CNTRY_OF_INCORPORATION
>CNTRY_OF_DOMICILE
>SEC_RESTRICT
>EQY_SH_OUT_REAL
>ADR_UNDL_CRNCY
>MULTIPLE_SHARE
>PX_QUOTE_LOT_SIZE
>PX_ROUND_LOT_SIZE
>ID_SEDOL2
>SEDOL1_COUNTRY_ISO
>SEDOL2_COUNTRY_ISO
>ID_MIC_PRIM_EXCH
>ID_MIC_LOCAL_EXCH
>EQY_SH_OUT_TOT_MULT_SH
>SECURITY_TYP2
>ID_BB_PRIM_SECURITY
>EQY_OPT_AVAIL
>EQY_FREE_FLOAT_PCT
>END-OF-FIELDS
>
>TIMESTARTED=Tue Sep 26 17:33:28 EDT 2006
>START-OF-DATA
>AA US Equity|0|95|AA|US|ALCOA INC|US|USD|Common
>Stock|1.000000000|New York|UN|3334|ALUMINUM
>PROD|Mining|Metal-Aluminum|Basic
>Materials|2021805|850206|US0138171014|N.A.|N.A.|90 2130|094464|100046|1000|013817101|009988106|
>| |USD|.600|.600|.15|.15| |Regular
>Cash|Quarter|20061125|20061103|20060915|20000612|2 for
>1|20061101|N|14|US|ACTV| | | |SPX|1| | |N.A.|Alcoa Inc| | |
>|1.000|Y|USD|866.888|20060721|EQ0010004600001000|E quity|N.A.|N|EquiServe/First
>Chicago Trust Co Div|AA|US|N| | |UN|USD|N|US|EX|US|US|0|866888257|
>|N|1.00|100|N.A.|US|N.A.|XNYS| |866.888|Common Stock|1000|Y|99.6964|
>AALA US Equity|0|95|AALA|US|AMERALIA INC|US|USD|Common
>Stock|.010000000|OTC US|UV|N.A.| |Mining|Quarrying|Basic
>Materials|2023588|880772|US0235592062|N.A.|N.A.|N. A.|N.A.|101793|1000|023559206|N.A.|
>| | | | | | | | |None| | | |19930119|1 for 40| |N|18|US|ACTV| | |
>|SPX|1| | |N.A.|Ameralia Inc| | |
>|1.000|Y|USD|16.866|20040901|EQ0010179300001000|Eq uity|N.A.|N|Atlas
>Stock Trasfer, Inc.|AALA|US|N| | |UV| |N|US|EX|US|US|0|16866301|
>|N|1.00|100|N.A.|US|N.A.|XOTC| |16.866|Common Stock|1000|N|37.4822|
>CALS CN Equity|0|95|CALS|CN|ASPIRE CAPITAL INC|CA|CAD|Common
>Stock|N.A.|CNQ|CF|N.A.| |Investment Companies|Capital
>Pools|Financial|N.A.|N.A.|CA04537P1080|N.A.|N.A.|N .A.|N.A.|1185676|1001|04537P108|N.A.|
>|
>|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |N.A.|N.A.|N.A.|N.A.|N.A.|702|CA|ACTV|
>| | |SPTSX|1| | |N.A.|Aspire Capital Inc| | | |.000|Y|
>|N.A.|N.A.|EQ0000000002830848|Equity|N.A.|N|Comput ershare Investor
>Services Inc.|CALS|CN|N| | |CF| |N|CA|CX|CA|CA|0|N.A.|
>|N|1.00|100|N.A.|N.A.|N.A.|XCNQ| |.000|Common Stock|1001|N|N.A.|
>805208Z CN Equity|0|95|805208Z|CN|CUDA CAPITAL CORP|CA|CAD|Common
>Stock|N.A.|Venture|CV|N.A.|
>|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |11105828|1000|N.A.|N.A.|
>|
>|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|N.A. |N.A.|N.A.|N.A.|N.A.|N.A.|N.A.|CA|PEND|
>| | |SPTSX|1| | |N.A.|Cuda Capital Corp| | | |.000|Y|
>|N.A.|N.A.|EQ0000000002831616|Equity|N.A.|N|N.A.|8 05208Z|CN|N| | |CV|
>|N|CA|CX|CA|CA|0|N.A.| |N|1.00|100|N.A.|N.A.|N.A.|XTSX| |.000|Common
>Stock|1000|N|N.A.|
>END-OF-DATA
>DATARECORDS=57129
>TIMEFINISHED=Tue Sep 26 17:51:45 EDT 2006
>END-OF-FILE
>
>-- file end above
>billious wrote:
Quote:
Originally Posted by
>"billious" <billious_1954@.hotmail.comwrote in message
>news:4529c1ac$0$28985$a82e2bb9@.reader.athenanews.c om...
Quote:
Originally Posted by
>
"batman" <uspensky@.gmail.comwrote in message
news:1160363613.855662.104440@.b28g2000cwb.googlegr oups.com...
>>i have a text file that is like:
>>
>date = OCT0606
>asdf
>sdaf
>asdfasdgsdgh
>asdfsdfasdg
>START-OF-DATA
>asdfasdfg
>asdfgdfgsfg
>sadfsdfgsa
>asdfgsdfg
>END-OF-DATA
>asdfgalsdkdfklmlkm
>asdfgasdfg
>>
>>
>>
>i need to clear everything from this file except the data between the
>START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
>open to suggestions of how to import using bulk insert in sql without
>changing the file at all. data is pipe seperated but obvioulsy has
>plenty of junk data in it. i have 2 similar files at about 30mb and
>60mb in size. thnks everyone
>>
>
Since you are posting from XP, do you want an XP solution?
alt.msdos.batch.nt deals with NT-series, and alt.msdos.batch with DOS and
9x.
>
Meanwhile, since pipe is a special character in DOS, can you be a little
more explicit about the contents of your file? How is
START-OF-DATA/END-OF-DATA detected (does it involve the presence of a pipe
or is it contained in one or more data-fields?) Does any line start with a
semicolon? Is there any non-alphameric content other than the pipes?
>>
>NT4/2K/XP/2K3 (NT+ systems) are discussed in alt.msdos.batch.nt as the
>techniques used differ markedly from DOS/9x methods.
>>
>For instance, given data like:
>>
>-- data begins ---
>a|b|c
>d|e|f
>0|start|1
>2|3|4
>5|6|7
>8|end|9
>g|h|i
>j|k|l
>-- data ends ---
>>
>in a file "psd.txt" then the following will produce "psdout.txt"
>>
>-- batch begins ---
>[1]@.echo off
>[2]setlocal enabledelayedexpansion
>[3]set yel=Y
>[4]for /f %%i in (psd.txt) do call :process "%%i" &if !yel!==Y echo
>%%i>>psdout.txt
>[5]goto :eof
>[6]
>[7]:process
>[8]if %yel%==L set yel=Y
>[9]if %yel%==N for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==end set
>yel=L
>[10]if %yel%==Y for /f "tokens=2delims=|" %%j in (%1) do if /i %%j==start
>set yel=N
>[11]goto :eof
>-- batch ends ---
>>
>Lines start [number] - any lines not starting [number] have been wrapped
>and should be rejoined. The [number] that starts the line should be removed
>>
>The label :eof is defined in NT+ to be end-of-file but MUST be expressed
>as :eof
>>
>Without better knowledge of your file's content, refining this is a little
>difficult.
awk "{if( $0 ~ /END-OF-DATA/) f=0; if( f ) print $0; if( $0 ~
/START-OF-DATA/) f=1}" infile outfile
All one line - awk is gawk.exe, free and open source, from
<http://gnuwin32.sourceforge.net/packages/gawk.htm>. That copies the
lines *between* the markers, but not the markers themselves. It's
easily rearranged to include them: swap the first and third
statements.
--
T.E.D. (tdavis@.gearbox.maem.umr.edu) Remove "gearbox.maem" to get real address - that one is dead|||Ted Davis <tdavis@.gearbox.maem.umr.eduwrote:
Quote:
Originally Posted by
On 8 Oct 2006 21:34:22 -0700, "batman" <uspensky@.gmail.comwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>>i have a text file that is like:
>>>>>
>>>>date = OCT0606
>>>>asdf
>>>>sdaf
>>>>asdfasdgsdgh
>>>>asdfsdfasdg
>>>>START-OF-DATA
>>>>asdfasdfg
>>>>asdfgdfgsfg
>>>>sadfsdfgsa
>>>>asdfgsdfg
>>>>END-OF-DATA
>>>>asdfgalsdkdfklmlkm
>>>>asdfgasdfg
>>>>>
>>>>i need to clear everything from this file except the data between the
>>>>START-OF-DATA and END-OF-DATA using a batcj file... elternitavly i am
Quote:
Originally Posted by
awk "{if( $0 ~ /END-OF-DATA/) f=0; if( f ) print $0; if( $0 ~
/START-OF-DATA/) f=1}" infile outfile
Also as below. The first is inclusive, the second exclusive
sed -n "/START-OF-DATA/,/END-OF-DATA/p" infile
sed "1,/START-OF-DATA/d" infile|sed "/END-OF-DATA/,$d"
All the best, Timo
--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance ; University of Vaasa
mailto:ts@.uwasa.fi <http://www.uwasa.fi/~ts/ ; FIN-65101, Finland
Useful script files and tricks ftp://garbo.uwasa.fi/pc/link/tscmd.zip|||Todd Vargo <tlvargo@.sbcglobal.netzwrote:
Quote:
Originally Posted by
In Windows 98 I would use the following (tested) batch which uses
VBScript to extract the portion indicated. The extract will be output
to a file named out.txt and opened in notepad for viewing. This batch
should work in Windows XP also (untested).
Tested ok for XP.
All the best, Timo
--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance ; University of Vaasa
mailto:ts@.uwasa.fi <http://www.uwasa.fi/~ts/ ; FIN-65101, Finland
Useful batch files and tricks ftp://garbo.uwasa.fi/pc/link/tsbat.zip|||JRS: In article <1160363613.855662.104440@.b28g2000cwb.googlegroups. com>,
dated Sun, 8 Oct 2006 20:13:33 remote, seen in news:alt.msdos.batch,
batman <uspensky@.gmail.composted :
Quote:
Originally Posted by
>
>i need to clear everything from this file except the data between the
>START-OF-DATA and END-OF-DATA using a batcj file...
MiniTrue :
mtr -n -x+ $1.txt (\A[^]*START-OF-DATA)([^]*)(END-OF-DATA[^]*) = \2
There may be a limit on the sizes of the three components of the file.
Otherwise, it's trivial in any HLL, even BASIC.
--
John Stockton, Surrey, UK. ?@.merlyn.demon.co.uk Turnpike v4.00 MIME.
Web <URL:http://www.merlyn.demon.co.uk/- FAQish topics, acronyms, & links.
I find MiniTrue useful for viewing/searching/altering files, at a DOS prompt;
free, DOS/Win/UNIX, <URL:http://www.idiotsdelight.net/minitrue/>|||"batman" <uspensky@.gmail.comwrote in message
news:1160435561.664102.10910@.e3g2000cwe.googlegrou ps.com...
Quote:
Originally Posted by
havent gotten a chance to test your replies yet guy but thank you. i
should be able to test them tomorrow. is there a way to do all this
within sql script? this will run using an automated process so i need
to eliminate the number of steps anywhere i can.
>
Sorry, I don't use SQL so I don't know.
--
Todd Vargo
(Post questions to group only. Remove "z" to email personal messages)
How to delete full text catalog in SQL server 2005
I copied a database. Then I need to delete full text catalog and
re-create one with exact same name. But it doesn't let me drop it. The
machine just hangs there forever when I try to drop it. So I read
through the usenet and found a way to delete it from
sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
apply ad hoc updates. So what can I do to delete this full text
catalog. I have also searched through all the sys.sp*, but don't see
one to delete sysfulltextcatalogs. Tried the one
sys.sp_full_text_catalog, but doesn't work.
Anyone knows a solution?
run profiler and see which command it is hanging on. Then you might want to
run it through query analyzer.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shao" <shellyshao@.gmail.com> wrote in message
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>
|||Did you try to remove the FT at service and table level? You can use the
stored procedures:
sp_fulltext_table
sp_fulltext_service
Antonio Soto
Solid Quality Learning
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"shao" <shellyshao@.gmail.com> escribi en el mensaje
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>
re-create one with exact same name. But it doesn't let me drop it. The
machine just hangs there forever when I try to drop it. So I read
through the usenet and found a way to delete it from
sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
apply ad hoc updates. So what can I do to delete this full text
catalog. I have also searched through all the sys.sp*, but don't see
one to delete sysfulltextcatalogs. Tried the one
sys.sp_full_text_catalog, but doesn't work.
Anyone knows a solution?
run profiler and see which command it is hanging on. Then you might want to
run it through query analyzer.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shao" <shellyshao@.gmail.com> wrote in message
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>
|||Did you try to remove the FT at service and table level? You can use the
stored procedures:
sp_fulltext_table
sp_fulltext_service
Antonio Soto
Solid Quality Learning
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"shao" <shellyshao@.gmail.com> escribi en el mensaje
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>
Sunday, February 19, 2012
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
Subscribe to:
Posts (Atom)