question

sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 asked ZoeHui-MSFT edited

How to convert Excel file into CSV within script task

Hello All,

I have a script task which will basically pick up the latest file from the folder and then pass it to the "Data Flow Task" where it will be loaded into the SQL Server, this process runs fine on visual studio however it fails on SQL agent because of the missing drivers. The DBA team is working on it however i was wondering if i can convert my picked up latest excel file into a CSV ? Because the csv automation on Agent runs fine.

Below is the code i have currently in Script task, Can someone please help me with this?




region Namespaces

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

endregion


namespace ST_9742f4376d5c4b3da8923e028d63a7a8
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());

         FileInfo[] files = directory.GetFiles("*LC_-_State*");
         DateTime lastModified = DateTime.MinValue;

         foreach (FileInfo file in files)
         {
             if (file.LastWriteTime > lastModified)
             {
                 lastModified = file.LastWriteTime;
                 Dts.Variables["User::VarFileName"].Value = file.ToString();
             }
         }

         //MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());

         Dts.TaskResult = (int)ScriptResults.Success;
     }

     #region ScriptResults declaration
     /// <summary>
     /// This enum provides a convenient shorthand within the scope of this class for setting the
     /// result of the script.
     /// 
     /// This code was generated automatically.
     /// </summary>
     enum ScriptResults
     {
         Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
         Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
     };
     #endregion

 }

}

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @sujithkumarmatharasi-0343,

It is better to stay with the existing DFT to process MS Excel file.

Though here is how to retrieve a latest fully qualified file name in the directory based on its datetime attribute. There is no need in any loop, like your current implementation is doing.

c#

 void Main()
 {
  var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());
  FileInfo file = directory.GetFiles("*LC_-_State*")
  .OrderByDescending(d => d.LastWriteTime)
  .FirstOrDefault();
    
  Dts.Variables["User::VarFileName"].Value = file.FullName;
 }
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 answered JeffreyWilliams-3310 commented

Hi @YitzhakKhabinsky-0887 ,

My only concern is i cannot automate the excel file through SQL agent so i need to convert it to csv. My current code already picks up the latest excel file its just i need to add something in here which will convert that picked up file to csv.

Thanks

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You have to be able to open and read the Excel file - and that requires the ACE drivers or ExcelDataReader or some other component to be installed on the server. It would be easier to just wait for the DBA's to get the ACE 64 and 32 bit drivers installed on the server.

You can install either one first with no problems - but installing the other version requires you to run that install from the command line using /quiet or /passive (depends on version being installed). So - if you install x64 first (which on a server is the one you really need), then you install the x86 version from the command line.

0 Votes 0 ·
ZoeHui-MSFT avatar image
1 Vote"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @sujithkumarmatharasi-0343,

Have you installed the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing?

You may install the provider for a try.

https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Also here are two articles about converting-excel-files-into-csv-and-uploading-files-using-ssis & ssis-how-to-convert-excel-file-to-csv.html.

Just for your reference.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.