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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

StreamReader issue with flat file

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :ASP.Net


Please observe this flat file image: http://screencast.com/t/YMpeI5pOlI

You will on the bottom of the text file, line numbers shown by MSVS 2005 that tell me that a line exists, yet with no TEXT along side it, the text file has too many carriage returns at the bottom. Lets call these BLANK ROWS of data in my text file.

The issue is when I import this data like (vbNET):

Dim oRead As StreamReader
Dim str As String
Dim rows(0) As String

oRead = New StreamReader(FullPath)
str = oRead.ReadToEnd()
str = str.Replace(vbCr, "")
rows = str.Split(ControlChars.Lf)

The rows array has entries within it for the BLANK lines I show in the image attached. And this mucks up my indexes when I manipulate that rows data.

Can I do a 'Replace' or something to clear these BLANK lines before they get into my rows array ??

At the moment I just clean up the text file before import, but I need a coding solution to make my life easier !

Any ideas ! What do you do ?

View Complete Post

More Related Resource Links

Issue: Load {NUL} from flat file to "Flat File Source" faild.

Hi guys, I've met a strange issue when I was working to use SSIS to load data from flat file to database. It is a story about {NUL}. Sample Flat File: FIELD_SAMPLE|OTHERS ABC{NUL}DEFG|Any Others   I placed an Flat File Source to load these into package, and the configuration of the Connection Manager are as following: FIELD_SAMPLE: Unicode string [DT_WSTR], Length 8   Then I click the "Preview" button and I can see this data in the "Preview" window correctly (with the value "ABC{NUL}DEFG"). But when I tried to run this package, I've got an error that the "ABC{NUL}DEFG" is too long for this field. Then I changed the length to 20 and it was loaded into SSIS package. But I found the value in SSIS package is "ABC", and "{NUL}DEFG" was not there~ I have no ideal why it is ok in Preview but it is not ok when the package running~     Anyway, I hope to load the {NUL} from the flat file into target database. So that is there anybody could give me a hand?

Flat File Loading Problem -> Messed Up Delimiters and Error Logging Issue

Hey Everyone,

I have a Data-Flow task embedded in a Sequence Container (does not fail component on error) on the Control Flow panel of the SSIS designer.  This data flow task contains a connection to a Flat File Source -> A Data Transformation -> Into an OLE Db Destination.

The problem is that the Flat File isn't always delimited properly -> the client cannot be relied on to do this.

My question is when the delimiters are messed up, how can I capture the offending error row(s) from the Flat File Source?

What I've tried:
1) Set every column in the source flat file on error to: Redirect Row
2) Added a Script Transformation to pull the description and the record id out of the offending row
3) Added an Error file flat file destination to the end of the flow.

The package always fails on the Flat File Source and never Redirects the offending Row to the error output - I never see my onError Script Transformation go Green, Red, or Yellow - SSIS doesn't let it get there.

I'm really new to SSIS so sorry if this is a super basic question.

Here is the Error Text:

[Source - InventTable_csv [1]] Error: The column delimiter for column "RECID" was not found.
[Source - InventTable_csv [1]] Error: An error occurred while processing file "C:\------InventTable.csv" on data row 15228.

User validation Issue in xlsx file



I am validating user from Login.xlsx file. It was working fine by validating email and password from that file. For testing i got new file same contents of file but added some more user names.

So after that when i validating the user i am getting the following error "Data type mismatch in criteria expression".

But when i replaced the new to old one it is working fine. The contens of both files are the same. So why this kind of error happened.


Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

Inserting a row on an extract flat file

Hi, I want to add in a header row to an extracted file. The file contains 5 columns of data but I only want to add in a 5chr id (stored in a variable) in the very first line of the extracted flat file.. please can you assit and tell me the best way of doing this..

"Mixed mode assembly is built against version 'v2.0.50727'.... " issue and use custom config file

Hi, My Winform applilcation has used Microsoft.SqlServer.Management.Common namespace(assembly :Microsoft.Sqlserver.smo.dll/Microsoft.Sqlserver.ConnectionInfo.dll) and developed in .NET 4.0. Problem:Application is throw an exception when it tries to use above assembly reference methods. Full error text is shown below, "Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information. " Error is resolved when we include "app.config" file with below code, <?xml version="1.0"?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> </startup> </configuration> But, I need to keep configuration file as "renamed.config" and not to include app.config file as of the project requirement.I have set configuration file as "renamed.config" file using below code, //AppDomain.CurrentDomain.SetData("APP_CONFIG_FILE", "d:\\reanmed.config" ); I have also included <Startup> element in this "renamed.config" configuration file and removed the app.config file.But it raise me below error, it always expect app.config file with <Startup> element to resolve this iss

export to Txt file - Null value issue

My query: SELECT   PRIMSPEC, MI, Case   when PrimSpec='040' then MI else null end as MI2 FROM   dbo.PROVIDER WHERE   PrimSpec in ('040','041') My results when PrimSpec in 041: 041 W NULL When the results are saved to a tab delimited file the "Null" is exported as the text "NULL".  I need these values export as null (nothing), not the word null. Any ideas?

AjaxContorlToolkit - issue: Could not load file or assembly

Hi everyone. Can somebody please tell me how to solve this problem with my ajax controls ?I've tried with copy the .dll's files in the project, and seems they are working fine in the visual studio with drag&drop, but when I'm trying to start the project i got this error. Thanks a lot.--------------------------------------------------- Server Error in '/' Application. Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.FileLoadException: Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Assembly Load Trace: The following information can

Convert Data from TXT file and face a length issue! Need help!

Hi All, I am trying to convert some TXT files in to sqlserver database by using SSIS.  The problem I am facing right now is that some TXT files I have are fixed with line length 286.  However, for the some other TXTs they have 296 characters per line. The reason why this happened is some one added one more field before generating those TXTs.  Instead of creating two SSIS packages for converting those TXTs, can I find another way to get around this?   

Export db to flat file

I'm trying to export data from a database to a flat file using the tool import and export of SQL Server 2008, I is generating an error that says: Error 1 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Tienda_Nombre" (201) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 2 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Proveedor_Nombre" (203) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 3 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Depto_Nombre" (205) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 4 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Subdepto_Nombre (207) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Validation Error 5 error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Clase_Nombre (209) is 1252 and is required to be 20 127. I saved the package and opened it, I saw that in the metadata columns that I got error I get the value of 1252, flat file connection says that the code page is 20127. What should I do to correct these errors?

How do you specify special characters as flat file delimiters?

I have a flat file that has fields delimited by tilde {~} and rows delimited by {CR}{LF}.  The problem is, my source is spitting out the {NULL} (&H00) at the end of the row. The data displays correctly in the pre-viewer (with a non-print symbol at the end of the last field), but gives an error when I try to run the package. Example: "data"~"data"~"data"{NULL}{CR}{LF} I can process the row in c# using: "\0\r\n" as the row delimiter.   What is the equivalent notation for SSIS?   Thanks,

Load and parse a file where the file path is located in a column of a flat file source

Hi, I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly.  Thats no problem.  The problem is that one of the columns specifies a filepath to an XML file that is supposed to contain data to be populated in a table with an FK constraint.  How do I specify the file name to the XML source component at runtime? For example TableA has columns: TableAID GroupName TableB has columns TableBID TableAID (FK to Table A) MemberID (the data in question) TableA has a 1 to many relationship with Table B. The CSV has a bunch of columns including: TableAID File path to list of members for each group. I don't know how to solve this problem.  I tried using a ForEach ... but I couldn't get it to work. Thanks in advance for any assistance.

Flat file upload: Error when inserting into datetime field.

Hi All I am having an issue with uploading a txt file into an SQL table and one of the fields needs to go into a datetime column. The issue is with column3 I have been into the advanced editor of the flat file source and set the output column to decimal and it fails. The error I am getting is below: Error: 0xC02020A1 at Materials Transfer, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Materials Transfer, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Column 3" (48)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 3" (48)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. Error: 0xC0202092 at Materials Transfer, Flat File Source [1]: An error occurred while processing file "D:\Development\r3_downloads\mat.txt" on data row 1. Any help would be great.   Thanks BigGopher

Adaptive Flat File Destination Columns

I'm sure it must possible to adjust the columns for a flat file output based on the input, but so far have not found the right tools. I have to convert quite a few tables to flat files, where the input columns have not been safely specified yet. The tables are still being changed.   e.g. I have a table with columns A,B,C. Columns D,E,F... could be added later, maybe B or C will even be removed or renamed. I have to transfer all data from the table except for column A to a flat file.   I find it annoying having to adjust the columns manually every time. Would be extreemly thankful for your advice.

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

\ in flat file forcing truncation

I am using a Flat File Source to import a csv file. The connection manager uses Delimted, Text Qualifier None. I am importing a row at a time then breaking it up. It is not importing a row because of the "/" char. Any way to fix? Thx in advance.   "September 11, 2010","article","51811 - government\ not loaded",1 "September 11, 2010","article","51811 - government gets loaded fine",1  

LINQ to SQL Compact db, file copy issue

Our application uses LINQ and SQL compact database .sdf file as datasource.  It is using Miscrosoft SQL Server Compact 3.5 SP 1 and .NET Framework 3.5 SP1. We store our application's project data into a .sdf file.  When our application opens a .sdf file (e.g. projectA.sdf), it will make a temporary copy of it and open the temporary file instead of the original file.  If we change one table cell value (i.e. one property value of an object that is created by LINQ), and call DataContext.SubmitChanges(ConflictMode.ContinueOnConflict), and then call File.Copy(DataContext.Connection.Database, filename, true); to copy the temporary file back to the original file (replacing the original one).   We found that the table value is not changed in projectA.sdf.  But in the temporary copy, the value is changed.  If we re-open db connection by calling DataContext.Connection.Close(); DataContext.Connection.Open(); right before calling File.Copy(), the changed value is copied successfully to projectA.sdf. If we do a lot of value changes in the database, or adding / deleting table row, then submit changes.  Without re-opening the db connection, after File.Copy(), the projectA.sdf have those changes copied. This strange behaviour looks like the temporary db file is cached.  For a small change, the cached version is not changed.  The File.Copy() use t
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