• Welcome, Guest. Please login.
 
August 20, 2019, 05:41:15 am

News:

Welcome to the SQLitening support forums!


Import CSV function

Started by cj, May 21, 2015, 04:06:04 pm

Previous topic - Next topic

cj

CSV import as a function.
Column names are created as C1, C2, ... 
Could easily be modified to place column names into sColumnNames$

#INCLUDE "sqlitening.inc"
'
FUNCTION PBMAIN () AS LONG     'CSV2Sqlite.bas
  LOCAL sCSVFile,sDataBaseName,sTableName AS STRING
  LOCAL DropTable,QuoteNumbers, counter AS LONG
  sCSVFile      = "work1.csv"   'input csv file
  sDataBaseName = "sample.db3"  'sqlite database to write to
  sTableName    = "T1"          '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