Click here to Skip to main content
15,902,636 members
Home / Discussions / C#
   

C#

 
GeneralRe: change focous when enter texbox Pin
Cenator23-Jul-15 23:54
Cenator23-Jul-15 23:54 
GeneralRe: change focous when enter texbox Pin
Dave Kreskowiak24-Jul-15 2:12
mveDave Kreskowiak24-Jul-15 2:12 
QuestionRe: change focous when enter texbox Pin
Cenator24-Jul-15 2:46
Cenator24-Jul-15 2:46 
AnswerRe: change focous when enter texbox Pin
Dave Kreskowiak24-Jul-15 3:37
mveDave Kreskowiak24-Jul-15 3:37 
GeneralRe: change focous when enter texbox Pin
Cenator24-Jul-15 3:54
Cenator24-Jul-15 3:54 
QuestionChanging IP Address with program. Pin
Member 1129387623-Jul-15 22:14
Member 1129387623-Jul-15 22:14 
AnswerRe: Changing IP Address with program. Pin
OriginalGriff23-Jul-15 23:24
mveOriginalGriff23-Jul-15 23:24 
QuestionC# program for my SSIS script task help Pin
czaar99923-Jul-15 21:16
czaar99923-Jul-15 21:16 
I am writing a code for loading a multiple flat file into SQL server. Each flat file has different number of columns. Below are few examples.


FlatFile1:

sampleID ;  rep# ; protein  ; fat ; Lactose ; TS ; SNF ; GLucose;
12334    ;   1   ; 0.23    ;  0.4 ; 0.23    ; 3.4; 4.5 ; 0.9;


FlatFile2:

sampleID ;  rep# ; Water ; TNC ; protein  ; TS ; SNF ; Fat;
12134    ;   1   ; 6.1   ; 2.1 ; 5.6      ; 1.3; 6.5 ; 0.4;


FlatFile3:

sampleID ;  rep# ; Ph ; Fat ; protein  ; TS ;
10986    ;   1   ; 6.1; 2.1 ; 5.6      ; 1.3; 
10207     ;   2   ; 5.0; 1.3 ; 3.1      ; 1.45;  


My Output:
sampleID   rep#   protein  TS   Fat 
12334       1       0.23   3.4  0.4
12134       1        5.6   1.3  0.4
10986       1        5.6   1.3  2.1
10207       2        3.1   1.45 1.3


My C# knowldege is very basic. After doing some research I came up with some code which is like below.

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

namespace SSISDYnanic.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion



        public void Main()
        {
            {

                string delimiter = Dts.Variables["User::Delimiter"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)
             (Dts.Connections["BIReports"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Reading file names one by one
                string SourceDirectory = Dts.Variables["User::SourceFolder"].Value.ToString();
                string[] fileEntries = Directory.GetFiles(SourceDirectory);
                foreach (string fileName in fileEntries)
                {

                    // MessageBox.Show(fileName);
                    string columname = "";

                    //Reading first line of each file and assign to variable
                    System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);



                    //Writing Data of File Into Table
                    int counter = 0;
                    string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {

                  if (counter == 0)
                        {
                            columname = line.ToString();
                            columname = columname.substring(length(columname)-1,1).replace(";","")//Not working
                            columname = "" +columname.Replace(delimiter, ",");
                            MessageBox.Show(columname);
                        }

                        else
                        {
                            // MessageBox.Show("Inside ELSE");
                            string query = "Insert into " + TableName +
               "(" + columname + ") VALUES('" + line.Replace(delimiter, "','") + "')";
                            //MessageBox.Show(query.ToString());
                            SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                            myCommand1.ExecuteNonQuery();
                        }

                        counter++;

                    }

                    SourceFile.Close();
                }
                

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


1.) Problem with the code below is it writes all the columns into the table. And the table has to have all possible columns which I can't guess because I don't know. But no matter how many columns in the flat file I know the columns I want.

If code could be modified to pick only the 5 columns in my out put that would be great other wise if I could get help fixing below that is fine too.

2.) Also other problem is in the way the flat file is it ends with a delimiter (;) which results in a insert statement that ends with a ','for example
SQL
"Insert into tablename(sampleID ,rep#,Ph,Fat,protein,TS,) values (10986, 1,6.1,2.1,5.6,1.3,)"



columname = columname.substring(length(columname)-1,1).replace(";","")

So I added this in the code which doesn't work basically trying to replace last ":" with an empty space.
SVK

AnswerRe: C# program for my SSIS script task help Pin
Richard Deeming24-Jul-15 2:32
mveRichard Deeming24-Jul-15 2:32 
GeneralRe: C# program for my SSIS script task help Pin
czaar99924-Jul-15 21:42
czaar99924-Jul-15 21:42 
QuestionWays to store and insert x rows from mvc application to database Pin
ShikhaSC23-Jul-15 20:34
ShikhaSC23-Jul-15 20:34 
AnswerRe: Ways to store and insert x rows from mvc application to database Pin
Herman<T>.Instance23-Jul-15 21:21
Herman<T>.Instance23-Jul-15 21:21 
QuestionHow to raise font Height ? Pin
goldsoft23-Jul-15 9:38
goldsoft23-Jul-15 9:38 
AnswerRe: How to raise font Height ? Pin
Richard Deeming23-Jul-15 9:52
mveRichard Deeming23-Jul-15 9:52 
AnswerRe: How to raise font Height ? Pin
Sascha Lefèvre23-Jul-15 10:00
professionalSascha Lefèvre23-Jul-15 10:00 
GeneralRe: How to raise font Height ? Pin
goldsoft23-Jul-15 11:39
goldsoft23-Jul-15 11:39 
GeneralRe: How to raise font Height ? Pin
Richard MacCutchan23-Jul-15 20:54
mveRichard MacCutchan23-Jul-15 20:54 
GeneralRe: How to raise font Height ? Pin
Eddy Vluggen23-Jul-15 21:42
professionalEddy Vluggen23-Jul-15 21:42 
Questiontext to audio Pin
Member 1185276323-Jul-15 7:49
Member 1185276323-Jul-15 7:49 
AnswerRe: text to audio Pin
Dave Kreskowiak23-Jul-15 8:21
mveDave Kreskowiak23-Jul-15 8:21 
QuestionObject does not match target type Pin
Gilbert Consellado23-Jul-15 2:47
professionalGilbert Consellado23-Jul-15 2:47 
AnswerRe: Object does not match target type Pin
Eddy Vluggen23-Jul-15 3:31
professionalEddy Vluggen23-Jul-15 3:31 
GeneralRe: Object does not match target type Pin
Gilbert Consellado23-Jul-15 21:18
professionalGilbert Consellado23-Jul-15 21:18 
Questionc# error/warning code Pin
jamesmc153522-Jul-15 4:01
jamesmc153522-Jul-15 4:01 
AnswerRe: c# error/warning code Pin
CHill6022-Jul-15 4:07
mveCHill6022-Jul-15 4:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.