Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Avatar of Zack

ASKER

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.
you probably need to think for a proper ETL (Extraction, Transformation, Load) approach and build your logic accordingly.
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

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.
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.
Avatar of Zack

ASKER

Hi Pawan,

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

Thank you.
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.
Great , please follow !!
Avatar of Zack

ASKER

Hi Pawan,

Keep getting a compile on the script you gave me.

How do I proceed?

Thank you.
Compile-Error-on-Build.PNG
Add this namespace in your code..

using System.IO;
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Hi Pawan,

Compile error again, see attached.

Thank you.
Complie-Error-2.PNG
Add this also

using System.Linq;
Avatar of Zack

ASKER

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.
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

Avatar of Zack

ASKER

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.
Avatar of Zack

ASKER

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.