CSV Import Problem

Question

Post reply

CSV Import Problem

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

and here's the set based solution (I knew there would be at least one...)

CREATE TABLE #tempWeirdExcelList

( id INT IDENTITY(1,1),

list VARCHAR(50)

)

INSERT INTO #tempWeirdExcelList(list)

SELECT ' 1400.00,"" ,17.50, 3,0,0 ,",,,,,"' UNION ALL

SELECT '"1,400.00","",17.50,300' UNION ALL

SELECT '"1,400.00",17.50,300' UNION ALL

SELECT '"1,400.00",",,,,",17.50,300' UNION ALL

SELECT '"1400.00","17,.5,0",300'

;

WITH cte AS -- get the shredded list with " as delimiter

(

SELECT * FROM #tempWeirdExcelList

CROSS APPLY dbo.delimitedsplit8k(list,'"')

)

, cte2 AS -- remove the comma(s) from every even element (this will always be the element within the text identifier)

(

SELECT

id,

list,

itemnumber,

CASE WHEN itemnumber%2=0 THEN REPLACE(item,',','') ELSE item END AS newitem

FROM cte

)

-- get the list back together using the FOR XML PATH method

SELECT

list,

STUFF((SELECT '"' + newitem FROM cte2 t2 WHERE t2.id = t1.id ORDER BY itemnumber FOR XML PATH('')),1,1,'') AS new_list

FROM cte2 t1

GROUP BY id,list

/*

result set for the test data:

listnew_list

1400.00,"" ,17.50, 3,0,0 ,",,,,," 1400.00,"" ,17.50, 3,0,0 ,""

"1,400.00","",17.50,300"1400.00","",17.50,300

"1,400.00",",,,,",17.50,300"1400.00","",17.50,300

"1,400.00",17.50,300"1400.00",17.50,300

"1400.00","17,.5,0",300"1400.00","17.50",300*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

SQLSmasher (11/30/2010)
Jeff,
The method i can straight away suggest is to use SSIS.
1. Create in-memory single column table with each csv row as table row
2. find COMMA's between "xxxx,yyyy"
3. replace with "xxxxyyyy"
4. continue with your particular SSIS job by reteriving data from this new table
Thanks

But that doesn't address the problem of "1,400.00" in the same column as 900.00 (no quotes because no embedded comma) and there's no code in your example to do it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

santosh.hungund SSC Enthusiast Points: 136 More actions · Answer 3

Hi All,

I am also facing same issue. Previously when I encountered this issue it got resolved automatically when changed first column name in flat file connection manager and reverting it back in advanced tab.

Same is not working today for another SSIS package. This is in SQL server 2012 and using BIDS 2010.

Basically in columns tab of flat file connection manager if horizontal and vertical scroll bar appears in case of more number of columns & rows then everything works fine if the scroll bar does not appear though you have more columns & rows then above issue occurs. This is my observation but don't know how to set it right.:-D

May be bug in MS SSIS. :w00t:

Regards,

Santosh R