Import CSV to SQLite-Database

[Byte]

hi,

i try to read a csv file (created with a access based app) into a sqlite-database.
the csv file has the fieldnames in the first line and is seperated by TAB.
the lines look somthing like this:

"Name" "Street" "ZIP" "City"

i allready imported the csv file to the database with the sqlitebrowser and it works fine. but since the data output of the other programm changes, i need to import, or better update the database with the created csv file.

the bad part about that is, i have no idea on how to acomplish that.

hope someone can help me on that.

byte

[Byte]

ok i got something figured out.

but i get an error "OnClick, Line 39: attempt to concatenate field '?' (a nil value)


local IsSelected = ComboBox.GetSelected("ComboBox_KatImport");

if (IsSelected == -1) then
Dialog.Message("Fehler", "Es wurden noch keine Kategorie ausgewählt.");
else
--Pfad laden
local sQuery = "SELECT * FROM pfad";
local tbResults = SQLite.QueryToTable(db, sQuery);

local PathMitgldat = tbResults.Data[1]["mitgldat"];

csv_contents = TextFile.ReadToString(PathMitgldat);
new_contents = String.Replace(csv_contents, "\"", "", true);
new_contents = String.Replace(new_contents, String.Char(9), ";", true);
TextFile.WriteFromString(_TempFolder.."\\mitgl.csv", new_contents, false);

-- Import csv file to table
tbImport = TextFile.ReadToTable(_TempFolder.."\\mitgl.csv");
-- Now each line contains a semicolon delimited list of values
if tbImport then --check to make sure that it is actually a table
nCount = Table.Count(tbImport);
-- Progress meter
StatusDlg.Show(MB_ICONNONE, false);
StatusDlg.SetTitle("Importiere Daten");
StatusDlg.SetMeterRange(1, nCount);

--Kategorie wählen
local KatSelected = ComboBox.GetSelected("ComboBox_KatImport");
local SelectedKat = ComboBox.GetItemText("ComboBox_KatImport", KatSelected);

--Use a for loop to cycle through the table
for line, list in tbImport do
StatusDlg.SetMeterPos(line);
StatusDlg.SetMessage("Importiere Datensatz "..line.." von "..nCount);
-- Convert each line to a table one at a time.
tbLine = DelimitedStringToTable(list, ";");
if tbLine then -- again make sure that the table exists
--Insert the data including the Kategory into the address Table.
SQLite.Query(db, 'INSERT INTO adressen (Kategorie, SieAnrede, DuAnrede, Herr_Frau, Titel, Nachname, Vorname, Geschlecht, Strasse, Land, PLZ, Ort, Geburtsdatum, Telefon, Mitgliedsnummer, MobileNr, EMail, StudioNr, StudioName) VALUES ("'..SelectedKat..'", "'..tbLine[1]..'", "'..tbLine[2]..'", "'..tbLine[3]..'", "'..tbLine[4]..'", "'..tbLine[5]..'", "'..tbLine[6]..'", "'..tbLine[7]..'", "'..tbLine[8]..'", "'..tbLine[9]..'", "'..tbLine[10]..'", "'..tbLine[11]..'", "'..tbLine[12]..'", "'..tbLine[13]..'", "'..tbLine[42]..'", "'..tbLine[57]..'", "'..tbLine[58]..'", "'..tbLine[60]..'", "'..tbLine[61]..'")');
end
end
end
end
StatusDlg.Hide();

i used the solution from TJ_Tigger http://www.indigorose.com/forums/showthread.php?t=9832&highlight=csv

this is the code in global functions


function DelimitedStringToTable(DelimitedString, Delimiter)
tbReturn = {};
local strWorking;
local nPos = nil;
local strData;
local nTableIndex = 1;
local nDelimiterLength = String.Length(Delimiter);

if(nDelimiterLength < 1)then
tbReturn[nTableIndex] = DelimitedString;
return tbReturn;
end

strWorking = DelimitedString;
nPos = String.Find(strWorking,Delimiter, 1, false);
while(nPos ~= -1)do
strData = String.Left(strWorking,nPos-1);
tbReturn[nTableIndex] = strData;
nTableIndex = nTableIndex + 1;
local nLength = String.Length(strWorking);
strWorking = String.Right(strWorking,nLength - (nPos + (nDelimiterLength-1)));
nPos = String.Find(strWorking,Delimiter);
end
if(strWorking ~= "")then
tbReturn[nTableIndex] = strWorking;
end

return tbReturn;
end


anyone here, that can help me on that?

byte

[Worm]

A guess is that you have a line in the CSV code that doesn't have the same number of fields, therefore making one or more of your tbLine[??] variables be a null/nil

[Worm]

a solution would be something like this before doing the sql statement


for n=1, 61 do
if tbLine[n] == nil then
tbLine[n] = "";
end
end


Of course this could throw off some of your columnlar data depending on which fields is missing

[Byte]

thanks alot worm, that was it.
works fine now

regards

byte

[Byte]

hi,

i need some help on that again. i want to import a address only if there is a email and mobilenumber stored in the csv-file. i thought that if there is no entry in the csv-file then an empty sting is stored in the tbLine.

that's what i came up with



local DB_MNR = SQLite.QueryToTable(db, "SELECT * FROM adressen");

local UpdateDB = 0;
local Record_Update = 0;
local Record_New = 0;
local Record_Scip = 0;

local IsSelected = ComboBox.GetSelected("ComboBox_KatImport");

if (IsSelected == -1) then
Dialog.Message("Fehler", "Es wurden noch keine Kategorie ausgewählt.");
else
--Pfad laden
local sQuery = "SELECT * FROM pfad";
local tbResults = SQLite.QueryToTable(db, sQuery);

local PathMitgldat = tbResults.Data[1]["mitgldat"];

csv_contents = TextFile.ReadToString(PathMitgldat);
new_contents = String.Replace(csv_contents, "\"", "", true);
new_contents = String.Replace(new_contents, String.Char(9), ";", true);
TextFile.WriteFromString(_TempFolder.."\\mitgl.csv", new_contents, false);

-- Import csv file to table
tbImport = TextFile.ReadToTable(_TempFolder.."\\mitgl.csv");
Table.Remove(tbImport, 1);

-- Now each line contains a semicolon delimited list of values
if tbImport then --check to make sure that it is actually a table
nCount = Table.Count(tbImport);
-- Progress meter
StatusDlg.Show(MB_ICONNONE, false);
StatusDlg.SetTitle("Importiere Daten");
StatusDlg.SetMeterRange(1, nCount);

--Kategorie wählen
local KatSelected = ComboBox.GetSelected("ComboBox_KatImport");
local SelectedKat = ComboBox.GetItemText("ComboBox_KatImport", KatSelected);

--Use a for loop to cycle through the table
for line, list in tbImport do
StatusDlg.SetMeterPos(line);
StatusDlg.SetMessage("Überprüfe Datensatz "..line.." von "..nCount);
-- Convert each line to a table one at a time.
tbLine = DelimitedStringToTable(list, ";");
if tbLine then -- again make sure that the table exists
for n=1, 61 do
if tbLine[n] == nil then
tbLine[n] = "";
end
end

if (tbLine[42] == "") and (tbLine[58] == "") then
Record_Scip = Record_Scip + 1;
else
if DB_MNR.Rows > 0 then
for i=1, DB_MNR.Rows, 1 do
if DB_MNR.Data[i]["Mitgliedsnummer"] == tbLine[42] then
UpdateDB = 1;
break;
end
end
else
--do nothing
end

if UpdateDB == 1 then
--Update the data including the Kategory into the address Table.
SQLite.Query(db, 'UPDATE adressen (Kategorie, SieAnrede, DuAnrede, Herr_Frau, Titel, Nachname, Vorname, Geschlecht, Strasse, Land, PLZ, Ort, Geburtsdatum, Telefon, Mitgliedsnummer, MobileNr, EMail, StudioNr, StudioName) VALUES ("'..SelectedKat..'", "'..tbLine[1]..'", "'..tbLine[2]..'", "'..tbLine[3]..'", "'..tbLine[4]..'", "'..tbLine[5]..'", "'..tbLine[6]..'", "'..tbLine[7]..'", "'..tbLine[8]..'", "'..tbLine[9]..'", "'..tbLine[10]..'", "'..tbLine[11]..'", "'..tbLine[12]..'", "'..tbLine[13]..'", "'..tbLine[42]..'", "'..tbLine[57]..'", "'..tbLine[58]..'", "'..tbLine[60]..'", "'..tbLine[61]..'")');
Record_Update = Record_Update + 1;
UpdateDB = 0;
else
--Insert the data including the Kategory into the address Table.
SQLite.Query(db, 'INSERT INTO adressen (Kategorie, SieAnrede, DuAnrede, Herr_Frau, Titel, Nachname, Vorname, Geschlecht, Strasse, Land, PLZ, Ort, Geburtsdatum, Telefon, Mitgliedsnummer, MobileNr, EMail, StudioNr, StudioName) VALUES ("'..SelectedKat..'", "'..tbLine[1]..'", "'..tbLine[2]..'", "'..tbLine[3]..'", "'..tbLine[4]..'", "'..tbLine[5]..'", "'..tbLine[6]..'", "'..tbLine[7]..'", "'..tbLine[8]..'", "'..tbLine[9]..'", "'..tbLine[10]..'", "'..tbLine[11]..'", "'..tbLine[12]..'", "'..tbLine[13]..'", "'..tbLine[42]..'", "'..tbLine[57]..'", "'..tbLine[58]..'", "'..tbLine[60]..'", "'..tbLine[61]..'")');
Record_New = Record_New + 1;
end
end
end
end
end
end
StatusDlg.Hide();

Dialog.Message("Ergebnis", "Importieren abgeschlossen\r\n\r\n"..Record_Update.." Aktualisierungen\r\n"..Record_New.." Neueinträge\r\n"..Record_Scip.." Nicht importiert\r\n\r\nDatensätze werden nicht importiert,wenn weder eine Email \r\nnoch eine Mobilnummer gespeichert ist.");


but it does not work.
what am i doing wrong her?
any suggestion?

byte

[Byte]

another problem,

the update statement is not working eather. in fact it does nothing.



SQLite.Query(db, 'UPDATE adressen SET Kategorie="'..SelectedKat..'", SieAnrede="'..tbLine[1]..'", DuAnrede="'..tbLine[2]..'", Herr_Frau="'..tbLine[3]..'", Titel="'..tbLine[4]..'", Nachname="'..tbLine[5]..'", Vorname="'..tbLine[6]..'", Geschlecht="'..tbLine[7]..'", Strasse="'..tbLine[8]..'", Land="'..tbLine[9]..'", PLZ="'..tbLine[10]..'", Ort="'..tbLine[11]..'", Geburtsdatum="'..tbLine[12]..'", Telefon="'..tbLine[13]..'", Mitgliedsnummer="'..tbLine[42]..'", MobileNr="'..tbLine[57]..'", EMail="'..tbLine[58]..'", StudioNr="'..tbLine[60]..'", StudioName="'..tbLine[61]..'"');


anyone here that can help me on these two problems???

byte

[azmanar]

another problem,

the update statement is not working eather. in fact it does nothing.



SQLite.Query(db, 'UPDATE adressen SET Kategorie="'..SelectedKat..'", SieAnrede="'..tbLine[1]..'", DuAnrede="'..tbLine[2]..'", Herr_Frau="'..tbLine[3]..'", Titel="'..tbLine[4]..'", Nachname="'..tbLine[5]..'", Vorname="'..tbLine[6]..'", Geschlecht="'..tbLine[7]..'", Strasse="'..tbLine[8]..'", Land="'..tbLine[9]..'", PLZ="'..tbLine[10]..'", Ort="'..tbLine[11]..'", Geburtsdatum="'..tbLine[12]..'", Telefon="'..tbLine[13]..'", Mitgliedsnummer="'..tbLine[42]..'", MobileNr="'..tbLine[57]..'", EMail="'..tbLine[58]..'", StudioNr="'..tbLine[60]..'", StudioName="'..tbLine[61]..'"');


anyone here that can help me on these two problems???

byte

Byte,

Place the single quote ' as a string and keep it within the double quote ".

UPDATE must come with statement WHERE RecordID = ' some integer ' as a unique identifier.

Sometimes to be even more precise, WHERE RID = 'integer' AND FNAME = 'text'.

Or even just , WHERE FNAME = 'text'.

Here's a sample with 2 of your variables:


sQuery =
" UPDATE adressen SET Kategorie='"..SelectedKat.."', SieAnrede='"..tbLine[1].."' WHERE RecordID = 'RID' " ;

SQLite.Query(db,sQuery);



Hope this helps.

[Byte]

thanks alot azmanar,

did not have the where statement. works fine now.

got the first part also fixed myself.

byte

[azmanar]

thanks alot azmanar,

did not have the where statement. works fine now.

got the first part also fixed myself.

byte

Byte,

Good to hear that.