How To Delete Lines From Text File
Oct 9, 2006
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
View 15 Replies
ADVERTISEMENT
May 29, 2007
I have a flat file that look like this
############################################################
# Market Issue Lookup
# Applies to: Muni,Pfd,Govt,Corp
###########################################################
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
############################################################
# Maturity Type Lookup
and i want it to look like this
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
Basically remove any line that start with a "#" or any blank lines..
I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful
Thanks for any help in advance.
smathew
View 5 Replies
View Related
Aug 23, 2007
Hi there!
I need help on how to count the number of lines in a text file..
Pls. advise
thanks in advance
View 7 Replies
View Related
Jan 4, 2007
I am trying to bcp import a text file into a SQL Server 2000 database.The text file is coming out of a java application where orderinformation is written to the text file. Each record is on it's ownrow, so the last item in each record has a new line character at theend of it to create the next row. This works well in creating the filehowever bcp does not like to import this text file with the extra blankline at the end. If I change the new line character to the beginning ofthe records then there is a blank line at the top of the text file,which bcp also does not like. Does anyone have any suggestions for meto get around this issue?Thanks,
View 6 Replies
View Related
Aug 11, 2006
Hello,
SQL SERVER 2000:
My problem is that I have to process a special text file every day which contains 0 ASCII values to separate fields. The DTS import program drops everything after the ascii 0 value in the row, but of course I need the entire row with all fields.
So how can I prevent the text file import task from dropping everything after the 0 ascii value?
Could you help me in this?
thank you
View 8 Replies
View Related
May 3, 2007
hi...
how to delete text files from c drive (ex: c:sale300407.txt) using ms sql express edition 2005 ?
plz help me...
View 13 Replies
View Related
Aug 31, 2015
How can I run through an array of ID's and delete the fields associated to it in a single table? I have a lot of bad data that needs to be removed. BTW, I'm using the SQL Server Management Studio to do this.
I'm currently doing by hand by using the following.
USE database;
DECLARE @id integer
SET @id = XXXXX
DELETE FROM table WHERE id = @id;
I would like to use the same code but with a slight variation to support an array.
View 4 Replies
View Related
Jul 6, 2015
created a very basic flow in SSIS: extracted table data through ole db connection, added multicast and as end result i created a flat file destination (with .txt file) and a ole db destination.
My question is; how can i delete the .txt file before executing the flow again? Want to avoid that the .txt file has duplicated rows after a second execution of the flow. Is it possible to use scd component or is this way to complicated? A for each loop?
i need a similar solution for the data that will be transported through the ole db destination task....
View 2 Replies
View Related
Jan 3, 2008
Hi All SSIS experts,
Happy 2008!!!!
I am inserting data into a tab delimted text file using SSIS package.
After data insetion some extra tabs get added between columns in some rows in the text file.
Can we programmatically delete the extra tabs from the text file, if so how to use/implement the code inside the SSIS package?
Any pointer/suggestions are welcome.
Thanks & Regards,
View 9 Replies
View Related
Jan 19, 2004
I have a DTS that reads in a bunch of transactions daily to a tran history table. I read them in from a text file each day. The problem is, that about half of the lines in the text file contain semicolons because they are comments. What I do now is, import the whole thing, and then do a Delete on my tran hist table for all lines with semicolons. As my tranhist table grows, this Delete will start to take a long time. How do I filter it so it doesn't even import the lines with semicolons to start with, that will run faster and save me time later.
Thanks,
Andrew
View 4 Replies
View Related
Sep 25, 2006
I have a dtsx package that works fine with one exception. When I open the dtsx package in BI, it gives me the following message:
Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?
When I respond yes, the package opens and I can edit or execute with no problem. Still, I want to understand what could cause this message to occur and, more importantly, how I can get rid of the message. When I try to simply execute the package I still get the same error and it seems this will be a problem for trying to run the package from SQL Server agent.
It seems likely to me that this message refers to the dtsx file (in xml format) itself. Does that make any sense?
View 2 Replies
View Related
Jun 13, 2008
Hi everybody,
I'm sending text based e-mails using SMS and I need some lines to be bolded. I don't want to switch to HTML based just to make some lines bold. But, I cannot figure out how I can make the lines bold in SMS. I didn't see any options in SMS to bold a line of text. Is there any any function available for varchar datatype that will bold the text or something like that? or will I have to go to HTML based e-mail? any help is greatly appriciated.
devmetz
View 1 Replies
View Related
May 6, 2008
Hi,
I've created a dataset with 27 measures and 20 query parameters. When attempting to load the report containing this dataset I'm shown the message;
'Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file.'
If I do open the file it does indeed respond very slowly or even hangs.
I can manually format the XML code but amending the code in any way (i.e. using the layout designer to move a chart) removes my formatting and re-introduces the problem.
Are these an unreasonable amount of measures / parameters?
Environment;
VS2005 v8.0.507
MSSQL 2005 9.00.1399.06 Build 3790 SP2
Windows Server 2003 SP2
Many thanks.
View 1 Replies
View Related
Oct 5, 2006
Hi There,
I am parsing a directory of flat files and looping through it with a foreach loop. Some of the files have lines that contain characters that I would like to remove. In fact, it would be good if I could remove the entire line. Is there a way to do this with a Script Task or some other way.
Thanks for your help.
View 3 Replies
View Related
Jun 5, 2007
Hi,
I have a very simple question, how do i read the first couple of lines only from a flat file source. Let me illustrate with an example:
**Source file***
Date of refresh 04/05/06 **
abc, 123
bac, 156
I need a way to read the first line 'Date of refresh 04/05/06 **' and get the date '04/05/06' ; and store the date in a variable.
Right now, I have a flat file source and a script component, which receives a connection from the flat file source, The script component is reading the lines, but thrice instead of once. It would be great if I can program the script component to terminate after reading the first line only. Any help would be appreciated very much.
Thanks
Bidyut
View 5 Replies
View Related
May 13, 2002
Is this possible? I would think it would be, but not sure of the commands and I can't find them in my books.
I'm looking for something like.
EXEC usp_studentassign SPOOL C:
esults.txt
Is there such a beast?
Thanks : )
View 1 Replies
View Related
Feb 21, 2007
I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.
10001,2005/01/01,some more data
SOME PRODUCT 1, 10
SOME PRODUCT 2, 5
Can somebody place give me some guidance on how to do this in SSIS?
View 2 Replies
View Related
Mar 27, 2008
I have used the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 9 datasources, ordering each on a field commmon to all.
I have created the required derived columns headers and have merged all the record types into a file.
The resulting file looks fine, except for the odd blank line between record types. Any ideas regarding cause and what to do to fix?
Any help is most appreciated!
View 10 Replies
View Related
Jul 10, 2015
I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.
I am attaching a sample spreadsheet with two tabs - current structure and desired structure.
Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.
The number of lines is variable per patient.
View 6 Replies
View Related
Mar 20, 2008
Hi,
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
View 6 Replies
View Related
Apr 27, 2015
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies
View Related
Nov 9, 2015
I have created a File System task which is contained in a Foreach Loop Container. I have .bak files that are populating a directory from a maintenance backup plan.
There is a point where I need to delete the .bak file's after I've zipped them all up.
How do I set the SourceVariable to read through the directory and pick up just the .bak file's in the directory to delete.
View 3 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Mar 6, 2008
During a server upgrade somehow the full-text catalog for a databasewas lost. Now when I go into Enterprise manager I can't remove the oldcatalog or create a new one!How do I get rid of the current broken catalog and start a fresh?
View 3 Replies
View Related
Jan 2, 2008
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
View 1 Replies
View Related
Jan 31, 2008
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
View 7 Replies
View Related
May 13, 2008
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
View 10 Replies
View Related
Oct 1, 2006
Hi,
I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.
View 15 Replies
View Related
Feb 22, 2001
Hi,
I have by mistake created a file by name abc.ndf on Primary filegroup on a database.
Now when I delete it it says can't delete because file is not empty.
I want to get rid of this file.
Any suggestion.
View 2 Replies
View Related
Dec 9, 2004
Our server is getting pretty full, the data has already been backed up, but it did not create additional room on the server. Can I delete the log file as long as I have backed up the items I need? We are running Microsoft SQL 2000.
Thanks for the help!
View 4 Replies
View Related
Dec 7, 2001
Can anyone point me to an example or tell me. How to run a job that delete's a file from a directory on a server? A have a ftp process that send's a larger flat file to my sql server every hour, which after about 4hr will full my server. I need to delete this file from its d drive location.
Thanks Reggie
View 4 Replies
View Related
Feb 9, 2005
Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
Nina
View 2 Replies
View Related
Nov 15, 2005
Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.
View 1 Replies
View Related
