How to create table in sqlite with changing number of colums

[vortex91]

I am dealing with many excel files which i want to port into csv

i know how to do this for each file if i know the number of columns to create.  But is there a way to do so without knowing? 

for example instead of  _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (test, test2, test3, test4);", $sOutputFile)

can i do _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test ($aArray1);", $sOutputFile)

 

thank in advance

[kylomas]

vortex91,

You can use a technique similar to the following to build the SQL statement.

;

local $array[random(1,10,1)]

ConsoleWrite('Creating table with ' & ubound($array) & ' columns' & @LF)

local $sqlstr = 'create table [test] ('
for $1 = 0 to ubound($array) - 1
    $sqlstr &= 'Col' & stringformat('%03i',$1+1) & ','
Next
$sqlstr = stringtrimright($sqlstr,1)
$sqlstr &= ');'

ConsoleWrite($sqlstr & @LF)

Then use the following function to execute the SQL

_SQLite_Exec (-1, $sqlstr)

kylomas

edit: additional info

_SQLite_SQLiteExe creates an instance of SQLite, opens whatever DB you specify, executes your SQL then shuts down.  If you are doing a one time thing you might do it this way. 

Presumably you will be doing more with the DB after creating it.  The following is a working example of the code above.

expandcollapse popup

#include <sqlite.au3>

;----------------------------------------------------------------------------------
;
;  SQLite startup section
;
;----------------------------------------------------------------------------------

_SQLite_Startup()
if @error then
    ConsoleWrite('SQLite cannot be loaded' & @LF)
    Exit
endif

OnAutoItExitRegister("_SQLite_ShutDown")

local $db  = @scriptdir & '\test.db3'

local $hDB = _sqlite_open($db)
if @error then
    ConsoleWrite('Cannot open DB = ' & $db & @LF)
    Exit
endif

OnAutoItExitRegister("_DbClose")

;----------------------------------------------------------------------------------
;
;  format SQL and create table
;
;----------------------------------------------------------------------------------

_sqlite_exec(-1,'drop table if exists [test];')

local $array[random(1,10,1)]
ConsoleWrite('Creating table with ' & ubound($array) & ' columns' & @LF)

local $sqlstr = 'create table [test] ('
for $1 = 0 to ubound($array) - 1
    $sqlstr &= 'Col' & stringformat('%03i',$1+1) & ','
Next
$sqlstr = stringtrimright($sqlstr,1)
$sqlstr &= ');'

ConsoleWrite($sqlstr & @LF)

if _sqlite_exec($hDB,$sqlstr) <> $sqlite_ok then
    ConsoleWrite('Table create failed' & @LF)
    Exit
EndIf

;----------------------------------------------------------------------------------
;
;  display the table definition
;
;----------------------------------------------------------------------------------

local $arows, $icols, $irows, $str = ''
_SQLite_GetTable2d(-1,'PRAGMA table_info(test);',$arows, $irows, $icols)

_arraydisplay($arows)


Func _DbClose()
    _SQLite_Close($hDB)
EndFunc   ;==>_DbClose

Edited August 1, 2013 by kylomas

[vortex91]

Thanks a bunch.  This works great. But what if i actualy wanted the names for columns to be the values from the array?

[vortex91]

ConsoleWrite('Creating table with ' & ubound($array) & ' columns' & @LF)

 

local $sqlstr = 'create table [test] ('

for $1 = 0 to ubound($array) - 1

    $sqlstr &=  ($array[$1]) & ','

Next

$sqlstr = stringtrimright($sqlstr,1)

$sqlstr &= ');'

 

ConsoleWrite($sqlstr & @LF)

 

this worked

 

thank you