SQL Server从表中读取CSV二进制文件 [英] SQL Server read csv binary from table

问题描述

我目前将csv格式的文件存储在磁盘上，然后按以下方式查询它们:

I currently store my csv formatted files on disk and then query them like this:

SELECT *
FROM OPENROWSET(BULK 'C:\myfile.csv',
    FORMATFILE = 'C\format.fmt',
    FIRSTROW = 2) AS rs

其中format.fmt是csv文件中列的定义格式.这很好. 但是我有兴趣将文件存储在SQL Server表中，而不是将其存储在磁盘中. 因此，当具有VARBINARY(MAX)数据类型列时.如何查询它们?

Where format.fmt are the defined format of the columns in the csv file. This works very well. But I'm interested in storing the file in a SQL Server table instead of storing them at disk. So when having a VARBINARY(MAX) datatype column. How do I query them?

如果我有一个像这样的表:

If I have a table like:

CREATE TABLE FileTable
(
    [FileName] NVARCHAR(256)
    ,[File] VARBINARY(MAX)
)

带有一行'myfile.csv', '0x427574696B3B44616....'

例如，如何将文件内容读取到临时表中?

How to read that file content into a temporary table for example?

推荐答案

如果您确实需要使用varbinary数据，则可以将其强制转换回nvarchar:

If you really need to work with varbinary data, you can just cast it back to nvarchar:

DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5468697320697320612074657374

SELECT CAST(@bin as VARCHAR(MAX))
-- gives This is a test

一旦将其转换为该格式，就可以使用split函数将其转换为表格.别问我为什么在SQL Server中没有内置的拆分功能，因为它是如此令人震惊地显而易见，但是却没有.因此，使用以下代码创建自己的代码:

Once you've got it into that format, you can use a split function to turn it into a table. Don't ask me why there isn't a built-in split function in SQL Server, given that it's such a screamingly obvious oversight, but there isn't. So create your own with the code below:

CREATE FUNCTION [dbo].[fn_splitDelimitedToTable] ( @delimiter varchar(3), @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ([String] VARCHAR(100), [Hierarchy] int )
AS
BEGIN

    DECLARE @String    VARCHAR(100)
    DECLARE @row int = 0

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @row = @row + 1
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String], [Hierarchy] )
        VALUES ( @String, @row )
    END

    RETURN
END

将它们放在一起:

select CAST('one,two,three' as VARBINARY)
-- gives 0x6F6E652C74776F2C7468726565

DECLARE @bin VARBINARY(MAX)
SET @bin = 0x6F6E652C74776F2C7468726565

select * from fn_splitDelimitedToTable(',', CAST(@bin as VARCHAR(MAX)))

给出以下结果:

string hierarchy
================
one    1
two    2
three  3

当然，如果愿意，您可以将结果放入临时表中以供使用:

And of course, you can get the result into a temp table to work with if you so wish:

select * into #myTempTable
from fn_splitDelimitedToTable(',', CAST(@bin as VARCHAR(MAX)))

这篇关于SQL Server从表中读取CSV二进制文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！