OpenRowset for CSV file returns blank or has errors
<p>So earlier, I am on the verge of creating a code that would import a CSV file that is machine generated, to our database. I created one in Excel, and I used this code</p>

<pre><code>select *from openrowset('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select * from D:\Test.CSV')
</code></pre>

<p>And it worked fine.
But when I am dealing with the actual data. The above code does not work.</p>

<p>So the CSV file contains a leading 18 row data which information could be dropped off (its just the name of the machine), the needed data is at the 19th row. </p>

<p>After searching, I found out a piece of code, and I tried it to the CSV file, which is </p>

<pre><code>    SELECT *
        FROM OPENROWSET(BULK 'D:\Data\sample\device1_2016-08-03_15-24-58.csv',
        FORMATFILE='D:\Data\sample\BCPFormat.xml',
        FIRSTROW = 19) AS a
</code></pre>

<p>but the data is blank!</p>

<p>I also tried this code</p>

<pre><code>select * from OpenRowset('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=D:\Data\sample\;','select * from device1_2016-08-03_15-24-58.csv')
</code></pre>

<p>The error states that</p>

<pre><code>An error occurred while preparing the query "select * from device1_2016-08-03_15-24-58.csv" for execution against OLE DB provider "MSDASQL" for linked server "(null)". 
</code></pre>

<p>The server DBMS that we are using is SQL Server 2008 R2. We are also using MS Office 2010 to create spreadsheets.</p>

<p>Any ideas are welcome, thank you in advance!</p>

<p>EDIT:</p>

<p>I'll be including the screenshot of the CSV File.  <a href="https://i.stack.imgur.com/iuoPP.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iuoPP.jpg" alt="screenshot"></a> </p>

<p>I'll also include the XML file (Because I have read that the FMT file is a XML file, kindly check if this is correct.)</p>

<pre><code>&lt;?xml version="1.0"?&gt;
&lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
 &lt;RECORD&gt;
  &lt;FIELD ID="1" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR=','/&gt;
  &lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="13" xsi:type="CharTerm" TERMINATOR=',' /&gt;
  &lt;FIELD ID="14" xsi:type="CharTerm" TERMINATOR='\n' /&gt;
 &lt;/RECORD&gt;
 &lt;ROW&gt;
  &lt;COLUMN SOURCE="1" NAME="NO." xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="2" NAME="Time" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="3" NAME="ms" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="4" NAME="degC1" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="5" NAME="degC2" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="6" NAME="degC3" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="7" NAME="degC4" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="8" NAME="degC5" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="9" NAME="degC6" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="10" NAME="A12345678901" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="11" NAME="A12345678902" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="12" NAME="A12345678903" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="13" NAME="A12345678904" xsi:type="SQLVARYCHAR" /&gt;
  &lt;COLUMN SOURCE="14" NAME="A1234" xsi:type="SQLVARYCHAR" /&gt;
 &lt;/ROW&gt;
&lt;/BCPFORMAT&gt;
</code></pre>

<p>I have included the screenshot of the the CSV file in textfile</p>

<pre><code>Vendor,GUARDIAN
Model,ZR-RX45
Version,Ver1.04
Sampling,10s
Total data points,0           
Start time,2016-08-03,15:25:01
End time,2016-08-03,15:24:59
Trigger time,2016-07-30,08:21:50
AMP Settings
CH,Signal name,Input,Range,Filter,Span
CH34, "PC-2",TEMP,TC_K,Off,250.000000,0.000000,degC
CH35, "PC-11",TEMP,TC_K,Off,250.000000,0.000000,degC
CH36, "PC-19",TEMP,TC_K,Off,250.000000,0.000000,degC
CH37, "PC-16",TEMP,TC_K,Off,250.000000,0.000000,degC
CH38, "PC-08",TEMP,TC_K,Off,250.000000,0.000000,degC
CH39, "PC-18",TEMP,TC_K,Off,250.000000,0.000000,degC
Logic/Pulse,Off
Data
Number,Date&amp;Time,ms,CH34,CH35,CH36,CH37,CH38,CH39,Alarm1-10,Alarm11-20,Alarm21-30,Alarm31-40,AlarmOut
NO.,Time,ms,degC,degC,degC,degC,degC,degC,A1234567890,A1234567890,A1234567890,A1234567890,A1234
1,2016-07-30 08:21:50,000,+0.0,+0.0,+0.0,+0.0,+0.0,+0.0,LLLLLLLLLL,LLLLLLLLLL,LLLLLLLLLL,LLLLLLLLLL,LLLL
</code></pre>