• Welcome, Guest. Please login.
 
September 16, 2019, 09:38:00 am

News:

Welcome to the SQLitening support forums!


Importing CSV File To SQLite

Started by Fredrick Ughimi, September 24, 2018, 11:44:27 am

Previous topic - Next topic

Fredrick Ughimi

Hello CJ,

I tried importing csv file to sqlite using your code here:

https://www.sqlitening.planetsquires.com/index.php?topic=9362.msg24699#msg24699

I got Incorrect Function err.


#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
'
FUNCTION PBMAIN () AS LONG     'CSV2Sqlite.bas
  LOCAL sCSVFile,sDataBaseName,sTableName AS STRING
  LOCAL DropTable,QuoteNumbers, counter AS LONG
  sCSVFile      = "DrugsSetup.csv"   'input csv file
  sDataBaseName = "HospitalProDB.db3"  'sqlite database to write to
  sTableName    = "tblDrugsSetup"          'table to write to
  DropTable     = 1             '0=append, 1= start fresh
  QuoteNumbers  = 1             'enclose all columns with $SQ
  counter = Csv2Sqlite _
    (sCsvFile,sDatabaseName,sTableName,DropTable,QuoteNumbers)
  ? USING$("Records in table #,",counter),,"Csv2Sqlite"
END FUNCTION
'
FUNCTION Csv2SQLite ( _
  sCSVFILE AS STRING, _
  sDataBaseName AS STRING , _
  sTableName    AS STRING,  _
  DropTable     AS LONG,    _
  QuoteNumbers  AS LONG) AS LONG

  'Create table with sColumnNames$ = "C1,C2, ..."

  LOCAL x, hFile,cols AS LONG
  LOCAL sInputLine,sColumnNames,sOutputLine,s,sInsert AS STRING
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  LINE INPUT #hFile, sInputLine 'read first line
  cols = PARSECOUNT(sInputLine) 'number of columns
  CLOSE #hFile
  'first line could be column names, using C1,C2, ..."
  FOR x = 1 TO cols
    sColumnNames = sColumnNames + "C" + FORMAT$(x)+ ","
  NEXT
  sColumnNames = LEFT$(sColumnNames,-1) 'your column names
  slOpen sDataBaseName,"C"
  IF DropTable THEN slexe "Drop table if exists " + sTableName
  slexe "Create Table if not exists " + sTableName + "(" + sColumnNames + ")"
  slexe "BEGIN EXCLUSIVE"
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  DO UNTIL EOF(#hFile) 'process line
    LINE INPUT #hFile, sInputLine
    RESET sOutputLine
    FOR x = 1 TO cols
      s = PARSE$(sInputLine,x)
        IF QuoteNumbers THEN        'enclose all columns in $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      ELSEIF IsNumeric(s) = 0 THEN 'only enclose strings with $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      END IF
        sOutputLine = sOutPutLine +  s + ","
    NEXT
    sOutPutLine = LEFT$(sOutputLine,-1)
    sInsert = "Insert into " + sTableName + " values (" + sOutputline + ")"
    REM ? sOutputLine:end 'look at first output record and end
    slExe sInsert
  LOOP
  slEXE "END"
  LOCAL sArray() AS STRING 'return number of inserts
  slSelAry "select count(*) from " + sTableName,sArray(),"Q9c"
  FUNCTION = VAL(JOIN$(sArray(),$CRLF))
END FUNCTION
'
FUNCTION isNumeric(Answer AS STRING) AS LONG
  Answer = TRIM$(Answer) 'may be required if right-justified text
  IF (LEN(Answer) = 0)                  OR _
     (VERIFY (Answer , ".-0123456789")) OR _
     (RIGHT$(Answer,1) = ".")           OR _
     (INSTR(-1,Answer,"-") > 1)         OR _
     (TALLY(Answer,".") > 1) THEN
     EXIT FUNCTION  'exit, return 0 not-numeric
  END IF
  FUNCTION = -1 'numeric
END FUNCTION



Could find such error in the help file.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Please try running this.
Can't test your CSV file.

#INCLUDE "sqlitening.inc"
'
FUNCTION PBMAIN () AS LONG     'CSV2Sqlite.bas
  LOCAL sCSVFile,sDataBaseName,sTableName AS STRING
  LOCAL DropTable,QuoteNumbers, counter AS LONG

  sDataBaseName = "junk.db3"  'sqlite database to write to
  sTableName    = "table1"          'table to write to
  DropTable     = 1             '0=append, 1= start fresh
  QuoteNumbers  = 1             'enclose all columns with $SQ
  sCSVFile      = "CSV.csv"   'input csv file
 
  'create csv file
  OPEN sCSVFile FOR OUTPUT AS #1
  LOCAL s AS STRING
  FOR counter = 1 TO 10
   s = FORMAT$(counter)
   WRITE #1, s,"now","brown","cow"
  NEXT
  CLOSE #1


  counter = Csv2Sqlite _
    (sCsvFile,sDatabaseName,sTableName,DropTable,QuoteNumbers)
  ? USING$("Records in table #,",counter),,"Csv2Sqlite"

  DIM sArray() AS STRING
  slOpen "junk.db3"
  slSelAry "select * from table1",sArray(),"Q9"
  ? JOIN$(sArray(),$CR)
END FUNCTION
'
FUNCTION Csv2SQLite ( _
  sCSVFILE AS STRING, _
  sDataBaseName AS STRING , _
  sTableName    AS STRING,  _
  DropTable     AS LONG,    _
  QuoteNumbers  AS LONG) AS LONG

  'Create table with sColumnNames$ = "C1,C2, ..."

  LOCAL x, hFile,cols AS LONG
  LOCAL sInputLine,sColumnNames,sOutputLine,s,sInsert AS STRING
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  LINE INPUT #hFile, sInputLine 'read first line
  cols = PARSECOUNT(sInputLine) 'number of columns
  CLOSE #hFile
  'first line could be column names, using C1,C2, ..."
  FOR x = 1 TO cols
    sColumnNames = sColumnNames + "C" + FORMAT$(x)+ ","
  NEXT
  sColumnNames = LEFT$(sColumnNames,-1) 'your column names
  slOpen sDataBaseName,"C"
  IF DropTable THEN slexe "Drop table if exists " + sTableName
  slexe "Create Table if not exists " + sTableName + "(" + sColumnNames + ")"
  slexe "BEGIN EXCLUSIVE"
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  DO UNTIL EOF(#hFile) 'process line
    LINE INPUT #hFile, sInputLine
    RESET sOutputLine
    FOR x = 1 TO cols
      s = PARSE$(sInputLine,x)
        IF QuoteNumbers THEN        'enclose all columns in $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      ELSEIF IsNumeric(s) = 0 THEN 'only enclose strings with $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      END IF
        sOutputLine = sOutPutLine +  s + ","
    NEXT
    sOutPutLine = LEFT$(sOutputLine,-1)
    sInsert = "Insert into " + sTableName + " values (" + sOutputline + ")"
    REM ? sOutputLine:end 'look at first output record and end
    slExe sInsert
  LOOP
  slEXE "END"
  LOCAL sArray() AS STRING 'return number of inserts
  slSelAry "select count(*) from " + sTableName,sArray(),"Q9c"
  FUNCTION = VAL(JOIN$(sArray(),$CRLF))
END FUNCTION
'
FUNCTION isNumeric(Answer AS STRING) AS LONG
  Answer = TRIM$(Answer) 'may be required if right-justified text
  IF (LEN(Answer) = 0)                  OR _
     (VERIFY (Answer , ".-0123456789")) OR _
     (RIGHT$(Answer,1) = ".")           OR _
     (INSTR(-1,Answer,"-") > 1)         OR _
     (TALLY(Answer,".") > 1) THEN
     EXIT FUNCTION  'exit, return 0 not-numeric
  END IF
  FUNCTION = -1 'numeric
END FUNCTION

Fredrick Ughimi

Same error message. It also deletes all my four columns and create a column c1.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

I ran the code again with a different IDE and I got error 75. The db, CSV and application are in the same folder.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 25, 2018, 12:45:05 pm #4 Last Edit: September 25, 2018, 12:46:45 pm by cj
No error here and no idea what an IDE has to do with this?
Please be sure you are starting with a fresh, empty database.

Fredrick Ughimi

Quote
No error here and no idea what an IDE has to do with this?


I noticed that sometimes I get strange error from PBIDE, but when I compile with jk-ide everything works fine. In this situation using PBIDE I get incorrect function error, but with jk-ide I get Path/file access error error 75.

Quote
Please be sure you are starting with a fresh, empty database.


Oh! My database is not empty. It has other tables with records, except  the table I am importing to. Will give it another try.




Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

QuoteOh! My database is not empty. It has other tables with records, except  the table I am importing to. Will give it another try.

If the table you are importing to doesn't have the same format it should fail.

Fredrick Ughimi

Its same format and column names. It works now after using an empty db and table. Thanks Mike.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet