[FIXED] How to improve the reading efficiency of a file (.CSV, .TXT) with Kotlin

Issue

I made a program in JavaFX with Kotlin I managed to make a CSV and TXT reading separated by ";" where I have efficiency problems and I don't know how I could improve the efficiency to make the SQL query construction.

fun generatedDelimited(filePath: String, table: String = "") {

        val sourceFile = File(filePath)
        var line: String?
        var header: Array<String>? = null

        val lines: MutableList<List<String>> = ArrayList()
        try {
            BufferedReader(FileReader(sourceFile)).use { br ->
                header = br.readLine().split(";").toTypedArray();
                while (br.readLine().also { line = it } != null) {
                    val values : Array<String> = line!!.split(";").toTypedArray();
                    lines.add(Arrays.asList(*values))
                }
            }
        } catch (e: IOException) {
            e.printStackTrace()
        }

        val joined = "INSERT INTO $table  (${header!!.joinToString(separator = ",")})\n"

        var textSelect = "${joined}SELECT * FROM ( \n"
        var selectUnion = ""
        var lineNo = 1
        for (line in lines) {
            var columnNo = 0
            var comma = ", "
            var select = "SELECT "
            var union = "UNION ALL\n"

            if (lines.size.equals(lineNo)) {
                union = ""
            }

            for (value in line) {
                if (columnNo ==  1) {
                    select = ""
                }
                if (line.size.equals(columnNo+1)) {
                    comma = " FROM DUAL \n$union"
                }
                selectUnion += "$select'$value' as ${header!![columnNo]}$comma"
                columnNo++
            }
            lineNo++
        }

        textSelect += "$selectUnion);"
        querySQL.text = textSelect

    }

Result:

INSERT INTO werwsf  (DATA1,DATA2,DATA3,DATA4,DATA5)
SELECT * FROM ( 
SELECT 'HOLA1' as DATA1, 'HAKA2' as DATA2, 'HAD3' as DATA3, '' as DATA4, 'ASDAD5' as DATA5 FROM DUAL 
UNION ALL
SELECT 'HOLA6' as DATA1, 'HAKA7' as DATA2, 'HAD8' as DATA3, 'FA9' as DATA4, 'ASDAD10' as DATA5 FROM DUAL 
);

Is there a way to improve efficiency? With 1600 rows it takes 5 minutes

Thank you.

Solution

this should be an optimised version of your code:

I used kotlin standard joinToString functions, which uses StringBuilder under the hood like @0009laH advised. I also removed the redundant list <-> array conversions and replaced the splitting and then joining back the first line (header) by the replace function, because it has the same effect as in the original code and it is faster. All these changes should result in faster, more readable and more concise code

fun generatedDelimited(filePath: String, table: String = "") {
    val sourceFile = File(filePath)
    val fileLines: List<String> = sourceFile.readLines()
    val header: String = fileLines.first().replace(';', ',')

    val lines: List<List<String>> = fileLines.drop(1).map { line ->
        line.split(";")
    }

    val selectUnion = lines.joinToString(separator = "UNION ALL\n") { line ->
        line.withIndex().joinToString(separator = ", ", prefix = "SELECT", postfix = " FROM DUAL\n") { (columnNo, value) ->
            "'$value' as ${header[columnNo]}"
        }
    }

    querySQL.text = "INSERT INTO $table  ($header)\nSELECT * FROM ( \n$selectUnion);"
}

Answered By - Allesio Pellicciotta
Answer Checked By - Cary Denson (JavaFixing Admin)