?
Solved

BULK INSERT most recent CSV

Posted on 2016-11-30
19
Medium Priority
?
82 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 51

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 53

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 400 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 29

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 29

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 29

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 29

Expert Comment

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

using System.IO;
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1600 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 29

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 51

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

741 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