CODEWITHSUNDEEP
sqlcsvssis
Steve Wallace
Steve Wallace

Reputation: 104

SSIS, Importing multiple datasets from one CSV file

I have been given a set of csv files with 2 datasets, neither of which is a fixed length. Can anyone suggest how I could extract the datasets from the file to import them to seperate tables in SQL.

File format is

EDITED

If anyone wants to experiment, I am assuming the file is like this:

blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
blah 
SUMMARY
headers1
S1L1
S1L2
S1L3

DETAIL
headers2
S2L1
S2L2
S2L3

Upvotes: 0

Views: 386

Answers (2)

Steve Wallace
Steve Wallace

Reputation: 104

Based on Marks Suggestion, i built this as my first cut Script task. It needs The summary section adding but thats easy enough. (Scripted task Object c#)

    public void Main()
    {
        // TODO: Add your code here            
        System.IO.StreamWriter outfile = null;
        string line=null;
        string inputfile = Dts.Variables["SourceFiles"].Value.ToString();
        var infile = new System.IO.StreamReader(inputfile);
        string outpath = Dts.Variables["DetailFiles"].Value.ToString();
        int CounterPart = (int)Dts.Variables["CounterPart"].Value;
        outpath = string.Format(outpath, CounterPart++);
        outfile = new System.IO.StreamWriter(outpath);

        while (line != "Detail") {
            line = infile.ReadLine();
                    }

        while (!infile.EndOfStream)
        {
            line = infile.ReadLine();
            outfile.WriteLine(line);
        }

        outfile.Dispose();
        infile.Dispose();

        Dts.Variables["DataFile"].Value=outpath;
        Dts.Variables["CounterPart"].Value = CounterPart;

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

}

Upvotes: 0

Mark Setchell
Mark Setchell

Reputation: 207873

This script will do it if you can use awk:

awk 'BEGIN{out=""}/SUMMARY/{out="1.csv";next}/DETAIL/{out="2.csv";next}/^$/{out="";next} length(out){print > out}' file

At the start it sets the output filename to nothing. Then, if it sees the word "SUMMARY" it sets the output filename to "1.csv". If it sees the word "DETAIL" it sets the output filename to "2.csv". On other lines, it checks to see if the output file is specified, and writes to it if it is.

Your two sections will end up in "1.csv" and "2.csv". The script does not rely on numbers of lines at all, just the words "SUMMARY" and "DETAIL".

Upvotes: 1

Related Questions