how to split CSV data?

[pr-apes]

@pgundlach,

imagine I have this minimal CSV file:

"Mary","Literature"
,"Chemistry"
,"French"
,
,
"Anne","Physics"
,"Math"
,
,
"John","German"
,"Greek"
,"Environmental Sciences"
"Jane","Chemistry"
,"French"
,"Physics"
,"Math"
,"German"

I need use your csv2xml.lua filter, but I dont know how to split in individual files:

csv = require("csv")
xml = require("xml")

csvtab, msg = csv.decode("csv-file.csv")
if not csvtab then
    print(msg)
    os.exit(-1)
end

csvtab._name = "data"

for i=first_line,#csvtab do
	csvtab[i]._name = "row_" .. tonumber(i)
	for j=1,#csvtab[i] do
		local val = csvtab[i][j]
		csvtab[i][j] = { _name = "cell_" .. tonumber(j), val }
	end
end

ok, err = xml.encode_table(csvtab)
if not ok then
    os.exit(-1)
end

The requirements of the above CSV data for data split are:

1. Ignore rows with no contents (with all csvtab[i][loop(1-#csvtab[i])] == "" [excuse the crappy pseudocode]).
2. Each file should got from a line when csvtab[i][1] ~= "" to the next line with the same condition (or a totally empty file).

How could I achieve that?

BTW, I would say that in previous versions had not required tonumber() in csvtab[i]._name = "row_" .. tonumber(i).

Many thanks for your help.

[pgundlach]

Not sure if I understand it correctly and I am not sure if this is really a speedata Publisher issue, but you can try something like this:

csv = require("csv")
xml = require("xml")

csvtab, msg = csv.decode("data.csv")
if not csvtab then
    print(msg)
    os.exit(-1)
end

dofile("debug.lua")

local datatab

local datacounter = 0

for i=1,#csvtab do
    local has_value = false
    local val = {}
	for j=1,#csvtab[i] do
		val[#val+1] = {_name = "cell", csvtab[i][j]}
        if csvtab[i][j] ~= "" then has_value = true end
	end

    if not has_value or i == #csvtab then
        if datatab then
            datacounter = datacounter + 1
            ok, err = xml.encode_table(datatab,string.format("data%d.xml",datacounter))
            if not ok then
                os.exit(-1)
            end
        end
        datatab = nil
    else
        datatab = datatab or { _name = "data" }
        datatab[i] = { _name = "row" }
        for v = 1, #val do
            datatab[i][#datatab[i]+1] = val[v]
        end
    end
end


os.exit(0)

to split the CSV table.

[pr-apes]

Many thanks for your help, @pgundlach.

Right now, I cannot test your filter, but many thanks for it.

[pr-apes]

@pgundlach.

besides my own use, I would like to offer an example of single-document generation from CSV data with Publisher.

The filter works fine, but I'm afraid I haven't expressed myself accurately enough.

From such a CSV file:

"Mary","Literature"
,"Chemistry"
,
,
"Anne","Physics"
,"Math"
,
,
"John","German"
,"Greek"
"Jane","Chemistry"
,"French"

I need single XML files for Mary, Anne, John and Jane.

I mean, when cell from column A in LibreOffice Calc is not empty, a new XML file should be generated.

I have tried to modify your code (with a start_new boolean), but I must admit I don't know how to make it work:

csv = require("csv")
xml = require("xml")

csvtab, msg = csv.decode("c.csv")
if not csvtab then
    print(msg)
    os.exit(-1)
end

-- dofile("debug.lua")

local datatab

local datacounter = 0

for i=1,#csvtab do
    local has_value = false
	local start_file = false
    local val = {}
	for j=1,#csvtab[i] do
		val[#val+1] = {_name = "cell", csvtab[i][j]}
        if csvtab[i][j] ~= "" then has_value = true end
        if j == 1 and csvtab[i][j] ~= "" then start_new = true end
	end

    if not has_value or i == #csvtab then
        if datatab and start_new then
            datacounter = datacounter + 1
            ok, err = xml.encode_table(datatab,string.format("data%d.xml",datacounter))
            if not ok then
                os.exit(-1)
            end
        end
        datatab = nil
    else
        datatab = datatab or { _name = "data" }
        datatab[i] = { _name = "row" }
        for v = 1, #val do
            datatab[i][#datatab[i]+1] = val[v]
        end
    end
end

os.exit(0)

I don't know where to include start_new to generate a new CSV file.

BTW, dofile("debug.lua") cannot find the file. I have no idea how to avoid this.

Many thanks for your help.

[pr-apes]

@pgundlach,

I'm afraid your original filter from your first reply to this message is not able to export the last line.

With the CSV data from my original report, Math is the last cell imported (but it should be German).

Sorry, but I don't know why the last line of the CSV file isn't read with the filter.

Many thanks for your help.