.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
satyapriyanayak
Post New Web Links

Ignore trailer record in delimited file

Posted By:      Posted Date: September 17, 2010    Points: 0   Category :Sql Server
 
SSIS 2008 I receive flat data files from a vendor that are pipe delimited however there is a header row and a trailer row that is not pipe delimited.  The first row is easily bypassed however SSIS rightfully complains about the last row;  “There is a partial row at the end of the file”  Short of having a separate process to strip the trailer record is there a way for SSIS to ignore this row?  Currently I am using a simple data flow with a flat file source and a OLE DB destination (database) with the usual column mapping. I can ascertain the data rows from the trailer row by the first character.  Data rows start with ‘D’ and the trailer row starts with ‘T’.     Thanks for any help! RJ  


View Complete Post


More Related Resource Links

Does this look correct for saving a file/ new record to a database

  

So thank you for all your help so far, So I think i got this right,
but would like for you all to look it over to see if there is any
areas I might run into trouble with:

//using asp.net mvc 2
the table (sql server 2005, using entities framework)
"Pattern_Media"
table fields
"Pattern_Media_GUID  uniqueid 
"Pattern_GUID  uniqueid
"Media varbinary(max)  
"height int
"Width int
"Media_Type String (50)


the form:
<h2> Add Media to this pattern</h2>
<% Html.EnableClientValidation(); %>
<% Html.BeginForm("AddMedia", "Pattern", new { id= Model.Pattern_Guid} , FormMethod.Post, new {enctype = "multipart/form-data"}); %>
   
    <%: Html.Label("height") %>
    <%: Html.TextBox("height") %>

    <%: Html.Label("width") %>
    <%: Html.TextBox("width") %>

    <%: Html.Label("Media") %>
    <input type="file" id="Media" name="Media" />
   
    <input type="submit" name=

How to save form data to delimited file?

  
I tried searching, but maybe I was putting in bad keywords =\How do I go about saving form data into a delimited file?

how do i create a schema.ini file from a tab delimited file?

  
Hi there,How do I create a schema.ini file from a tab delimited file?I have this so far:[myFile.txt]ColNameHeader=TrueFormat=TabDelimitedCharacterSet=ANSII think I need something like this after my last line from above:Col1=first TEXT width 150My text file has 7 columns, the first row is the header row. The data comes from a Micosoft SQL Server Database where the fields are of the following types:intnvarchar(MAX)nvarchar(50)Many many thanks,Kiley

Reading each record from a text file and sending email

  
Hi All, I have a text file (CSV) which has only three fields (Name, Email, SendYN). I want to read each record from this text file and send email to those records where SendYN field has Y. I had created Flat File connection and a Flat File source in a data flow which uses Flat file connection. Now I stuck how to move further. Which control I need to add and where to identify whether SenderYN = Y and how to send email? I am new to SSIS. Thanks.

parsing tab delimited spanish characters file

  

Using vb.net/asp.net 2005.

another spanish character issue:  I am parsing special spanish characters, removing the tildes (EX:  "ñ" becomes "n") that customers send us in files, in tab delimited files that is.

When the characters are valid spanish characters (like "ñ") then this is not an issue, however some of the files that we get have characters such as:

"Ni±o"

Which should actually be:

"Niño"

Has anyone seen this and know any solution?  The "ñ" is fine but I am not certain what to do about the "±".

this is only one example, other spanish characters are listed as a question mark "?" so this is causing some issues.


thanks

MC



Create new record using external CSV file

  

Hello,

I have a CSV file that contains e-mail addresses. I would like to use that file to create new records for customers whose e-mail addresses exist in the CSV file.

The new record for all customers will be identical, except for the counter which is a unique key:

Here is the structure of the table:

CREATE TABLE [dbo].[Letter_C](
	[Counter] [int] IDENTITY(1,1) NOT NULL,
	[DateCreated] [datetime] NULL,
	[DateModified] [datetime] NULL,
	[PrimaryCode_C] [nvarchar](30) NOT NULL,
	[UserCreated] [nvarchar](30) NULL,
	[UserModified] [nvarchar](30) NULL,
	[ContactCode_C] [nvarchar](50) NULL,
	[Description_C] [nvarchar](250) NULL,
	[LetterName_C] [nvarchar](250) NULL,
	[LetterPath_C] [nvarchar](250) NULL,
	[LetterCode_C] [nvarchar](50) NULL,
 CONSTRAINT

Non Delimited Header in a delimited output file

  

 

I have a requirement to make a delimited flat file from a data base table.  The table has some 20+ columns.   I totally understand how to accomplish this part.  What is a bit more  tricky is that I need to create a header row that is not delimited.  The final output would look something like this

 

$header –Opt1 –Opt2 20101005

DBCol1|dbCol2|DbCol3

SSIS - load table from source text file with multiple record lengths

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Load multiple record length text file into table using SSIS

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

How can I export my reports to tab delimited csv file?

  

How can I export my reports to tab delimited csv file?

thanks,

Gok

 


Xml File did not take new record?

  

Hi,

I have Registor page.When i user registor the data store in xml file like this.

 XmlDocument xdoc = new XmlDocument();
                XmlElement ClientElement = xdoc.CreateElement("Client");
                xdoc.AppendChild(ClientElement);
                XmlElement UserElement = xdoc.CreateElement("User");
                UserElement.SetAttribute("UserName", txtUserName.Text);
                UserElement.SetAttribute("Password",txtPassword.Text);
                UserElement.SetAttribute("VarifyPassword",txtVarify.Text);
                UserElement.SetAttribute("FirstName",txtFname.Text);
                UserElement.SetAttribute("LastName",txtlname.Text);
                UserElement.SetAttribute("Address", txtAddress.Text);
                UserElement.SetAttribute("Country",cmbCountry.SelectedItem.Text);
                UserElement.SetAttribute("Email",txtEmail.Text);
                ClientElement.AppendChild(UserElement);
                xdoc.Save(Server.MapPath(@"~\UserInfo.xml"));


Ex:-


<Client>
  <User UserName="krishna" Password="123" VarifyPassword="123" FirstName="Rakesh" LastName="Indira" Address="Vijayawada

Reading Tab delimited xls file

  

Hello,


I have tab delimited .xls file. I want read this to a dataset. Oledb connection string is given below. But i'm getting one error on objConn.Open();


string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0";

OleDbConnection objConn = new OleDbConnection(strConn);
 objConn.Open();

Error : External table is not in the expected format.


How can i read tab delimited .xls file to a dataset using oledb connection?


Thanks,

Misha


Convert comma delimited record to string array

  

Is there any efficient way to convert a comma delimited record with quotes to string array?

Say, "Peter","Pan",123,"Hello, is it ok",Test
str[0] = "Peter"
str[1] = "Pan"
str[2] = 123
str[3] = "Hello, is it ok"
str[4] = "Test"

That is comma within quotes are not treated as delimited

Thanks


export data to pipe delimited file

  

Hi,

I am trying to export the sql script data to pipe delimited text file.

I did below steps

1. JOined the sql tables and wrote the sql script.
2. export the results to glbal temp table.
3. then BCP the results to pipe delimited text file.


here is the select query

Select dbo.FN_Decrypt(@master, Login.LoginSkey,PatientDisplayID) as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',
    ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count'
   
    INTO ##PIPE_REPORT
FROM #temp_patients inner join Login ON  Login.LoginID = #temp_patients.PatientID

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T '

DROP TABLE #temp_patients


I have successfully exported data into pipe delimited text file. but only data not the columns names

I need columns names inthe first row, please let me know how can i do this?

and also I need special characters enclosed in quotes. And how can I describe each column data type in text file? I will be having alphabets and numerics in text file.

Thank you


Kishore

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend