Solved

BULK INSERT most recent CSV

Posted on 2016-11-30
19
68 Views
Last Modified: 2016-11-30
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
Comment
Question by:Zack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
  • +1
19 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41906891
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
 

Author Comment

by:Zack
ID: 41906896
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
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41906898
you probably need to think for a proper ETL (Extraction, Transformation, Load) approach and build your logic accordingly.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 100 total points
ID: 41906913
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
 

Author Comment

by:Zack
ID: 41906921
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906927
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
 

Author Comment

by:Zack
ID: 41906928
Hi Pawan,

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

Thank you.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906930
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
 

Author Comment

by:Zack
ID: 41906934
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906937
Great , please follow !!
0
 

Author Comment

by:Zack
ID: 41906943
Hi Pawan,

Keep getting a compile on the script you gave me.

How do I proceed?

Thank you.
Compile-Error-on-Build.PNG
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906945
Add this namespace in your code..

using System.IO;
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 400 total points
ID: 41906948
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
 

Author Comment

by:Zack
ID: 41906949
Hi Pawan,

Compile error again, see attached.

Thank you.
Complie-Error-2.PNG
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906953
Add this also

using System.Linq;
0
 

Author Comment

by:Zack
ID: 41906955
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41906957
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
 

Author Comment

by:Zack
ID: 41906964
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
 

Author Closing Comment

by:Zack
ID: 41906969
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 29
Sorting a SQL script 5 34
Creating a View from a CTE 15 39
Sql server query 8 13
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

736 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question