March 30, 2017 at 12:20 pm
How can I Bulk insert a CSV pipe delimited file given path to a temp table and i want to access thatt temp table for further look up's
March 30, 2017 at 12:41 pm
komal145 - Thursday, March 30, 2017 12:20 PMHow can I Bulk insert a CSV pipe delimited file given path to a temp table and i want to access thatt temp table for further look up's
What have you tried so far and what happened?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 30, 2017 at 12:41 pm
DECLARE @filedirectory NVARCHAR(100) = 'E:\Temp\Filename\'
DECLARE @command NVARCHAR(1000) = 'dir /B "'+ @filedirectory +'"'
IF OBJECT_ID('TEMPDB..#tmp') IS NOT NULL
DROP TABLE #tmp
--Loop through all of the files
CREATE TABLE #tmp(jsonFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell @command;
DECLARE @fileName NVARCHAR(100)
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(SKu Nvarchar(250)
, [Product Status] Nvarchar(250)
,Title Nvarchar(250)
,[TI Category] Nvarchar(250)
,[TI Class] Nvarchar(250)
,[TI Sub Class] Nvarchar(250)
,[TI Price] int )
WHILE(SELECT Count(*) FROM #tmp WHERE jsonFileName is not null) > 0
BEGIN
SELECT TOP 1 @fileName = jsonFileName From #tmp
DECLARE @SQL NVARCHAR(4000)
SET @SQL = ''
+' BULK'
+' INSERT #temp'
+' FROM '''+REPLACE(@filedirectory,'\','\\') + REPLACE(@filename,'\','\\') --location with filename
+''' WITH'
+' ('
+' FIELDTERMINATOR = ''|'','
+' ROWTERMINATOR = ''\n'''
+' )'
EXEC sp_executesql @SQL
END
IS this correct...it takes long for me
March 30, 2017 at 9:49 pm
The reason it takes so long is because you're always loading the "TOP 1" file and there's nothing to end the loop. You've also got some other nuances in your code that I'd correct if it were me. With that in mind, here's how I'd write the code. Keep in mind that I don't have your data, your directory, your files, etc, etc. That's means that I haven't tested the code at all and there may be some errors that you need to fix. Details are in the comments.
--=====================================================================================================================
-- PRESETS
--=====================================================================================================================
--===== Drop all temp tables up front to make reruns in SSMS easier.
-- This section may be commented out if the code is converted to a stored procedure.
-- Keep in mind that all DDL should be done at the beginning to help avoid recompiles.
IF OBJECT_ID('tempdb..#FileName') IS NOT NULL DROP TABLE #tmp;
IF OBJECT_ID('tempdb..#ImportStaging') IS NOT NULL DROP TABLE #TempTable;
--===== For the same reasons as dropping temp tables up front, create them up front, as well.
-- Also, name your tables in a meaningful manner to make the code easier to understand.
CREATE TABLE #FileName
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED --added and the PK is important for performance.
,jsonFileName VARCHAR(500)
)
;
CREATE TABLE #ImportStaging
(
SKu VARCHAR(250) --Again, NVARCHAR is probably overkill.
,[Product Status] VARCHAR(250) --I'd also avoid spaces in my column names so no need for brackets.
,Title VARCHAR(250)
,[TI Category] VARCHAR(250)
,[TI Class] VARCHAR(250)
,[TI Sub Class] VARCHAR(250)
,[TI Price] INT
)
;
--===== Local variables
DECLARE @Command VARCHAR(1000) = 'dir "' + @filedirectory + '" /B' --changed, location of switches was bad
,@Counter INT = 1 --Added
,@FileDirectory VARCHAR(100) = 'E:\Temp\Filename\' --Probably no need for NVARCHAR
,@FileName VARCHAR(1000) --Again, probably no need vor NVARCHAR
,@SQL VARCHAR(4000)
;
--===== Load the full filenames to be imported.
-- The RowNum column is auto incrementing from 1.
INSERT INTO #tmp
(jsonFileName) --added
EXEC xp_cmdshell @command
;
--=====================================================================================================================
-- LOAD THE FILES
--=====================================================================================================================
--===== Get the first file name.
SELECT @FileName = jsonFileName
FROM #FileName
WHERE RowNum = @Counter --1 at this point
;
--===== Loop through the file names and load each file
WHILE @FileName > '' -- Not NULL or Blank
BEGIN
--===== Tokenized dynamic SQL is a whole lot easier to read/troubleshoot.
SELECT @SQL = REPLACE(REPLACE('
BULK INSERT #ImportStaging
FROM <<FilePath>>
WITH (
FIELDTERMINATOR = "|"
,ROWTERMINATOR = "\n"
)
;' -- Other end of the REPLACEs
,'"' ,'''')
,'<<FilePath>>',QUOTENAME(@FileDirectory + @FileName,'''')) --Careful! QUOTENAME is only good for 258 characters max.
; --It also helps with SQL Injection attempts.
--===== Execute the dynamic SQL to load the file into the staging table.
-- No need for sp_ExecuteSQL here so no need for NVARCHAR all over the place.
EXEC (@SQL)
;
--===== And now for the part you forgot. "BUMP" the counter to get to the next file name
SELECT @Counter += 1
;
--===== Get the next file name to work on. If it's NULL or blank, the loop will quit.
SELECT @FileName = jsonFileName
FROM #FileName
WHERE RowNum = @Counter
;
END
;
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply