.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

Unable to load data from txt file with text delimiter specified, if the data has the same character

Posted By:      Posted Date: September 26, 2010    Points: 0   Category :Sql Server

Have come across a strange scenario and seeking your ideas/thoughts.

I'm using a ssis package to load a txt file. The txt file is text delimited by double quote (") and column delimited by tilde (~). It works fine in most scenarios and strips the enclosing "" from the columns -no problems there. However, in a specific scenario where the last column has double quote in the data as well being the text delimiter, the package throws the following exception indicating that it's missing a column delimiter

 The column delimiter for column "COL_NAME" was not found

This behaviour was not observed when double quotes were present as data in any other column in the file. Was able to workaround by disabled text delimiter only for the last column, but that isn't a perm solution. Also observed that if the " was replaced by escape sequence /" the file loaded successfully. Wondering if this a known issue ?


View Complete Post

More Related Resource Links

Could not load file or assembly "System.Data.DLL" or one of its dependencies

Could not load file or assembly "System.Data.DLL" or one of its dependencies

Data loading from text file

Hi, I am loading the data from a flat file into a sql table. The values in one of the column have spaces at the end of the value. I want to remove those spaces and load into the table. I am thinking of using derived column and use rtrim on that column, how can I use that as an expression. Thanks.sqldev

Unable to load data into a LookUp column in sharepoint using sharepoint destination

I have loaded details of a customer on a list and was able to load the data with out any problem using sharepoint list destination. We have a lookup column called customerid looking at customer list  . This lookup column is in another list, called Customer Main, so I am trying to load additional data for customer in this customer Main  list which contains a lookup column called customerID. I was not able to load data into this cutsomerid, whereas rest of the data is coming into sharepoint with out any problem. How should I load data into a lookup column. Thanks  

Unable to export data to an excel file in grid format!!!

I'm exporting data to an excel sheet from the ojects list. The data is exporting to excel sheet well, but I'm unable to see the grid format for the data. The data is looking with out the rows and columns lines. I think I'm missing to set some property or somethnig ehwn exporting.   Any help would be really appriciated.

saving a text file in ms access data base

i have to read a text file in a text box then have to save it into ms access data base in column format how could i do this....

system.io.fileloadexception - could not load file or assembly system.data.sqlserverce version=3.5.1.



my application raises this exception but the file system.data.sqlserverce.dll is in the folder of the application, and the version is correct.


do u have any idea about this problem ?


thanks in advance for every answer Smile


hello from switzerland !



Some problem in Data Flow while upload a "||" seperated text file


I am facing one problem with sql 2005 SSIS while uploading a "||" seperated text file with Data Flow in SSIS.Below I am giving the sample data of the text file



When I uploaded it using sql 2000 DTS it was not giving and it was being uploaded into the table but when I tried with SQL 2005 it is giving the below problem.

"[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "



how to load data in file XML to richtextbox


 am developing a windows application, in which I am giving two options to the user for the file selection.

1. Open File Dialog

2.select  file 

3.load data in file to richtextbox


load data in file to textbox

private void button1_Click(object sender, EventArgs e)
            OpenFileDialog fs = new OpenFileDialog();
            fs.InitialDirectory = "x:\\";
            fs.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
            fs.CheckFileExists = true;
            fs.RestoreDirectory = true;
            fs.InitialDirectory = Application.StartupPath;

            if (fs.ShowDialog() == DialogResult.OK)
                rtxtbox.LoadFile(fs.FileName);    //i have a problem in this here

Delimiter File Read Task Installer failed to show in SSIS Data Flow Items


I downloaded the Delimiter File Read and followed the instruction and installed it. The file is on the correct directory (>:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents) The file DelimitedFileReader.dll is there.

However, when I go to BI Development Studio and try to add the new task I can't see it in the SSIS Data Flow Items...it is not visible. Does anyone know why I can add/see it?



How to output data from master detail tables into text file but on different lines.



I have a requirement to output data from two tables, a master table and a detail table, to a text file such that the output looks like the following:

master record 1

detail record  1 1

detail record   1 2

master record 2

detail record  2 1

master record 3

detail record 3 1

detail record 3 2

detail record 3 3



Is this possible in one query?  I am sufficiently familiar with the bcp utility to do the output from any of the tables separately but have some difficulty with doing it for the two tables and have the result structured as outlined above.

how to load data with the file name?


We are loading data from xls/txt/csv files into our existing Db tables, but with this we want to also store file name into our table column, how to achieve it?


How to connect text file data to native SharePoint lists with auto update?


I have to pull external data from a text file to a SharePoint list on a daily base. Additional column content (e.g. attachments) in SharePoint should be kept during update. There should be RSS feeds, email notifications or workflows on external data change (so delete all items and import new will not work).

I've found this codeless 3rd party solution with automatic background update (and write-back???):

Any other idea? BDC-based? Thanks.

Thanks, SharePointFrank http://www.layer2.de/en/products/

Load XML data (string format - not file) into a gridview




 I need some help help loading some XML tagged data I received as a reply from a Web Service (String format), into individual fields on the web form.


After spending some time,  I was able to load an XML file into a gridview using this code...

<?xml version="1.0" encoding="ISO-8859-1"?>
        <title>Empire Burlesque</title>
        <artist>Bob Dylan</artist>
        <title>Hide your heart</title>
        <artist>Bonnie Tyler</artist>
        <company>CBS Records</company>

Upload Text File data to SQL table Using SSIS

I have a text File with following format  (  HED - Header Row, CL-Data (Sample 3 Rows) , TRL-Trailer Row)


CL 012352908607309000014090 2010-03-04 16:12:53.077000000 Inserted / Log ICode Action on Order Number / 33949591 / with / I-Code / IDEC/ / Memo: / cci questions about alert/

CL 012352958107309000020378 2010-04-14 10:20:03.340000000 Inserted / Log ICode Action on Order Number / 36992594 / with / I-Code / ISRB,INEW,ILKD/ / Memo: / cci to cxl, saved with benefits and gave her instructions to move account online/

CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Inserted / Log Customer Info Change Action CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Updated / Log Customer Info Change Action

TRL201008240001PCMCL 0000007349

I have to Import The above Mentioned data in to Following Table

CREATE TABLE [dbo].[Log]

[Type] [varchar

How do I create an XML file from a text file containing simple ascii data? Put another way, how do I


Im trying to take a text file I downloaded from the US Census Bureau website and transform it into an XML file. It is what the Census Bureau calls a ZCTA file. The schema of the file I have reposted as follows:

ZCTA File Record Layout

The ZCTA file contains data for all 5 digit ZCTAs in the 50 states, District of Columbia and Puerto Rico as of Census 2000. The file is plain ASCII text, one line per record.

  • Columns 1-2: United States Postal Service State Abbreviation
  • Columns 3-66: Name (e.g. 35004 5-Digit ZCTA - there are no post office names)
  • Columns 67-75: Total Population (2000)
  • Columns 76-84: Total Housing Units (2000)
  • Columns 85-98: Land Area (square meters) - Created for statistical purposes only.
  • Columns 99-112: Water Area (square meters) - Created for statistical purposes only.
  • Columns 113-124: Land Area (square miles) - Created for statistical purposes only.
  • Columns 125-136: Water Area (square miles) - Created for statistical purposes only.
  • Columns 137-146: Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively

Columns 147-157: Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively

All I want to do is produce an XML file with t

Could Not Load File or assembly 'MySql.data'



I am trying to test website on a remote server to see if I can read or write to a MySql Database.

In VS Web developer 2010 Express edition, I created a new website and saved it via ftp on the remote host. I have added the 'Mysql.data.dll' to the website and placed it in the bin directory of the project.

When I try to run the program i get "Could not load File or assembly MySql.Data, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified.

Line 4:  		<compilation debug="true" urlLinePragmas="true">
Line 5:        <assemblies>
Line 6:          <add assembly="MySql.Data, Version=, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>
Line 7:        </assemblies>
Line 8:      </compilation></system.web></configuration>

The remote server supports .Net v2, so I have made my application focused on v2 framework.  I have tried similar code on my local machine and works fine.

Can anyone pls advice what I might be doing wrong?



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