visual-studio-2019bimlssis-2017

replace all double quotes with nothing in csv file in BIML script


I am importing flatfile connections using BIML. " is used around text and ; is used as delimiter. However, in some of the files I see this:

;"this is valid text""";

There are double double quotes with nothing between them. If I edit the file and search & replace all double double quotes with nothing, the import runs well. So, is it in BIML possible to do this action automagically? Search al instances of "" and replace these with ?

<#
string[] myFiles = Directory.GetFiles(path, extension);
string[] myColumns;

       // Loop trough the files
            int TableCount = 0;
            foreach (string filePath in myFiles)
            { 
                TableCount++;
                fileName = Path.GetFileNameWithoutExtension(filePath);
                #>
                <Package Name="stg_<#=prefix#>_<#=TableCount.ToString()#>_<#=fileName#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None" ProtectionLevel="<#=protectionlevel#>" PackagePassword="<#=packagepassword#>">
                    <Variables>
                        <Variable Name="CountStage" DataType="Int32" Namespace="User">0</Variable>
                    </Variables>               
                    <Tasks>
                        <ExecuteSQL ConnectionName="STG_<#=application#>" Name="SQL-Truncate <#=fileName#>">
                            <DirectInput>TRUNCATE TABLE <#=dest_schema#>.<#=fileName#></DirectInput>
                        </ExecuteSQL>
                         
                        <Dataflow Name="DFT-Transport CSV_<#=fileName#>">
                            <Transformations>
                                <FlatFileSource Name="SRC_FF-<#=fileName#> " ConnectionName="FF_CSV-<#=Path.GetFileNameWithoutExtension(filePath)#>">
                                </FlatFileSource>
                                 
                                <OleDbDestination ConnectionName="STG_<#=application#>" Name="OLE_DST-<#=fileName#>" >
                                <ExternalTableOutput Table="<#=dest_schema#>.<#=fileName#>"/>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Package>
    <#    }    #>

Solution

  • Turns out I was looking completely at the wrong place for this. Went to the part where the file is read and added .Replace("\"\"","")

    myColumns = myFile.ReadLine().Replace("""","").Replace(separator,"").Split(delimiter);