SQL inserting from csv with converting filepath to binary image value at the same time

Question

I'm trying to make sql script that will add to a table some rows with different data types in each field.

table has 4 columns - ID, first name, last name, image

I have a csv file with about 4000 rows with those columns (last one as a path to image file):

ex.

1,'John','Smith','d:\images\johnsmith.jpg'

I can add single row with this code:

INSERT INTO [Employee] ([ID],[First_Name],[Last_Name],[Image])
     SELECT
            1 AS [ID]
           ,'John' AS [First_Name]
           ,'Smith' AS [Last_Name]
           , * FROM OPENROWSET(BULK 'd:\images\johnsmith.jpg', SINGLE_BLOB) AS [Image];
GO

But I would like to import this that like this

BULK INSERT [Employee]
    FROM 'd:\tmp\data.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row

    TABLOCK
    )

GO

however with this method I cannot not convert filepath to image data type. Maybe there is a way to run a command saved in csv fil, so I could make the file something like:

1,'John','Smith','* FROM OPENROWSET(BULK 'd:\images\johnsmith.jpg', SINGLE_BLOB)'

Is there any simple way to do this? I'm not very good with sql

why don't you use SSIS?

Fuzzy
– Fuzzy

2016-03-01 13:59:14 +00:00
Commented Mar 1, 2016 at 13:59 — Fuzzy
– Fuzzy, Commented Mar 1, 2016 at 13:59

Søren Kongstad · Accepted Answer · 2016-03-01 14:39:40Z

First of all - Be sure not to use the IMAGE datatye, use varbinary(max) https://msdn.microsoft.com/en-us/library/ms187993.aspx

Second of all, you will run into a problem with OPENROWSET. It does not allow for variables in its parameters, so you cannot make the

, * FROM OPENROWSET(BULK 'd:\images\johnsmith.jpg', SINGLE_BLOB) AS [Image];

query work inside a select (with different files)

For a brute force solution, do it in two steps. First load the CSV into a table.

    BULK INSERT [#TempEmployee]
    FROM 'd:\tmp\data.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row

    TABLOCK
    )

Then create the bulkloads dynamically:

    create table #TempEmployee (
        [ID] nvarchar(max),
        [First_Name] nvarchar(max),
        [Last_Name] nvarchar(max),
        [FilePath] nvarchar(max))

Declare @SQL nvarchar(max)
Declare TempEmployees CURSOR FOR
SELECT N'
    INSERT INTO [Employee] ([ID],[First_Name],[Last_Name],[Image])
    SELECT
        '''+[ID]+''' AS [ID]
       ,'''+[First_Name]+''' AS [First_Name]
       ,'''+[Last_Name]+''' AS [Last_Name]
       , * FROM OPENROWSET(BULK '''+[FilePath]+''', SINGLE_BLOB) AS [Image];' as [SQL]
FROM #TempEmployee

OPEN TempEmployees 
FETCH NEXT FROM TempEmployees 
INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
   exec (@SQL);
   FETCH NEXT FROM TempEmployees 
   INTO @SQL
END
CLOSE TempEmployees
DEALLOCATE TempEmployees
DROP TABLE #TempEmployee

Another solution would be to create a filetable in the database, and compy all of the image files to the filetable. Now load the csv file, and you have the employee data including filename and path to image in the Employee table and the images in the Filetable.

For an overview of Filetables see here: http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html You've got to have SQL Server 2012 or later for that to work

Collectives™ on Stack Overflow

SQL inserting from csv with converting filepath to binary image value at the same time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related