BULK INSERT most recent CSV

Heyas,

How would I get a BULK INSERT statement to only insert the CSV with most recent file creation date?

Any assistance is welcome.

Thank you
ZackGeneral IT Goto GuyAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Zack, this is a T-SQL solution if you want to use it:
DECLARE @FileName AS NVARCHAR(128)
DECLARE @MaxDate AS VARCHAR(15)
DECLARE @BulkCmd varchar(1000);  

CREATE TABLE #DirectoryTree (
	subdirectory nvarchar(512),
	depth int,
    isfile bit);

INSERT INTO #DirectoryTree
EXEC master..xp_dirtree 'C:\', 1, 1

SELECT TOP 1 @FileName = subdirectory, @MaxDate=MAX(RIGHT(subdirectory, 12))
FROM #DirectoryTree
WHERE isfile=1 AND subdirectory LIKE '%.csv'
GROUP BY subdirectory
ORDER BY MAX(RIGHT(subdirectory, 12))

DROP TABLE #DirectoryTree

SET @BulkCmd = 'BULK INSERT TableName  
    FROM ''' + @FileName + '''
    WITH (ROWTERMINATOR = ''\n'')'  
EXEC(@BulkCmd);  

Open in new window

You'll need to provide the correct path (I've used C:\ in my example).
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server doesn't have functions to work with files.
I can see this being done with CLR or xp_cmdshell but with the latest one you'll need to enable it since isn't enabled by default for security reasons.
If the creation date is part of the file name maybe I can find a 3rd way to do this.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Vitor,

I just adjusted the PowerShell script that generates the file, it now has the creation date in the format is the following: AllMessages_MO_raw_20161001_20161031.csv

Thank you.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ryan ChongCommented:
you probably need to think for a proper ETL (Extraction, Transformation, Load) approach and build your logic accordingly.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try..

CREATE a SSIS Package.

In Script task, use like below to get the latest file name..

1. Get latest file name

var YourdirectoryName = new DirectoryInfo(@"E:\Pawan\SSIS\");
var YourFileName = YourdirectoryName.GetFiles()
             .OrderByDescending(k => k.LastWriteTime)
             .First();

2. Create a stored procedure with an Input parameter FileName

3. In that stored procedure use below
             
BULK INSERT dbo.YourtableName
FROM 'E:\Pawan\SSIS\FileName'
WITH
  (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
  );
                  
4. Check data in the table...

Hope it helps !!
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

Never created an SSIS package before give my some time to 'Google it' and I will let you know if I need any assistance.

Any tutorials you can recommend?

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
Hi,
Pls refer this MSDN URL - https://msdn.microsoft.com/en-us/library/ms135937.aspx

and inside the script task use below and check if you are getting the last file name or not.

var YourdirectoryName = new DirectoryInfo(@"E:\Pawan\SSIS\");
var YourFileName = YourdirectoryName.GetFiles()
             .OrderByDescending(k => k.LastWriteTime)
             .First();

Open in new window


Other steps will take later.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

That link is https://msdn.microsoft.com/en-us/library/ms135937.aspx is Active Directory how does that relate?

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
No leave that ... Just add a script task using the steps..

You have to use the code that I gave you.  In their example they have used active directory.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
0
 
Pawan KumarDatabase ExpertCommented:
Great , please follow !!
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

Keep getting a compile on the script you gave me.

How do I proceed?

Thank you.
Compile-Error-on-Build.PNG
0
 
Pawan KumarDatabase ExpertCommented:
Add this namespace in your code..

using System.IO;
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

Compile error again, see attached.

Thank you.
Complie-Error-2.PNG
0
 
Pawan KumarDatabase ExpertCommented:
Add this also

using System.Linq;
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Victor,

Using the following code:

DECLARE @FileName AS NVARCHAR(128)
DECLARE @MaxDate AS VARCHAR(15)
DECLARE @BulkCmd varchar(1000);  

CREATE TABLE #DirectoryTree (
	subdirectory nvarchar(512),
	depth int,
    isfile bit);

INSERT INTO #DirectoryTree
EXEC master..xp_dirtree 'D:\Test', 1, 1

SELECT TOP 1 @FileName = subdirectory, @MaxDate=MAX(RIGHT(subdirectory, 12))
FROM #DirectoryTree
WHERE isfile=1 AND subdirectory LIKE '%.csv'
GROUP BY subdirectory
ORDER BY MAX(RIGHT(subdirectory, 12))

DROP TABLE #DirectoryTree

USE MedicalObjectsReporting

SET @BulkCmd = 'BULK INSERT MOMSGS
    FROM ''' + @FileName + '''
    WITH (ROWTERMINATOR = ''\n'')'  
EXEC(@BulkCmd);  

Open in new window


I get the following error message:

(1 row(s) affected)
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "AllMessages_MO_raw_20161001_20161031.csv" does not exist.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, couldn't test it.
You need to add the correct path. Below example with a fake path (C:\MyFiles\):
SET @BulkCmd = 'BULK INSERT TableName  
    FROM ''C:\MyFiles\' + @FileName + '''
    WITH (ROWTERMINATOR = ''\n'')'  

Open in new window

0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Victor,

That worked below was the final script:

DECLARE @FileName AS NVARCHAR(128)
DECLARE @MaxDate AS VARCHAR(15)
DECLARE @BulkCmd varchar(1000);  

CREATE TABLE #DirectoryTree (
	subdirectory nvarchar(512),
	depth int,
    isfile bit);

INSERT INTO #DirectoryTree
EXEC master..xp_dirtree 'D:\Test', 1, 1

SELECT TOP 1 @FileName = subdirectory, @MaxDate=MAX(RIGHT(subdirectory, 12))
FROM #DirectoryTree
WHERE isfile=1 AND subdirectory LIKE '%.csv'
GROUP BY subdirectory
ORDER BY MAX(RIGHT(subdirectory, 12))

DROP TABLE #DirectoryTree

USE MedicalObjectsReporting

SET @BulkCmd = 'BULK INSERT MOMSGS
    FROM ''D:\test\' + @FileName + '''
    WITH ( FIRSTROW=2,
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n'')'  
EXEC(@BulkCmd);  

Open in new window


Thank you.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Thank you both for your assistance on this matter.

I will have to do some research on SSIS later. I have never used SSIS, so I didn't know running scripts via an SSIS package would be so seamless according to the tutorials I have been reading. Thank you Pawan for pointing this out to me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.