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/sho...&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.