0

I have a text file I'm trying to import that contains 14 million records, but it's delimited with commas and there is one field that also contains commas, and there is no text qualifer to denote that the field has multiple values separated by commas. Does anyone know a way import with this scenario, or to correct delimited file for import?

I'm using SQL Server 2012. I'm open to correcting the text file via C#, VB.NET, or if there is a way in SSIS to correct it.

Jeremy Dempsey
  • 1
  • Does that field always contain the same number of commas or is it variable? – jmcilhinney Mar 09 '17 at 05:08
  • It's variable, that field may contain no commas, sometimes 2 values with commas or 3 values with commas – Jeremy Dempsey Mar 09 '17 at 05:13
  • Can you regenerate the CSV file WITH text qualifiers? – Jeroen Mar 09 '17 at 05:29
  • The text file is already generated, and I have no way to re-generate it. Unless I use C# or VB to programically place the field with double quotes around it. My scripting skills are very limitted though. Do you have a similar script that could do this? – Jeremy Dempsey Mar 09 '17 at 05:48
  • 2
    The only way I can think of to handle that would be to split the line on the commas, take the appropriate number of values from the beginning and the end and then assume what's left in the middle is that one field. If there were multiple fields like that then it would be impossible. – jmcilhinney Mar 09 '17 at 05:48
  • The values for the field in question can only be the following: NULL AGT AGT, CLT AGT, CLT, CTL, ISS AGT, CLT, ISS AGT, CLT, ISS, CTL AGT, CLT, ISS, EXCH AGT, CTL AGT, CTL, CLT, ISS AGT, ISS, CLT BROKDEAL BROKDEAL, CLT Client Role Dual Role Non Client Role – Jeremy Dempsey Mar 09 '17 at 06:40
  • All possible values of the column before Role is: Active Deactivated Merged Prospective All possible values of the column after Role is: NULL 1.1. Active Rem 1.1. Rem - Active Rem 1.3.4. Become Inactive – Jeremy Dempsey Mar 09 '17 at 06:41
  • You can use vb.net to insert those records. – Vijunav Vastivch Mar 09 '17 at 08:47
  • Possible duplicate of [Import CSV File Error : Column Value containing column delimiter](http://stackoverflow.com/questions/40412216/import-csv-file-error-column-value-containing-column-delimiter) – Hadi Mar 20 '17 at 21:59

0 Answers0