title: SQL import/create columns in SMILE chemical structures
body: <p>We try to import a csv file where the first column includes chemical structures (SMILE) like this</p>
  
  <pre><code> c1cccc(c12)n(C)c(c2)CN(C)C(=O)c(c3)ccc(c34)NCC(=O)N(C4)C,14-BENZODIAZEPINEDERIV.4_145,1
   c1cccc(c12)n(C)c(c2)CN(C)C(=O)c(c3)ccc(c34)N[C@H](C(=O)N(C4)C)CC(=O)OC,14-BENZODIAZEPINEDERIV.3_146,1
  </code></pre>
  
  <p>Here is the code in SQL</p>
  
  <pre><code>--Define Table
  CREATE TABLE Amide_actives_test
  (Structure VARCHAR(40),
  Name VARCHAR(40),
  Active INT)
  GO
  
  --Import Data from CSV
  
  BULK 
  INSERT Amide_actives_test
  FROM 'C:\Amide_actives.csv'
  WITH
  (
  FIELDTERMINATOR = ',',  --CSV field delimiter
  ROWTERMINATOR = '\n'   --Use to shift the control to next row
  )
  GO
  --Check the content of the table
  SELECT * FROM Amide_actives_test
  GO     
  </code></pre>
  
  <p>The following error message will pop out:</p>
  
  <pre><code>Bulk load data conversion error (truncation) for row 1, column 1 (Name).
  Msg 4863, Level 16, State 1, Line 10
  ...repeating the previous 2 lines 10 times....
  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not    give any information about the error.
  Msg 7330, Level 16, State 2, Line 10
  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
  </code></pre>
  
  <p>Apparently there is a problem of SQL to read the first column in "Structure VARCHAR(40)".  I have tried all the string types (CHAR,VARCHAR.NCHAR,NVARCHAR,NTEXT,TEXT) and none of them works.</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/ff848814.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ff848814.aspx</a></p>
  
  <p>There is one way to solve this issue is to purchase another customized MySQL module from DayLight. However, 1. it costs 2. it doesn't support SQL</p>
  
  <p><a href="http://www.daylight.com/dayhtml/doc/pgsql/daycart_pg_search.html" rel="nofollow">http://www.daylight.com/dayhtml/doc/pgsql/daycart_pg_search.html</a></p>
  
  <p>May I know if any SQL guru has SQL solutions? Thanks!</p>