Sign in to follow this  
Followers 0
vortex91

How to create table in sqlite with changing number of colums

4 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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.

#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 by kylomas
1 person likes this

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0