Importing CSV

[votan]

I need to import data from a CSV file into a SQLite DB. I know there is an importer function in SQLite that allows me to specify a seperator like ",",but this would enter all data with the quotes.
But I want to import the following text (example below) without removing all the quotes first, as I need the data without the quotes. And using "","" as the sepretaor would also not help because of the leading and traling quotes.
Additionally I would also like to prevent the "somestuff" entries from being added to the DB."1358249984","1358254079","somestuff","1004918400","ut","ut1","name"
"1358254080","1358258175","somestuff","1234742400","at","at1","name1"Maybe someone knows a way how to do it in one step? Or mabye there is a way to define a seperator and characters to drop?

Oh.. and another question.... will the importer ignore comment lines automatically (like #this is a comment, etc)? If not, I'll have to preparse the whole file before importing anyway...

[kokenge]

Something like this should work.

x$(1) = """1358249984","1358254079","somestuff","1004918400","ut","ut1","name"""
x$(2) = ""1358254080","1358258175","somestuff","1234742400","at","at1","name1"""

sqliteconnect    #sql,   "yourDb"        ' Connect to the DB
for i = 1 to 2
  a$ = trim$(word(x$(i),1,"#"))
  if a$ <> "" then
    sql$ = insert into tableName (fld1,fld2,fld3,fld3,fld4,fld5,fld6) values (";a$;")"
    #sql execute(sql$)
  end if
next i
end

The dim x$ could be a csv file.

HTH.

[votan]

Ok, so that means "manual parsing".
Thanks!

[kokenge]

Ya! Actually you can do it with some of the SQLite managers. But when you mess with the setup, it's probably easier to just write the code. The code is just to easy IMHO.
Notice fld3 is entered twice.
This eliminates the "somestuff" field..

Have fun..
Dan

[kokenge]

I wanted to point out that there is a SQLite manage program that does what you want.
Using SQLite from the RB projects page allows you to import CSV files.
How it works:
Click on [LoadCsv] next to the table you want to load.
You tell it where the CSV file is located.
It has parameters.
You need to tell it that each field is terminated with a comma (,) and enclosed in quotes (").

Since you want to eliminate a field you will need a header record on your CSV file. You have the option when creating the CSV to request a header, or you can enter one by hand.
You need to tell it that the first row (header) is used for file names. The header field names must have a corresponding field name in the DB.
Example:
If you have 3 field names called a,b, and c, and you want to eliminate field b from your data, your CSV file should look like this.
"a","c","c" # header these are DB field names
"1","2","3" # this is data
"1","2","3" # this is data
Since you want to eliminate field b, you put the data "2" into field "c" as specified by the header. Data "3" is also loaded into field "c", and that basically whips out the "2" that was just loaded. It eliminates it.

Hope this helps..

[metro]

Can sonebody shed some light on why this doesn't work

x$(1) = """1358249984","1358254079","somestuff","1004918400","ut","ut1","name"""
x$(2) = ""1358254080","1358258175","somestuff","1234742400","at","at1","name1"""

sqliteconnect #mem, ":memory:"

mem$ = "CREATE TABLE doc(fld1 integer, fld2 integer, fld3 text,fld4 integer ,fld5 text,fld6 text,fld7 text)"
#mem execute(mem$)

for i = 1 to 2
 a$ = trim$(word(x$(i),1,"#"))
 if a$ <> "" then
   mem$ = insert into doc (fld1,fld2,fld3,fld3,fld4,fld5,fld6,fld7) values (";a$;")"
#mem execute(mem$)
 end if
next i
'end


mem$ = "SELECT * FROM doc"
#mem execute(mem$)

WHILE  #mem hasanswer()
   
html docData$
WEND

thanks in advance

Laurie

[kokenge]

Hmmm!
I tried your code and made a couple of simple changes, but other than that it worked great...

x$(1) = """1358249984"",""1358254079"",""somestuff"",""1004918400"",""ut"",""ut1"",""name"""
x$(2) = """1358254080"",""1358258175"",""somestuff"",""1234742400"",""at"",""at1"",""name1"""

sqliteconnect #mem, ":memory:"

mem$    = "CREATE TABLE doc(fld1 integer, fld2 integer, fld3 text,fld4 integer ,fld5 text,fld6 text,fld7 text)"
#mem execute(mem$)

for i = 1 to 2
a$ = x$(i)
if a$ <> "" then
mem$ = "insert into doc values (";a$;")"
#mem execute(mem$)
end if
next i

mem$    = "SELECT * FROM doc"
#mem 	execute(mem$)
html "<TABLE border=1>"
WHILE #mem hasanswer()
     #row = #mem #nextrow()
    fld1 	= #row fld1()
    fld2 	= #row fld2()
    fld3$	= #row fld3$()
    fld4 	= #row fld4()
    fld5$	= #row fld5$()
    fld6$	= #row fld6$()
    fld7$ 	= #row fld7$()

html "<TR>"
html "<TD>";fld1;"</TD>"
html "<TD>";fld2;"</TD>"
html "<TD>";fld3$;"</TD>"
html "<TD>";fld4;"</TD>"
html "<TD>";fld5$;"</TD>"
html "<TD>";fld6$;"</TD>"
html "<TD>";fld7$;"</TD>"
html "</TR>"
WEND
html "</table>"
end

[metro]

Thanks Dan

actually it's your code and those couple of little changes you made I couldn't find.
so I see double quotes around each data item
and also this caused problems
a$ = trim$(word(x$(i),1,"#"))

so no need to list names of fields if all are to get data
' mem$ = insert into doc (fld1,fld2,fld3,fld3,fld4,fld5,fld6,fld7) values (";a$;")"
for i = 1 to 2
a$ = x$(i)
if a$ <> "" then
mem$ = "insert into doc values (";a$;")"
#mem execute(mem$)
end if
next i

thanks for taking the time


regards

Laurie

[kokenge]

Actually any of the following formats below are valid.
The double quotes are needed because double quotes within quotes becomes a single quote. So reading CSV data from a file would only need the single quote.

SQLite only requires alphanumeric data to be in quotes. Numbers don't require quotes.

Actually you are better off not placing quotes around numeric data. Once quoted, SQLite treats it as alpha. Therefore if you quoted '1', '2', and '10' and sequenced the file it would be in the following sequence when sorted as alpha. 1,10,2.

Basically SQLite treats the formats such as integer, text, and character as a suggestion. You can put alpha in integer fields, and numeric in text fields. Place quotes around a numeric field, even if you place it in an integer field and SQLite will look at it as alpha during sorts.


On Inserts you do not need to list the fields if the data you are inserting has the exact number of fields and they are in the same sequence.

Also some CSV files are exported with a <carriage return> and a <line feed> (CrLf) instead of a single <carriage return> (Cr). This will sometimes look like you are getting a extra line when printing the data. Because CR and LF are both treated as a CR.
So be careful when reading CSV files cause you may get a blank line.
Best to do a replace of chr$(10) to chr$(13), Then replace chr$(13) + chr$(13) to chr$(13)

x$(1) = """1358249984"",""1358254079"",""somestuff"",""1004918400"",""ut"",""ut1"",""name"""
x$(2) = """1358254080"",""1358258175"",""somestuff"",""1234742400"",""at"",""at1"",""name1"""
x$(3) = "'1358249984','1358254079','somestuff','1004918400','ut','ut1','name'"
x$(4) = "'1358254080','1358258175','somestuff','1234742400','at','at1','name1'"
x$(5) = "1358249984,1358254079,'somestuff',1004918400,'ut','ut1','name'"
x$(6) = "1358254080,1358258175,'somestuff',1234742400,'at','at1','name1'"

sqliteconnect #mem, ":memory:"

mem$    = "CREATE TABLE doc(fld1 integer, fld2 integer, fld3 text,fld4 integer ,fld5 text,fld6 text,fld7 text)"
#mem execute(mem$)

for i = 1 to 6
#mem execute("insert into doc values (";x$(i);")")
next i

Hope this helps a little.
Dan

[metro]

thanks Dan

extremely helpful

I appreciate you, Stefan and others taking the time to respond to questions that may appear trivial to most.

sometimes light bulb moments initiated by all you guru's makes it far more clear
all too often I spend hours reading or stareing at pages of code and just can't see the answer one small detail and it all falls into place.

thanks for taking the time

regards

Laurie