I would like to use OPENROWSET with the BULK command to load data into SQL Server as a CLOB, and as a second step to parse the CLOB and load read the data as a table.
E.g.:
SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\somedir\somefile.txt', SINGLE_CLOB) TheFile
yields:
BulkColumn
Col1,Col2,Col3,1,2,3,1,2,3,1,2,3,1,2,3
I want to select this as:
Col1 Col2 Col3
1 2 3
1 2 3
1 2 3
Create a format file and use OPENROWSET or BULK INSERT to import data from text file. Example format.xml:
This is the key line:
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1" />
where the terminator char is "," and not "\r\n", if the data is in a single line.Example OPENROWSET:
Example BULK INSERT: