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

Bulk Inserting Comma separated Quotation Delimited text file Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 4:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:41 AM
Points: 75, Visits: 142
I am receiving a text file with comma separated data with quotation marks around certain fields that I need to bulk insert into SQL Server 2008 R2. An example of the data is:

"BUNGALOW 101,RIDGEWAY DR","POINT FORTIN",,,"TT","868-389-9769",,"ZPXB303191",1

As you can see there is a comma in the first field.

Using this Bulk Insert command causes that first field to be split into two columns. I know BOL says this is what is supposed to happen and to choose the separators carefully, but I have no control over the text file which is created by a 20 year old system which cannot be changed.

Any ideas how I can import this?
Post #1406531
Posted Sunday, January 13, 2013 6:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 3,420, Visits: 5,347
I'm not sure if this will help you, but the following code will replace the comma embedded between quotation marks (as long as it doesn't appear in the first position) with a semicolon.

;WITH MyData (ID, strcol) AS (
SELECT 1, '"BUNGALOW 101,RIDGEWAY DR","POINT FORTIN",,,"TT","868-389-9769",,"ZPXB303191",1'),
NewData (ID, ItemNumber, strcol) AS (
SELECT ID, ItemNumber
,strcol=CASE WHEN CHARINDEX(',', Item) > 1 THEN REPLACE(Item, ',', ';') ELSE Item END
FROM MyData
CROSS APPLY PatternSplitCM(strcol, '["]'))
SELECT ID, (
SELECT strcol + ''
FROM NewData b
WHERE a.ID = b.ID
ORDER BY ItemNumber
FOR XML PATH(''))
FROM NewData a
GROUP BY ID


The PatternSplitCM FUNCTION can be found in the 4th article linked into my signature line.

You would need to decide what character was appropriate to use in place of semicolon, then REPLACE it back to comma after BULK INSERT completes its task (if that is possible).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1406538
Posted Sunday, January 13, 2013 7:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
SSIS would handle this very nicely and be easily repeatable.
Post #1406541
Posted Sunday, January 13, 2013 8:39 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
jerry-621596 (1/13/2013)
SSIS would handle this very nicely and be easily repeatable.


Agreed. ACE drivers with OPENROWSET would also work well.


--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 #1406552
Posted Monday, January 14, 2013 4:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:41 AM
Points: 75, Visits: 142
Thanks Dwain,

I'll try that out
Post #1406654
Posted Monday, January 14, 2013 4:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:41 AM
Points: 75, Visits: 142
Sorry, I should have said this was SQL Server 2008 R2 Express. I don't think SSIS is available.
Post #1406655
Posted Monday, January 14, 2013 4:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:41 AM
Points: 75, Visits: 142
Jeff Moden (1/13/2013)
jerry-621596 (1/13/2013)
SSIS would handle this very nicely and be easily repeatable.


Agreed. ACE drivers with OPENROWSET would also work well.


Thanks, I'll research that.
Post #1406656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse