CODEWITHSUNDEEP
sql-serverssisssmssql-server-2016ssis-2012
Roger Clerkwell
Roger Clerkwell

Reputation: 426

Import a CSV comma-delimited file into a SQL Server table

I need to import a .CSV file into a SQL Server table and I'm having problems due to " appearing within the string.

I have found the problem 

lines ,"32" Leather Bike Trs ",

enter image description here

It never splits the column.

enter image description here

I've been trying to solve this for hours, what I'm I missing here.

If it can't be done with SSMS Import.

Can it be done in SSIS, import as one big column and use SQL, C# script, what would be my next step to research?

Thanks.

Below sample line to put into a csv file to try.

"Company","Customer No","Store No","Store Name","Channel","POS Terminal No","Currency Code","Exchange Rate","Sales Order No","Date of Sales Order","Date of Transaction","Transaction No","Line No","Division Code","Item Category Code","Budget Group Description","Item Description","Item Status","Item Variant Season Code","Item No","Variant Code","Colour Code","Size","Original Price","Price","Quantity","Cost Amount","Net Amount","Value Including Tax","Discount Amount","Original Store No","Original POS Terminal No","Original Trans No","Original Line No","Original Sales Order No","Discount Code","Refund Code","Web Return Description" "Motor City","","561","Outback","In-store","P12301","HKD","1","","","20160218","185","10000","MT","WW","Jeans","32" Leather Bike Trs ","In Stock","9902","K346T4","BK12","BK","12","180.00000000000000000000","149.00000000000000000000","1.00000000000000000000","34.12500000000000000000","135.45000000000000000000","149.00000000000000000000",".00000000000000000000","","","0","0","","","",""

Upvotes: 2

Views: 460

Answers (1)

Hugo Salaun
Hugo Salaun

Reputation: 206

You're right the issue come from one " placed in your text. The fun fact is, if you had 2 " in your text, SSMS could handle it (as many other tools).

Maybe you should consider the possibility to change the text qualifier of your file before implementing a SSIS package ?

Upvotes: 1

Related Questions