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 1234»»»

import flat file through SSIS Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2007 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 12:27 PM
Points: 6, Visits: 16
I have a SSIS package which imports a flat file to DB. The file delimiter is Comma, the text qualifier is set to " (double quote). It was all fine until it hits this column: "toys ""r"" us, inc." supposedly the comma is inside of the text qualifier, but I guess the double double quotes around the R confused the system, so the field was splited into two. And this causes problem. I was wondering if anybody have any idea how to handle this (modify the original import file is not an option.)
Thanks!
Post #436342
Posted Wednesday, December 26, 2007 8:24 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I tried with a file having the folllowing content.

"Jacob","jacob"s" File", "Other File"

I get this file processed correctly (Jacob, Jacob"s" File, Other File) with the following settings (SSIS Flat File Connection manager)

format: delimited
Text Qualifier: "
Column delimiter: comma {,}
Row delimiter: {CR}{LF}


.
Post #436348
Posted Wednesday, December 26, 2007 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 12:27 PM
Points: 6, Visits: 16
Thanks for your quick response. I guess I didn't state the problem clearly. In my case, I don't want the data imported as two seperated fields: "toys ""r"" us" and "inc". the whole thing should be one field (column).
Post #436352
Posted Wednesday, December 26, 2007 8:36 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
ah! my mistake.

.
Post #436353
Posted Thursday, December 27, 2007 8:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 9:28 AM
Points: 660, Visits: 323
I don't have a solution, but shouldn't it be "toys 'r us" with a single quote before the r? Maybe the data can be corrected before you receive it?
Post #436684
Posted Thursday, December 27, 2007 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2008 1:38 PM
Points: 32, Visits: 69
...or find any instances of double qoutes in your text file and replace with single qoutes before executing the import.

Does anyone ever get really 'Clean' data?

'nix
Post #436701
Posted Thursday, December 27, 2007 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 12:27 PM
Points: 6, Visits: 16
Thanks for all the inputs! Yea, I am working on reformatting the data. I was hoping that I don't have to do that...
Post #436718
Posted Thursday, December 27, 2007 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
i do monthly imports of taxation data from CCH and have had a similar problem

only solution i ever found was to map out the column lengths


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #436758
Posted Thursday, December 27, 2007 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 12:27 PM
Points: 6, Visits: 16
By "map out the column lengths" do you mean make it a fixed length file?
Post #436776
Posted Thursday, December 27, 2007 11:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
If you setup a BCP format file correctly and use Bulk Insert, that problem will magically disappear... performance problems will probably disappear, as well. I've used Bulk Insert with a format file to import more than 5 million rows per minute on a 20 column quoted identifier CSV.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #436778
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse