Excel to text files conversion
Hi I have created one script which is taking Excel file and converting it to .txt files.But the code is not converting some of the excel files correctly. I dont know the reason, the reason might be different formats in one worksheet or some other issue. But the thing is if the format is coming differenty then also I want to handle the excel and each column should be generated in .txt files.Can anyone please help me regarding this problem as this is very urgent.
usingSystem;System.IO;
using
System.Data;using
System.Data.OleDb;using
System.Collections.Generic;using
System.ComponentModel;using
System.Text;using
System.Data.SqlClient;using
System.Windows.Forms;//using System.Threading;namespace
{
[System.AddIn.
ST_429b5e76bab24ae3a998c68118acd459.csprojAddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase{
#region
VSTA generated codeenum ScriptResults{
Success = Microsoft.SqlServer.Dts.Runtime.
Failure = Microsoft.SqlServer.Dts.Runtime.
};
DTSExecResult.Success,DTSExecResult.Failure#endregion
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
/*
{
convertExcelToCSV();
public void Main()// TODO: Add your code hereDts.TaskResult = (
}
{
connectionString = Dts.Variables[
connectionString = connectionString.Replace(
connectionString = connectionString.Replace(
cnn =
cnn.Open();
sql =
sqlComm.CommandType =
sqlComm.CommandTimeout = 0;
sqlCommdes.CommandTimeout = 0;
sqlADP.Fill(report);
sqlADPdes.Fill(reportdes);
sourceFile_CP = row[0].ToString();
Target_Directory_CP = rowdes[0].ToString();
cnn.Close();
int)ScriptResults.Success;private void convertExcelToCSV()string worksheetName = String.Empty;string targetFile = String.Empty;string sourceFile_CP=null;string Target_Directory_CP=null;string lookupSheet = String.Empty;SqlConnection cnn;string connectionString = null;string sql = null;"User::v_ETL_MP_DQIMTD"].Value.ToString();"Provider=SQLNCLI10.1;", "");"Auto Translate=False", "");new SqlConnection(connectionString);"select ConfiguredValue from dbo.PackageConfigurationsLog_Source_To_PreStage_M where ConfigurationFilter='ExcelSrcPath'";string sqldes = "select ConfiguredValue from dbo.PackageConfigurationsLog_Source_To_PreStage_M where ConfigurationFilter='Destination_Directory'";SqlCommand sqlComm = new SqlCommand(sql, cnn);SqlCommand sqlCommdes = new SqlCommand(sqldes, cnn);CommandType.Text;SqlDataAdapter sqlADP = new SqlDataAdapter(sqlComm);SqlDataAdapter sqlADPdes = new SqlDataAdapter(sqlCommdes);DataSet report = new DataSet();DataSet reportdes = new DataSet();DataRow row = report.Tables[0].Rows[0];DataRow rowdes = reportdes.Tables[0].Rows[0];///**************For Reading Excel Source****
string strConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile_CP + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; OleDbConnection conn = null;StreamWriter wrtr = null;OleDbCommand cmd = null;OleDbDataAdapter da = null;try{
conn =
conn.Open();
sqlselect =
sqlselectComm.CommandType =
sqlselectComm.CommandTimeout = 0;
new OleDbConnection(strConn1);string sqlselect = null;"SELECT FileName FROM [ETL_MP_DQIMTD].[dbo].[FileName_ColumnName_M] where Data in ('CP') and FileName Not like '%ForecastSalesDomestic_%'";SqlCommand sqlselectComm = new SqlCommand(sqlselect, cnn);CommandType.Text;SqlDataAdapter sqlselectADP = new SqlDataAdapter(sqlselectComm);//DataSet report = new DataSet();
sqlselectADP.Fill(Tab);
{
{
lookupSheet = Row[
{
{
worksheetName = rows[
{
targetFile = Target_Directory_CP + Export_File_Name;
cmd =
cmd.CommandType =
wrtr =
da =
da.Fill(dt);
{
HdrString.Append(dt.Columns[y].ColumnName.ToString() +
}
wrtr.WriteLine(HdrString.ToString().Substring(0, HdrString.Length - 1));
{
{
rowString.Append((
DataTable Tab = new DataTable();if (Tab != null)foreach (DataRow Row in Tab.Rows)int v_IsFileExists = 0;Boolean the_fire_again = true;"FileName"].ToString();DataTable dtWS = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);if ((dtWS != null))foreach (DataRow rows in dtWS.Rows)"TABLE_NAME"].ToString().Replace("'", "");if (worksheetName.Contains(lookupSheet) && (worksheetName.EndsWith("$") == true))string Export_File_Name = rows["TABLE_NAME"].ToString().Replace(">", "").Replace("$", "").Replace("'", "").Trim() + ".txt";new OleDbCommand("SELECT * FROM [" + worksheetName + "]", conn);CommandType.Text;new StreamWriter(targetFile);new OleDbDataAdapter(cmd);DataTable dt = new DataTable();StringBuilder HdrString = new StringBuilder();for (int y = 0; y < dt.Columns.Count; y++)";");for (int x = 0; x < dt.Rows.Count; x++)StringBuilder rowString = new StringBuilder();for (int y = 0; y < dt.Columns.Count; y++)if (dt.Rows[x][y].ToString().Contains("e+"))Convert.ToDouble(dt.Rows[x][y])).ToString() + ";");elserowString.Append(dt.Rows[x][y].ToString() +
}
wrtr.WriteLine(rowString.ToString().Substring(0, rowString.Length - 1));
}
wrtr.Close();
wrtr.Dispose();
v_IsFileExists = 1;
}
}
}
{
Dts.Events.FireInformation(0,
";");if (v_IsFileExists != 1)string Filename = Row["FileName"].ToString();"FileName: ", Filename + " is not found", string.Empty, 0, ref the_fire_again);//MessageBox.Show(Filename+" "+"Does not Exist in the Sheet");}
}
}
}
{
}
catch (Exception exc)Console.WriteLine(exc.ToString());Console.ReadLine();finally{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
}
}
}
}
if (conn.State == ConnectionState.Open)
using