Hi all!

Got a little speed issue here... \:\)


When I run the following script it takes 14.5 minutes to import 18200 lines from a csv file to an Access DB.

If I do the Import from Access it takes less than 5 sec. to do all 18200 lines \:\(


 Code:
break on

$nul = setoption('WrapAtEOL','On')

?? "[1] : " + @time

$K2E_EXEPath = @scriptdir
$lokaldb = $K2E_EXEPath + "\Artiklar.mdb"
$importfile = loadfile(@scriptdir + '\090420.csv')
$i_array = quicksort($importfile,0)

?? "[2] : " + @time

$ConnDSN = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + $lokaldb + ";Uid=;Pwd=;"
$sql = "CREATE TABLE tmptbl (Artikelnummer varchar (255), Benämning varchar (255), Styckpris double, Flag char (1), EAN varchar (255))"
$rs = DBCommand($ConnDSN,$sql)

?? "[3] : " + @time

For each $row in $i_array
  If Not $row = ''
    $items = Split($row,";")

    If not Instr($items[0],'Artikel')
      $aitem = Cstr($items[0])

      $bitem = Cstr($items[1])
      If Instr($bitem,"'")
        $bitem = Join(Split($bitem,"'"),"")
      Endif

      $citem = Join(Split($items[2],' '),'')

      $ditem = Cstr($items[3])

      $values = "'" + $aitem + "','" + $bitem + "','" + $citem + "','" + $ditem + "'"
      $fields = "Artikelnummer,Benämning,Styckpris,EAN"
      $sql = "INSERT INTO tmptbl(" + $fields + ") VALUES(" + $values + ")"
      $rs = DBCommand($ConnDSN,$sql)

      $items = ''

    Endif
  Endif
Next

?? "[4] : " + @time

Get $x



I have looked at the example here, that looks like this:
(would be nice to get the info from the xls, instead of saving it to a csv first)

 Code:
 Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing


But I can't figure out how to get the strSQL-line to work with DBCommand()

Or is there another way to get closer to the 5 sec. result that I get in Access?

/Viggen