Set row delimiter property on Flat File Connector

Question

I am creating a BIML script that will allow me to import CSV's.

However, the property Row Delimiter on the Flat File Connection Manager is somehow set to Vertical Bar, just as the Column Delimiter is. That prevents me from importing the files.

The column delimiter as a vertival bar right, I just do not get why it sets the row delimiter as vertical bar also...

Whenever I manually set the Row Delimiter to CRLF the thing works like a charm. But from BIML it is always set to the same value as the column delimiter.

I can not find where that happens....

I tried to use expressions in several ways to set the Row Delimiter but alas.....

Does anyone have any clue?


    <#
    string Applicatie = "BIML";
    string Prefix = "import";
     
    string fileName;
    string path = @"c:	emp";
    string[] myFiles = Directory.GetFiles(path, "*.csv");
     
    string[] myColumns;
    #>
    
    <#
    foreach (string filePath in myFiles)
    {
 
    #>
    
        
            <# 
                 
                StreamReader myFile = new StreamReader(filePath);
                myColumns = myFile.ReadLine().Replace("\"","").Split('|');
                 myFile.Close();
                 
                // to determine the column delimiter 
                int columnCount = 0;
                string columnDelimiter = "|";
 
                 foreach (string myColumn in myColumns) 
                {
                    columnCount++;
                    if (columnCount == myColumns.Length)
                    {
                        columnDelimiter = "CRLF";
                    }
                    {
                        columnDelimiter = "|";
                    }
                #>
                
                <# } #>
            
        
            <#}#>
    
    
            <#
            foreach (string filePath in myFiles)
            {
             
            #>
            
                 
                    "\""
                    "CRLF"
                    "CRLF"
                

            
            <# } #>
            
            
                 
                    
                        "\""
                        "CRLF"
                        "CRLF"
                    
                
     
        
 
    <#       // Loop trough the files
            int TableCount = 0;
            foreach (string filePath in myFiles)
            { 
                TableCount++;
                fileName = Path.GetFileNameWithoutExtension(filePath);
                #>
                
                    
                        0
                                   
                    
                        
                            TRUNCATE TABLE dbo.<#=Prefix#>_<#=fileName#>
                        
                         
                        
                            
                                
                                
                                 
                                
                                
                                
                            
                        
                    
                
    <#    }    #>
 
                
                
                    
                    <#  int TableCount2 = 0;
                        foreach (string filePath in myFiles)
                        { 
                                TableCount2++;
                                fileName = Path.GetFileNameWithoutExtension(filePath); #>
                 
                            
                                
                            
                        <#
                        }
                        #>    
                    
                
 
    

 

<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.IO"#>

This is the actual BIML outputted:


    
        
            
                "\""
                "CRLF"
            
        
        
    
    
        
            
                0
            
            
                
                    TRUNCATE TABLE dbo.import_AB

user_181965 · Answer

Found it

                if (columnCount == myColumns.Length)
                {
                    columnDelimiter = "CRLF";
                }
                {
                    columnDelimiter = "|";
                }

You lack an else in there. It should be

                if (columnCount == myColumns.Length)
                {
                    columnDelimiter = "CRLF";
                }
                else
                {
                    columnDelimiter = "|";
                }

Minimal repro


<#
List myColumns = new List{"A", "B", "C"};

#>

    
        
<#
int columnCount = 0;
string columnDelimiter = "";
WriteLine($"");
    foreach(string myColumn in myColumns)
    {
    
        columnCount++;
        bool finalColumn = columnCount == myColumns.Count;
        WriteLine($"");
        WriteLine($"");
        
        if (finalColumn)
        {
            columnDelimiter = "CRLF";
        }
        else
        {
            columnDelimiter = "|";
        }
        WriteLine($"");
        
#>
    
<#
    }
#>

Emitted Biml would look like

Set row delimiter property on Flat File Connector

1 Answers