Loading CSV file in SQL Server using SSIS when data is not formatted - Sandeep Kanao

Loading CSV file in SQL Server using SSIS when data is not formatted  - Sandeep Kanao

Problem Definition

File Format is comma(,)delimiter and some data with double quotes.

Input File format:
ID,Name,Address,salary
id=1,name="hari",address="ABC",salary="5000"
id=2,name="mahesh",address="ABC,BCD",salary="5000"

Destination table :
ID Name      Address Salary
1 hari          ABC             5000
2 Mahesh      ABC,BCD 5000

Try to load data with text qualifier double quotes(") it will not be loaded correctly.

Suggested Solution :

In above example, structure/pattern of data are similar, so we could use Script Component as Source to extract data from flat file.

1. Create output columns and give proper data type and length. Personally, I would suggest setting up data type  of all columns to string.

2. Use following script in SSIS

 public override void CreateOutputRows()
    {
       
       
        string[] lines = File.ReadAllLines(@"input.txt");

        for (int i = 1; i < lines.Length; i++)
        {
            int pFrom1 = lines[i].IndexOf("id=") + "id=".Length;
            int pTo1 = lines[i].LastIndexOf(",name");

            int pFrom2 = lines[i].IndexOf("name=\"") + "name=\"".Length;
            int pTo2 = lines[i].LastIndexOf("\",address");

            int pFrom3 = lines[i].IndexOf("address=\"") + "address=\"".Length;
            int pTo3 = lines[i].LastIndexOf("\",salary");

            int pFrom4 = lines[i].IndexOf("salary=\"") + "salary=\"".Length;

            Output0Buffer.AddRow();

            Output0Buffer.ID= lines[i].Substring(pFrom1, pTo1 - pFrom1);

            Output0Buffer.Name= lines[i].Substring(pFrom2, pTo2 - pFrom2);

            Output0Buffer.Address = lines[i].Substring(pFrom3, pTo3 - pFrom3);

            Output0Buffer.Salary = lines[i].Substring(pFrom4, lines.Length+1);

        }
    }

3. Run SSIS package, check the results - Sandeep Kanao

4. Convert the data type in destination table