Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Splitting single comma separated line Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 3:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
Hi All,

My source file is coma separated file having 6 columns. But some times two lines are merging i.e. eow containas 12 columns instead of 6 and my package is failing.


Kindly guide me how can i handle this issue?


Thanks
Abhas.
Post #1541518
Posted Friday, February 14, 2014 5:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 4,828, Visits: 11,179
abhas (2/14/2014)
Hi All,

My source file is coma separated file having 6 columns. But some times two lines are merging i.e. eow containas 12 columns instead of 6 and my package is failing.


Kindly guide me how can i handle this issue?


Thanks
Abhas.


Should not be your problem.

Speak to the people responsible for producing the source file and ask them to fix up the bug which is causing the issue.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1541551
Posted Friday, February 14, 2014 5:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 468, Visits: 817
1st of all i totally agreed with Phil.

but most of the time ... they say ... there is no issue in it :P

here are couple of things you can try thou:

1. Check that csv if its ending line 'CR' or 'LF'. (you can check it NotePad++)
because, there might be a case try to adjust your row identifier, you can also check this file while to import its using MS Access as well. check if the same issue happens or not.
if you are using flat file connection.

2. you can parse the file in script manager, in that you can avoid it when you split the string and you can also log these error lines in a file or any tables as well.




Post #1541555
Posted Friday, February 14, 2014 6:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 4,828, Visits: 11,179
twin.devil (2/14/2014)


2. you can parse the file in script manager, ...



What do you mean by this?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1541562
Posted Friday, February 14, 2014 6:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 468, Visits: 817
Phil Parkin (2/14/2014)
twin.devil (2/14/2014)


2. you can parse the file in script manager, ...



What do you mean by this?


Sorry, i meant to say is "Script Component" instead of "script manager".
Post #1541567
Posted Friday, February 14, 2014 6:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 4,828, Visits: 11,179
twin.devil (2/14/2014)
Phil Parkin (2/14/2014)
twin.devil (2/14/2014)


2. you can parse the file in script manager, ...



What do you mean by this?


Sorry, i meant to say is "Script Component" instead of "script manager".


That was my guess, but I wanted to make sure that I hadn't missed something. 'Script Manager' sounds like a cool tool



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1541573
Posted Friday, February 14, 2014 6:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 6,795, Visits: 6,268
As already stated fix the problem at source.

Probable cause of 'joining' lines is missing CR char ie LF instead of CRLF
If this is the case delimit on LF and remove CR from 6th column if present



Far away is close at hand in the images of elsewhere.

Anon.

Post #1541578
Posted Friday, February 14, 2014 2:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 07, 2014 2:09 PM
Points: 79, Visits: 242
Correcting at the source is most logical thing.

But, on the other hand you can validate your flat file before loading data into database. You load the good data into table. You can send the invalid data for correction or may be correct it using a utility. Here is something you can try (if you want)

SSIS: How to validate Flat File Row Length?


Vikash Kumar Singh || www.singhvikash.in
Post #1541778
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse