June 14, 2010 at 9:34 am
I am trying to import a csv file using openrowset on SQL 2005, Server 2003, both 64 bit.
The Issue: I need to be able to insert into a table without knowing the field names before the insert. the reason is that we receive partner records from multiple partners, and the files sometime lack some of the required fields, or the names have changes, etc. I am trying load the files into a table and then check to make sure all of the required fields are present. I am getting an error with Openrowset.
Can anyone help with the error of provide an alternative to loading csv when you don't know the field names?
Statement:
select *
from
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=\\ServerName\D$\SSIS\PartnerLoading\Working;Extensions=CSV;',
'SELECT * FROM [Account_And_Policy.csv]')
Error Message:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
thanks !!!
June 14, 2010 at 11:18 am
Hmm, I just did something similiar.
The way I did this was to use the BULK INSERT command to put the file into a staging table. I then get the first row (column headings), and use the DelimitedSplit function to rip it apart into separate entities. For the bulk insert, I utilized an XML format file.
First, the DelimitedSplit function:
CREATE function [dbo].[DelimitedSplit] (
@list varchar(max),
@Delimiter char(1)
)
RETURNS TABLE
AS
RETURN
-- first, need to break down into separate items.
-- See Jeff Moden's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (select 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
ItemSplit (ItemOrder, Item) AS (
SELECT N,
RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,CHARINDEX(',',@Delimiter + @list + @Delimiter,N+1)-N-1)))
FROM Tally
WHERE N < LEN(@Delimiter + @list + @Delimiter)
AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = ','
)
SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),
Item
FROM ItemSplit
ORDER BY ItemID
Secondly, the XML format file. Note that in the row/column source, you will need to change the name to whatever is the name of the column you are importing into.
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="8000"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
And finally, the code that puts this all together. For this, my CSV file is in the C:\Scripts directory, and is named "MyTestFile.csv". The XML format file is in the same directory, and is the same name as the source file with a ".xml" appended to the end.
if OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test (Col1 varchar(8000), RowID INT IDENTITY)
BULK INSERT #test
FROM 'C:\Scripts\MyTestFile.csv'
WITH (FORMATFILE='C:\Scripts\MyTestFile.csv.xml')
SELECT t.RowID, ds.Item
FROM #test t
CROSS APPLY dbo.DelimitedSplit(Col1, ',') ds
WHERE t.RowID = 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 17, 2010 at 1:03 pm
Thanks Wayne. A great Alternative.
June 20, 2010 at 12:21 pm
An alternative approach is to initially access the file using OPENROWSET BULK. The file is then split vertically using CROSS APPLY/Tally split method and within the same query reassembled horizontally using PIVOT. The detailed logic of the process is explained in the comments.
DECLARE @File VARCHAR(100)
SET @File = '\\ServerName\D$\SSIS\PartnerLoading\Working\Account_And_Policy.csv'
DECLARE @ROWSET NVARCHAR(255)
DECLARE @HeadersRAW VARCHAR(8000)
DECLARE @RowList VARCHAR(MAX)
DECLARE @ConvertHeaderList VARCHAR(MAX)
--First we need to extract the Headers into the variable @HeadersRAW
--We do this using OPENROWSET BULK to extract the first row using the unstructured format file BulkFile.fmt which has a single field called BulkColumn defined as SQLCHAR 8000
SET @ROWSET = N'SELECT @HeadersRAW = BulkColumn FROM OPENROWSET (BULK ''' + @File + ''', FORMATFILE = ''C:\BulkFile.fmt'', LASTROW = 1) AS X'
EXEC SP_EXECUTESQL @ROWSET, N'@HeadersRAW VARCHAR(8000) OUTPUT', @HeadersRAW OUTPUT
--This CTE Block factors the headers and the row list for the final query to work
--Here we split and flip the @HeadersRAW string into vertical data and then concatenate in the [1] AS [Column A] syntax
--Then we use FOR XML PATH to concatenate into comma-separated horizontal row @ConvertHeaderList
--also check for blank headers in which case use ROW instead as column header
--eg [1] AS [Column A],[2] AS [Column B],[3] AS [Column C],[4] AS [Column D],[5] AS [5],[6] AS [6]
--At the same time we evaluate the row list eg [1],[2],[3],[4],[5],[6]
;
WITH cteHeadersConvert1 AS
(
SELECT @HeadersRAW AS HeadersRAW
)
,
cteHeadersConvert2 AS
(
SELECT * FROM cteHeadersConvert1
CROSS APPLY
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(HeadersRAW + ',', N, CHARINDEX(',', HeadersRAW + ',', N) - N), '') AS Value
FROM master.dbo.Tally
WHERE N < LEN(HeadersRAW) + 2 AND SUBSTRING(',' + HeadersRAW + ',', N, 1) = ','
) AS Z
)
,
cteHeadersConvert3 AS
(
SELECT 0 AS Block, QUOTENAME(CONVERT(VARCHAR, ROW), ']') + ' AS ' + QUOTENAME(COALESCE(Value, CONVERT(VARCHAR, ROW)), ']') AS ConvertValue FROM cteHeadersConvert2
UNION ALL SELECT 1 AS Block, QUOTENAME(CONVERT(VARCHAR, ROW), ']') AS ConvertValue FROM cteHeadersConvert2
)
,
XMLHeadersConvert AS
(
SELECT DISTINCT Block, STUFF((SELECT ',' + ConvertValue FROM cteHeadersConvert3 AS t2 WHERE t2.Block = t1.Block FOR XML PATH('')), 1, 1, '') AS ConvertList
FROM cteHeadersConvert3 AS t1
)
,
cteCombined AS
(
SELECT (SELECT ConvertList FROM XMLHeadersConvert WHERE Block = 0) AS ConvertHeaderList, (SELECT ConvertList FROM XMLHeadersConvert WHERE Block = 1) AS RowList
)
SELECT @ConvertHeaderList = ConvertHeaderList, @RowList = RowList FROM cteCombined
--Now we have the variables we need to feed in to the dynamic SQL
--The data starting from row 2 is first imported using OPENROWSET BULK into an unstructured raw column and split vertically using CROSS APPLY/Tally split method
--Finally it is reassembled horizontally using PIVOT
EXEC(
'
;
WITH
cteFileData AS
(
SELECT
BulkColumn FROM OPENROWSET (BULK ''' + @File + ''', FORMATFILE = ''C:\BulkFile.fmt'', FIRSTROW = 2) AS X
)
,
cteData AS
(
SELECT ' + @ConvertHeaderList + ' FROM cteFileData
CROSS APPLY
(
SELECT ' + @RowList + ' FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
SUBSTRING(BulkColumn + '','', N, CHARINDEX('','', BulkColumn + '','', N) - N) AS Value
FROM master.dbo.Tally
WHERE N < LEN(BulkColumn) + 2 AND SUBSTRING('','' + BulkColumn + '','', N, 1) = '',''
) AS Z
PIVOT
(
MAX(Value) FOR ROW IN
(
' + @RowList + '
)
)
AS pvt
)
AS Y
)
SELECT * FROM cteData
'
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply