[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

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
0
Zack
Asked:
Zack
  • 9
  • 6
  • 3
  • +1
2 Solutions
 
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
 
Ryan ChongCommented:
you probably need to think for a proper ETL (Extraction, Transformation, Load) approach and build your logic accordingly.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Pawan KumarDatabase 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
 
Vitor MontalvãoMSSQL 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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now