How to integrate flat file with special characted contain

jn93 651 Reputation points
2023-06-04T04:03:11.3333333+00:00

Hi I have a csv flat file as per attached. I need to integrate the data from this flat file ODS destination SQL server table. I get error VinNo column delimiter not found when I run the SSIS. I understand because of the special char presence in the column VinNo. How can I resolve this error? Like SSIS derived column able to help?

VehicleKey,RegNo,VinNo "1","ABC",""PN153SYF103109526 ""

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2023-06-04T16:16:00.95+00:00

    Hi,

    VinNo column delimiter not found... I understand because of the special char presence in the column VinNo.

    This is a common issue which can be related to one problematic character in the CSV.

    It will help if you can provide a simple CSV file which include 2-3 rows (fake data can fit) which on of these raise the issue. Let us see the type of the format which is not working. In addition please provide the DDL of the table (query to create the same table so we will have the exact structure of the table).

    VehicleKey,RegNo,VinNo "1","ABC",""PN153SYF103109526 ""

    Is this an exact line from your CSV?

    Do you have some strings values like VehicleKey whcih is not enclosed in quotation marks, while other text like ""PN153SYF103109526 "" surrounded by double quotes.

    Or maybe you meant to publish VehicleKey,RegNo,VinNo as the header line (columns names) and in a seperate line you have the content "1","ABC",""PN153SYF103109526 ""

    In this case, why it is stil not clear to me why the last value ""PN153SYF103109526 "" surrounded by double quotes. Is this how you get the source file?

    As I mentioned a bit more information and sample can help.

    In the meantime, here are common cases which raise this error, you can check

    • (probably the most common) a delimiter use {LF} instead of {CR}{LF} or vice versa. Make sure that your file actually uses the same line break that your SSIS set to.
    • If you have a quotation marks in expression (part of the text value), then you need to escape the inner quote with a \
    • ...
  2. ZoeHui-MSFT 32,736 Reputation points
    2023-06-05T03:25:09.35+00:00

    Hi @jn93，

    I used the attachment and the setting is like shown below without issue.

    You may have a double check.

    User's image

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

  3. Olaf Helper 40,741 Reputation points
    2023-06-05T05:50:29.23+00:00

    ,VinNo "1",

    In a CSV the double quote " is use as text qualifier for the case, then text contain the column seperator, here the comma ,.

    But the text qualifier have to enclose the complete text, not only a part of it.

    **""**PN153SYF103109526 ""

    And it enclose it once, not twice.

    You simply have trash data.

    0 comments