====== Reading CSV files in MSSQL ======

===== bcp utility =====
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. See [[http://technet.microsoft.com/en-us/library/ms162802(v=sql.105).aspx|bsp utility on Technet]].

== Allow xp_cmdshell ==

<code sql>
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
</code>

=== Format file ===
Read more details on [[http://technet.microsoft.com/en-us/library/ms191516(v=sql.105).aspx|Technet]]
== Structure ==
<code>
version
Number of columns
col number, file data type, prefix length, data length, terminator, table col order, table col name, file collation
</code>
== Example ==
<code>
10.0
3
1       SQLCHAR             0       12     ","     1     Col1                                       ""
2       SQLCHAR             0       12     ","     2     Col2                                       SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       12     ","     3     Col3                                       SQL_Latin1_General_CP1_CI_AS
</code>
[[http://technet.microsoft.com/en-us/library/ms189110.aspx|Data types]]

== Generating format file ==
<code>
EXEC xp_cmdshell 'bcp DB..MyTable format nul -c -f c:\formatFile.txt -T'
</code>

=== Reading the file ===
<code sql>
EXEC xp_cmdshell 'bcp DB..MyTable in c:\DataSource.csv -f c:\formatFile.txt -T'

</code>
===== Bulk insert =====
<code sql>
bulk insert dbo.a from 'c:\Projects\DataSet4assign.csv' with (formatfile = 'c:\Projects\formatFile.txt')
</code>

====== Sources ======

  * [[http://technet.microsoft.com/en-us/library/ms162802(v=sql.105).aspx]]
  * [[http://technet.microsoft.com/en-us/library/ms190693.aspx]]
  * [[http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html]]