INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Convertting a text CSV string to a Msaccess table.

Convertting a text CSV string to a Msaccess table.

(OP)
I notice there are many examples around of using vb6 to fill a MsAccess table with data from a FILE containing CSV data.

My problem is I have the CSV data in a single string, having received it from an internet site using WinHTTP.
I want to use this text to create a table so I can use SQL to do various queries.

I could do this 2 cludgy ways by labouriously seperating each field and writing it to a table using recordsets OR save text to a file and immediately use the above method to create the table.

Is there a way of using a similar method as the CSV file to table but in effect using CSV string to table instead?

I haven't found one yet but maybe I am looking in the wrong place?

RE: Convertting a text CSV string to a Msaccess table.

If you open MSAccess where your table resides, can’t you - in Access – select something like: Import – Data – From text file – Format CSV ?

No VB6 needed?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Convertting a text CSV string to a Msaccess table.

Ted's point is that he know's how to import from a text file, but wants to avoid the step of creating such a file from the data he has downloaded, which is currently in a string.

Not sure how feasible that is, though.

RE: Convertting a text CSV string to a Msaccess table.

OK, I see. So I take it you have a file (MyFile.csv) with CSV (comma separated values) text in it. And no table in Access to put this data.

My guess is, your (MyFile.csv) file looks like:
123,ABCD,1/1/2015,100.65,Bob Brown


So you want to create table called MyFile with 5 fields: Number, Text, Date, Number, and Text. If so, what would be the names for those Fields in MyFile table?

Or am I completely way off…?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Convertting a text CSV string to a Msaccess table.

Just to reiterate: he doesn't have a CSV file. He has a single string containing CSV data, pulled down from a URL.

RE: Convertting a text CSV string to a Msaccess table.

We don't even know whether the String has data for one row or multiple rows, but to cover both bases about the only thing I can imagine might be:

CODE

Private Sub AddNewRow(ByVal CsvLine As String)
    'Let ADO coerce from String to other types where necessary:
    RS.AddNew FieldNames, VSplit(CsvLine, ",")
End Sub

Private Sub AddNewRows(ByVal CsvLines As String)
    Dim Lines() As String
    Dim I As Long
    
    Lines = Split(CsvLines, vbNewLine)
    For I = 0 To UBound(Lines)
        If Len(Lines(I)) > 0 Then AddNewRow Lines(I)
    Next
End Sub 

You can't use Split so you need to write a Variant Split. Whole demo:

CODE

Option Explicit

Private Const CONNSTRING As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$MDB$'"

Private DbPath As String
Private CN As ADODB.Connection
Private RS As ADODB.Recordset
Private FieldNames As Variant

Private Sub OpenDb()
    If Len(Dir$(DbPath, vbNormal)) > 0 Then
        Set CN = New ADODB.Connection
        CN.Open Replace$(CONNSTRING, "$MDB$", DbPath)
    Else
        With CreateObject("ADOX.Catalog")
            .Create Replace$(CONNSTRING, "$MDB$", DbPath)
            Set CN = .ActiveConnection
        End With
        CN.Execute "CREATE TABLE SOMETABLE(" _
                 & "ID IDENTITY CONSTRAINT PK_UID PRIMARY KEY," _
                 & "NAME TEXT(10) WITH COMPRESSION NOT NULL," _
                 & "LAT DECIMAL(11,8) NOT NULL," _
                 & "LON DECIMAL(11,8) NOT NULL)", _
                   , _
                   adCmdText Or adExecuteNoRecords
    End If
    'Hard-code or could fetch looping over ADOX Columns of Table:
    FieldNames = Array("NAME", "LAT", "LON")
    Set RS = New ADODB.Recordset
    With RS
        .CursorLocation = adUseServer
        .Open "[SOMETABLE]", CN, adOpenForwardOnly, adLockOptimistic, adCmdTable
    End With
End Sub

Private Sub CloseDb()
    RS.Close
    CN.Close
End Sub

Private Function VSplit(ByVal Text As String, ByVal Delim As String) As Variant
    'Like Split, but returns a Variant containing an array of Variants.
    Dim Pos As Long
    Dim NextPos As Long
    Dim I As Long
    Dim ReturnValue As Variant

    Pos = 1
    Do
        Pos = InStr(Pos, Text, Delim)
        If Pos = 0 Then Exit Do
        I = I + 1
        Pos = Pos + Len(Delim)
    Loop
    ReDim ReturnValue(I)
    Pos = 1
    I = 0
    Do
        NextPos = InStr(Pos, Text, Delim)
        If NextPos = 0 Then Exit Do
        ReturnValue(I) = Trim$(Mid$(Text, Pos, NextPos - Pos))
        I = I + 1
        Pos = NextPos + Len(Delim)
    Loop
    ReturnValue(I) = Trim$(Mid$(Text, Pos))
    VSplit = ReturnValue
End Function

Private Sub AddNewRow(ByVal CsvLine As String)
    'Let ADO coerce from String to other types where necessary:
    RS.AddNew FieldNames, VSplit(CsvLine, ",")
End Sub

Private Sub AddNewRows(ByVal CsvLines As String)
    Dim Lines() As String
    Dim I As Long
    
    Lines = Split(CsvLines, vbNewLine)
    For I = 0 To UBound(Lines)
        If Len(Lines(I)) > 0 Then AddNewRow Lines(I)
    Next
End Sub

Private Sub Main()
    DbPath = App.Path & "\demo.mdb"
    OpenDb
    AddNewRows "ABC, 48.10028, 86.0234" & vbNewLine _
             & "LMNOP, 48.10116, 86.010066" & vbNewLine _
             & "XYZ, 48.1018, 85.99081" & vbNewLine
    CloseDb
End Sub 

This will of course fall over if there is badly formed data or String field values within it are quoted.

RE: Convertting a text CSV string to a Msaccess table.

So it looks like to you do know (or hope to get) the string like:
Boston,42.4565,93.5678
Huston,43.456,92.654

And you want to set up a table called SOMETABLE with fields: ID, NAME (that’s a terrible name for a field, it is a reserved word), LAT, and LON

How come all of this very important information was a secret so far?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Convertting a text CSV string to a Msaccess table.

Maybe raise your screen DPI settings, it seems you can't make out the user names on posts. wink

RE: Convertting a text CSV string to a Msaccess table.

Dilettante, are you saying I should read some/all other posts of tedsmith’s in order to understand this particular problem stated here at the top in OP?
I may be wrong, but I’ve always thought each individual tread is an ‘independent’ question, unless there is/are links to other treads/posts/discussion groups where part of the issue is/were discussed or partly resolved. So that’s why I ask question(s) that may be obvious to other ‘mind-readers’ smile (who read other tedsmith’s posts) but elude my perception.ponder

Actually one of my co-worker wanted me to attend a ‘mind reading’ class to help me with my work. Unfortunately class like that is not available around here. sad

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
WOW so many assumptions!

My question is clearly a stand alone question and requires no knowledge of my previous threads.
Admittedly it is related to the same project that I have recently asked a few question about but those problems are solved.
I already have the CSV string and this new question is how to handle the data string I received in the previous solved thread.
That's why I followed the rules and started a new thread so there is no need to attend a mind reading class.

Maybe the confusion is because of misinterpretation of what I call a FILE.

To clarify:-
By FILE I mean data on a hard or removable disk.(EG MyData.csv - this is what I haven't got and don't want to make)
By SINGLE STRING I mean a the same data currently as a string in memory.( Eg MyData$ - this is what I have)
By TABLE I mean a table in a MSAccess database. This can be created or replaced in an existing table or recreated each time it is updated.
What I want is for the CSV string data to end up automatically, nice and quickly in the table.

Example of format of CSV STRING Data I have :-

ID,Name,Mental_Capacity
1, Andrzejek, very clever
2, Ted, senile
3, strongm, brilliant

The methods of getting each field for the CSV and rewriting to the table in turn is much too slow. There could be over 1000 rows of 100 bytes in 8 fields each in this data.
I am experimenting with SQL CREATE TABLE (Columns,,,),(Values,,,) which looks promising but I am getting errors. I still have to Split the data to separate the rows then Split each row to get the fields so it looks as though it could be slow with a large file.

All the examples I have found that use INSERT INTO are for updating a table from a FILE in one step.

I wondered if there was a way (maybe like using GDI+ like strongm did to replace a Image1.image = loadfile()?) of updating the data bytes received directly to the table instead of from the file?
Or maybe there is a different SQL word for string use?
I could use the published file method but it seems counter productive (cludgy) to have to create a temporary file then read it back to the table.

RE: Convertting a text CSV string to a Msaccess table.

Then the solution from dilettante is what you need - yes it may be slow for big strings, but as you don't wish to create a temp file there's not much more you can do in Access (you could do more in SQL Server/Oracle but this isn't it)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Convertting a text CSV string to a Msaccess table.

It might possibly be made a little faster by either using a client cursor and batch updating, or by wrapping multiple updates within a transaction, but the firehose cursor used above is pretty fast too. Probably fast enough that it doesn't matter unless there are a great many new rows to add.

But there is no opposite of GetString (PutString?) in ADO, and the Jet Text IISAM is completely file based.

Ideally your source would send you a persisted disconnected batch-mode Recordset containing the new rows instead of a CSV string. The binary ADTG format is more compact than the later XML persistence format. But CSV was never supported.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Thanks
Yes it was something like using a stream I was thinking about but I wonder how I would go about that?
Any clues as to converting the CSV to a stream?
I can receive the data as binary. Would writing this to a stream work?
Then how would I write the stream to the table?

RE: Convertting a text CSV string to a Msaccess table.

ADO Streams cannot use CSV as a persistence format as already mentioned.

RE: Convertting a text CSV string to a Msaccess table.

Oops, I meant Recordsets.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Well the data can be received in binary format when received by the WinHttp. by using .responsebody
There is also a known method of creating and writing to a table from csv FILE.

The Loadpicture function loads an Image control from a file and it is also possible to show a picture from a byte array so it seems to me that there must be a way of writing to a table from data using a stream method - or is that too simplistic?

Admittedly it used Endamo's 'stream' methods.

Unfortunately there is little or nothing published that relates to using this in vb6.
Perhaps strongm could comment as to whether this is possible?

RE: Convertting a text CSV string to a Msaccess table.

I've already commented. I said that I was "[n]ot sure how feasible that is, though.". Others have elucidated since then.

ADO does not have a method of directly importing a string containing CSV 'formatted' data directly into a table.

As already mentioned, it does have a method of importing a table from a stream, as long as the stream contains XML that conforms to the XML-Data schema, or the stream contains Microsoft's proprietary ADTG binary format (which represents a persisted ADO recordset).

So an indirect route might be to locate (or write) an XLST transform that could convert your CSV into XML-Data, and then load that into the stream. But that means learning some new technologies, and probably is no faster than a solution similar to the one dilettante has already proposed.



RE: Convertting a text CSV string to a Msaccess table.

I'm not sure this is really a question of speed anyway. I got the strong impression the issue was closer to "How can I import CSV data to a Jet MDB table from a String using the fewest lines of code?"

Obviously nothing horribly slow would be desired, but the pinnacle of performance probably isn't required here.


If I had to build this sort of thing I'd probably poll the remote data collectors via HTTP using a WinHttpRequest talking to some "HttpResponder" object in the remote servers. Since the server code is probably VB6 too it could simply return ADTG-format disconnected Recordsets populated with the data point rows. Yes, I realize this terminology reverses the client and server roles from the layman's view but that's really how it is with HTTP (at least until you start getting into things like Web Sockets).

You could return XML but that means a little bit more work. If you Save a Recordset to a Stream in XML it always writes UTF-16LE as far as I can tell so you'd have to convert that to UTF-8 for compactness before sending it. Even so the XML will be a far larger payload than the ADTG equivalent.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
You're right, speed might not be a factor. I suppose I was looking for the most 'elegant' way.
I had nothing to do with the web server and can't change it. It is just plain csv text and can be loadable straight into an EXCEL spreadsheet.
The reason I want to get it to an access table is so I can sort and search data quickly and easily using SQL type statements and use join queries with fixed tables (Eg. to get a customer address not in the csv for a customer ID in the csv.)

It is updated every 10 seconds and I need to sort out data, compare with a fixed table and send the appropriate different data results in turn every 20 seconds to 69 workstations connected on a LAN.

To reduce the number of sending winsocks, I open one, 500ms later send then 500ms later close it for reuse 500ms later. I only need 5 winsocks open at any time as a result.
I am experimenting further when I get it all going. One second extra making the table might not be a problem.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
After some experimentation in areas with which I am familiar I came up with two minimum code solutions that probably will be fast enough and with extra error trapping cover possible intermittent errors in the original incoming CSV String data.

The first one is similar to dilettante's example but faster using INSERT INTO to write each row.
They both split the string to form array rows then insert single quotes around each row field to write the row.
With a string of 92629 characters of 5700 rows of 8 columns the first method takes 2.2 seconds, the second 1.1 seconds which I can probably live with.

CODE -->

Sub MakeGPSTable(MyGPSData As String)
'make table from CSV string using ADODB
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet As New ADODB.Recordset
Dim RowArray() As String
Dim RowCounter As Integer
Dim FieldString As String
T = Timer
On Error GoTo MGTXError
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & MyDrive & ":\DataCollectionServer\DataCollectionServerSettings.mdb"
MyConn.Open
MyConn.Execute ("DELETE FROM GPSData")
RowArray = Split(MyGPSData, vbCr)
For RowCounter = 1 To UBound(RowArray) - 1
    FieldString = Chr(39) & Replace(RowArray(RowCounter), Chr(44), (Chr(39) & Chr(44) & Chr(39))) & Chr(39)
    Set MyRecSet = MyConn.Execute("INSERT INTO GPSData VALUES(" & FieldString & ")")
Next
MyConn.Close
Debug.Print Timer - T '(2.2 secs)
On Error GoTo 0
Exit Sub

MGTXError:
msgbox error
Resume Next

End Sub 

Then second uses DAO which is twice as fast.

CODE -->

Sub MakeGPSTableX(MyGPSData As String)
'Makes a table from GPS String data using DAO
Dim RowCounter As Integer
Dim Mydb As Database
Dim FieldString As String
T = Timer
On Error GoTo MGTError
Set Mydb = OpenDatabase(MyDrive & ":\DataCollectionServer\DataCollectionServerSettings.mdb")
Mydb.Execute "DELETE GPSData" 'clear old table entries
RowArray = Split(MyGPSData, vbCr)
For RowCounter = 1 To UBound(RowArray)-1
    FieldString = Chr(39) & Replace(RowArray(RowCounter), Chr(44), (Chr(39) & Chr(44) & Chr(39))) & Chr(39)
    Mydb.Execute ("INSERT INTO GPSData VALUES(" & FieldString & ")")
Next
Mydb.Close
Debug.Print Timer - T '(1.1secs)
On Error GoTo 0
Exit Sub

MGTError:
Msgbox error
Resume Next

End Sub 

If there was an easy way to write all rows in one SQL statement it would probably be faster still (like updating one table from another similar table takes 0.2 secomds). Challenge someone?

Incidentally this GPSData is the current locations of up to 1000 buses running around our city that I will process to feed currently up to 50 bus stations around the city with LCD screens showing real time departure info. Quite an interesting project. VB6 is still alive!
You can imagine that every millisecond counts as they all have to be updated with any changes every 20 seconds.

Once I make a table it is quick and easy to sort out from the data which bus is shown on which stop. Each query only takes a few milliseconds and I run a group query for each bus stop every 200ms and send it to the stops in turn.

RE: Convertting a text CSV string to a Msaccess table.

Just to compare times could you try the following alternative.
Untested so may give errors


CODE

Sub MakeGPSTable(MyGPSData As String)
'make table from CSV string using ADODB
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet As New ADODB.Recordset
Dim RowArray() As String
Dim RowCounter As Integer
Dim FieldString As String

Dim RowDataArray() As Variant
Dim RowfieldArray() As Variant
RowfieldArray() = Array("field1", "field2", "field3", "field4") ---- change this to match your field names and number of fields

T = Timer
On Error GoTo MGTXError
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & MyDrive & ":\DataCollectionServer\DataCollectionServerSettings.mdb"
MyConn.Open
MyConn.Execute ("DELETE FROM GPSData")
MyRecSet.Open "GPSData", MyConn,,adLockOptimistic
RowArray = Split(MyGPSData, vbCr)
For RowCounter = 1 To UBound(RowArray) - 1
    RowDataArray = Split(RowArray(RowCounter), ",")
    MyRecSet.AddNew RowfieldArray, RowDataArray
Next
MyRecSet.UpdateBatch
MyConn.Close
Debug.Print Timer - T
On Error GoTo 0
Exit Sub

MGTXError:
msgbox error
Resume Next

End Sub 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Convertting a text CSV string to a Msaccess table.

I have no idea what code you actually tested but this shows an elapsed time for each run varying from 0.21 to 0.25 seconds here. And this isn't a particularly fast PC by current standards either.

CODE

Option Explicit

Private Const CONNSTRING As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$MDB$'"

Private DbPath As String
Private CN As ADODB.Connection
Private RS As ADODB.Recordset
Private FieldNames As Variant

Private Sub OpenDb()
    If Len(Dir$(DbPath, vbNormal)) > 0 Then
        Set CN = New ADODB.Connection
        CN.Open Replace$(CONNSTRING, "$MDB$", DbPath)
    Else
        With CreateObject("ADOX.Catalog")
            .Create Replace$(CONNSTRING, "$MDB$", DbPath)
            Set CN = .ActiveConnection
        End With
        CN.Execute "CREATE TABLE SOMETABLE(" _
                 & "F1 TEXT(25)," _
                 & "F2 TEXT(25)," _
                 & "F3 TEXT(25)," _
                 & "F4 TEXT(25)," _
                 & "F5 TEXT(25)," _
                 & "F6 TEXT(25)," _
                 & "F7 TEXT(25)," _
                 & "F8 TEXT(25))", _
                   , _
                   adCmdText Or adExecuteNoRecords
    End If
    'Hard-code or could fetch looping over ADOX Columns of Table:
    FieldNames = Array("F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8")
    Set RS = New ADODB.Recordset
    With RS
        .CursorLocation = adUseServer
        .Open "SOMETABLE", CN, adOpenForwardOnly, adLockOptimistic, adCmdTable
    End With
End Sub

Private Sub CloseDb()
    RS.Close
    CN.Close
End Sub

Private Function VSplit(ByVal Text As String, ByVal Delim As String) As Variant
    'Like Split, but returns a Variant containing an array of Variants.
    Dim Pos As Long
    Dim NextPos As Long
    Dim I As Long
    Dim ReturnValue As Variant

    Pos = 1
    Do
        Pos = InStr(Pos, Text, Delim)
        If Pos = 0 Then Exit Do
        I = I + 1
        Pos = Pos + Len(Delim)
    Loop
    ReDim ReturnValue(I)
    Pos = 1
    I = 0
    Do
        NextPos = InStr(Pos, Text, Delim)
        If NextPos = 0 Then Exit Do
        ReturnValue(I) = Trim$(Mid$(Text, Pos, NextPos - Pos))
        I = I + 1
        Pos = NextPos + Len(Delim)
    Loop
    ReturnValue(I) = Trim$(Mid$(Text, Pos))
    VSplit = ReturnValue
End Function

Private Sub AddNewRows(ByVal CsvLines As String)
    Dim Lines() As String
    Dim I As Long
    
    Lines = Split(CsvLines, vbCr)
    For I = 0 To UBound(Lines)
        If Len(Lines(I)) > 0 Then
            RS.AddNew FieldNames, VSplit(Lines(I), ",")
        End If
    Next
End Sub

Private Sub Main()
    Dim I As Long
    Dim Line As String
    Dim CsvLines As String
    Dim T0 As Single
    
    DbPath = App.Path & "\demo.mdb"
    OpenDb
    
    Line = "Name567890123456789012345,Junk567890123456789012345," _
         & "Stuff67890123456789012345,Junk5678901234567890123," _
         & "Stuff678901234567890123,Junk567890123456789012," _
         & "Stuff67890123456789012,Crap" & vbCr
    CsvLines = Space$(Len(Line) * 5700)
    For I = 1 To 5700
        Mid$(CsvLines, (I - 1) * Len(Line) + 1) = Line
    Next

    T0 = Timer()
    AddNewRows CsvLines
    MsgBox Format$(Timer() - T0, "#,##0.000")
    CloseDb
End Sub 

RE: Convertting a text CSV string to a Msaccess table.

Adding one line:

CODE

Private Sub AddNewRows(ByVal CsvLines As String)
    Dim Lines() As String
    Dim I As Long
    
    CN.Execute "DELETE FROM SOMETABLE", , adCmdText Or adExecuteNoRecords
    Lines = Split(CsvLines, vbCr)
    For I = 0 To UBound(Lines)
        If Len(Lines(I)) > 0 Then
            RS.AddNew FieldNames, VSplit(Lines(I), ",")
        End If
    Next
End Sub 

... increases each run by an additional 0.03 seconds or so.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Aren't you putting the same data into every row of the table? I suspect this would be much faster.
I would think you have to try a real 90000 long CSV string where ever character is different to test it.
The data I tested it with is the real data of an existing system downloaded from the internet.
You have to make up a CSV string using random characters to really test it.

The batch processing suggestion when applied to my DAO version halves the time again to 0.6 seconds.
I inserted dbEngine.Workspaces(0).Begintrans before and Committrans after.

The general impression I have had is that SQL add tables (insertion) is faster than using Addnew and DAO is faster than ADODB but is this the case?

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Still experimenting!

RE: Convertting a text CSV string to a Msaccess table.

>DAO which is twice as fast.

Well, yes. DAO is the data access technology of choice for Jet databases.

>If there was an easy way to write all rows in one SQL statement

You can with SQL Server (as fredericofonseca hinted much earlier in the thread)

>You're right, speed might not be a factor.
>the first method takes 2.2 seconds, the second 1.1 seconds which I can probably live with

So, is speed a factor or not?

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Yes speed is a factor but it is relative. I need to know what time it will take so I can arrange other things in the App.
If it is slow I would have to arrange other things differently and split less important tasks.
My problem is I have to download the data and update every one of the (now) 72 clients once every 15 seconds.
I am therefore looking for the fastest possible so I can arrange timings of other activities and split tasks at different parts of a 15 second cycle. Plus a lot of other housekeeping tasks that happen once a minute 24 hours a day .
Eg. 5 seconds to make the table would be hopeless, 1.2 workable but .6 secs much better. 0.3 would be excellent!

I have written quite a few aps with cyclic routines in them to receive data, process and feed out to machines continuously and I know every millisecond can count.

Yes I am looking for a suitable SQL batch update query from text.
I haven't been able to get fredericofonseca's suggestion to work as a whole update.
It would appear that you still have to apply Addnew for every row.
It is likely therefore no faster.

When you mention SQL Server, are you saying this has a SQL statement that will do a batch job that Jet won't do?

RE: Convertting a text CSV string to a Msaccess table.

>When you mention SQL Server, are you saying this has a SQL statement that will do a batch job that Jet won't do?

Yep. For example, SQL Server's INSERT INTO ... VALUES is more flexible than that in Jet-SQL, and can add up to 1000 rows in a single statement rather than 1 (you'd still require a teeny bit of manipulation of the CSV string to get it into the correct format)

RE: Convertting a text CSV string to a Msaccess table.

No, the "speed" of DAO is almost entirely mythical aside from a few contrived cases which don't fit your scenario at all.

No, AddNew in the form I used above can be far faster than cobbling together SQL DML by hand and executing it, especially when using a firehose cursor as I did.

No, there is no need for "random" data for a test such as this. I tried it, same results: well under 0.3 seconds and most trials only 0.25 or less. Far better than your 1.1 second times.

CODE

Private Sub Main()
    Dim InsertAt As Long
    Dim I As Long
    Dim F As Long
    Dim Field As String
    Dim CsvLines As String
    Dim T0 As Single
    
    DbPath = App.Path & "\demo.mdb"
    OpenDb
    
    Randomize
    
    CsvLines = Space$(18& * 8& * 5700&)
    InsertAt = 1
    For I = 1 To 5700
        For F = 1 To 8
            Field = ChrW$(Int(Rnd() * 26) + 65) _
                  & CStr(Int(Rnd * 100000000)) _
                  & CStr(Int(Rnd * 100000000))
            Mid$(CsvLines, InsertAt) = Field
            If F = 8 Then
                Mid$(CsvLines, InsertAt + Len(Field)) = vbCr
            Else
                Mid$(CsvLines, InsertAt + Len(Field)) = ","
            End If
            InsertAt = InsertAt + Len(Field) + 1
        Next
    Next
    CsvLines = Left$(CsvLines, InsertAt - 1)

    T0 = Timer()
    AddNewRows CsvLines
    MsgBox Format$(Timer() - T0, "#,##0.000")
    CloseDb
End Sub 

You not only fail to use string builder logic, but fail to use multicharacter literals where you could and instead have character by character concatentation of characters generated on the fly using the slowest possible methods. Not only do you use the slow Variant Chr() function, this is the slower ANSI flavor of the slow Variant ChrW() function! All of this slow-boat cabbage chewing is frighteningly bad programming.

It becomes harder to take you seriously each day. I can't wait to see how you manage to move the goalposts next.

RE: Convertting a text CSV string to a Msaccess table.

> a few contrived cases a few contrived cases

Have to say that when the company I was working for actually carried out some tests a number of years ago, DAO did indeed prove to be faster than ADO (not by a huge margin, admittedly) for simple operations, which this is. I suppose it is possible that they were contrived cases.

RE: Convertting a text CSV string to a Msaccess table.

Makes you wonder why his vaunted DAO test case takes so long then. Maybe it's all of that other bad code? The biggest downside of DAO is that it closes off the option of another DBMS.

RE: Convertting a text CSV string to a Msaccess table.

> The biggest downside of DAO is that it closes off the option of another DBMS.

Indeed.

RE: Convertting a text CSV string to a Msaccess table.

Quote:



Yes I am looking for a suitable SQL batch update query from text.
I haven't been able to get fredericofonseca's suggestion to work as a whole update.
It would appear that you still have to apply Addnew for every row.
It is likely therefore no faster.

yes you do the addnew for each row ... but the main thing is that you only update those to the DB after all rows have been added to the recordset, not 1 by 1

CODE

For RowCounter = 1 To UBound(RowArray) - 1
    RowDataArray = Split(RowArray(RowCounter), ",")
    MyRecSet.AddNew RowfieldArray, RowDataArray
Next
MyRecSet.UpdateBatch 

note that the updatebatch is after the loop

As for addnew being faster than a single insertion - it all depends on volumes.

small volumes - direct insert is normally faster

significant volumes - parameterized query is faster than direct insert, but and will be faster than batchupdate if volumes are really huge and a single update is done - note that you can do batch updates every 50k records for example which would be faster than loading 1,000,000 records into a recordset and one single update at the end.

and as mentioned by others using DAO locks you into Access - have you tried playing around with sql server express? you can use its localdb feature. only "issue" is 1GB amx memory used which may still outperform Access. database size is 10GB PER database, not instance, so really it means a limit of 10GB per table, so not really an issue if you are using Access

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Convertting a text CSV string to a Msaccess table.

(OP)
Thanks, I really appreciate your interests!

Don't worry, I will experiment again with dilettante's suggestion when I get the rest of the app finished.

The database is really quite a small one in this case and 0.6 sec is not too bad.

RE: Convertting a text CSV string to a Msaccess table.

I tested both wrapping the updates in a transaction and batch updating. There was no advantage in doing either one, as expected.

RE: Convertting a text CSV string to a Msaccess table.

But since I did sleep at a Holiday Inn Express last night wink I took another stab at this.

Here is an even faster refinement, taking from 0.14 to 0.16 seconds to insert the 5700 rows from a String. Bonus, it is even a little more generalized than the previous code. I don't think it has bugs left though some might well lurk in there somewhere yet.

CODE

Option Explicit

Private Const CONNSTRING As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$MDB$'"

Private DbPath As String
Private CN As ADODB.Connection
Private FieldIds As Variant

Private Sub OpenDb()
    If Len(Dir$(DbPath, vbNormal)) > 0 Then
        Set CN = New ADODB.Connection
        CN.Open Replace$(CONNSTRING, "$MDB$", DbPath)
    Else
        With CreateObject("ADOX.Catalog")
            .Create Replace$(CONNSTRING, "$MDB$", DbPath)
            Set CN = .ActiveConnection
        End With
        CN.Execute "CREATE TABLE SOMETABLE(" _
                 & "F1 TEXT(25)," _
                 & "F2 TEXT(25)," _
                 & "F3 TEXT(25)," _
                 & "F4 TEXT(25)," _
                 & "F5 TEXT(25)," _
                 & "F6 TEXT(25)," _
                 & "F7 TEXT(25)," _
                 & "F8 TEXT(25))", _
                   , _
                   adCmdText Or adExecuteNoRecords
    End If
    CN.CursorLocation = adUseServer
    FieldIds = Array("F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8")
End Sub

Private Sub CloseDb()
    CN.Close
End Sub

Private Sub PutString( _
    ByRef StringData As String, _
    ByVal Connection As ADODB.Connection, _
    ByVal TableName As String, _
    ByVal ColumnIds As Variant, _
    Optional ByVal ColumnDelimiter As String = vbTab, _
    Optional ByVal RowDelimiter As String = vbCr, _
    Optional ByVal NullExpr As Variant = vbNullString)
    'A sort of "inverse analog" of the ADO Recordset's GetString() method.
    
    Dim RS As ADODB.Recordset
    Dim ColumnStart As Long
    Dim ColumnLength As Long
    Dim ColumnValues() As Variant
    Dim Pos As Long
    Dim RowLimit As Long
    Dim I As Long
    Dim AtRowEnd As Boolean

    If (VarType(ColumnIds) And vbArray) = 0 Then Err.Raise 5 'Invalid procedure call or argument.
    
    With New ADODB.Command
        Set .ActiveConnection = CN
        .CommandType = adCmdTable
        .CommandText = TableName
        .Properties![Append-Only Rowset] = True
        .Properties![Others' Changes Visible] = False 'Doesn't matter when using exclusive access.
        Set RS = .Execute()
    End With
    
    ReDim ColumnValues(UBound(ColumnIds))
    Pos = 1
    Do
        RowLimit = InStr(Pos, StringData, RowDelimiter)
        If RowLimit = 0 Then RowLimit = Len(StringData)
        I = 0
        AtRowEnd = False
        Do
            ColumnStart = Pos
            Pos = InStr(Pos, StringData, ColumnDelimiter)
            If Pos = 0 Or Pos >= RowLimit Then
                ColumnLength = RowLimit - ColumnStart
                If Pos <> 0 Then
                    Pos = Pos + Len(RowDelimiter)
                    If Mid$(StringData, Pos, 1) = vbLf Then Pos = Pos + 1 'Auto-handle CrLf.
                End If
                AtRowEnd = True
            Else
                ColumnLength = Pos - ColumnStart
                Pos = Pos + Len(ColumnDelimiter)
            End If
            ColumnValues(I) = Trim$(Mid$(StringData, ColumnStart, ColumnLength))
            If Not IsMissing(NullExpr) Then
                If ColumnValues(I) = NullExpr Then ColumnValues(I) = Null
            End If
            I = I + 1
        Loop Until AtRowEnd
        RS.AddNew ColumnIds, ColumnValues
    Loop Until Pos = 0
End Sub

Private Sub Main()
    Dim InsertAt As Long
    Dim I As Long
    Dim F As Long
    Dim FieldText As String
    Dim CsvLines As String
    Dim T0 As Single
    
    DbPath = App.Path & "\demo.mdb"
    OpenDb
    CN.Execute "DELETE FROM SOMETABLE", , adCmdText Or adExecuteNoRecords
    
    Randomize
    
    CsvLines = Space$(18& * 8& * 5700&)
    InsertAt = 1
    For I = 1 To 5700
        For F = 1 To 8
            FieldText = ChrW$(Int(Rnd() * 26) + 65) _
                      & CStr(Int(Rnd * 100000000)) _
                      & CStr(Int(Rnd * 100000000))
            Mid$(CsvLines, InsertAt) = FieldText
            If F = 8 Then
                Mid$(CsvLines, InsertAt + Len(FieldText)) = vbCr
            Else
                Mid$(CsvLines, InsertAt + Len(FieldText)) = ","
            End If
            InsertAt = InsertAt + Len(FieldText) + 1
        Next
    Next
    CsvLines = Left$(CsvLines, InsertAt - 1)

    T0 = Timer()
    PutString CsvLines, CN, "SOMETABLE", FieldIds, ","
    MsgBox Format$(Timer() - T0, "#,##0.000")
    CloseDb
End Sub 

RE: Convertting a text CSV string to a Msaccess table.

I had a nagging suspicion...

Yep, it was horribly flawed. After the 1st row it was losing the 1st column and using column 1 as 0, 2 as 1, etc. and reusing the last column value from the 1st row for every subsequent row.

This appears to fix it though, with no loss in performance:

CODE

Private Sub PutString( _
    ByRef StringData As String, _
    ByVal Connection As ADODB.Connection, _
    ByVal TableName As String, _
    ByVal ColumnIds As Variant, _
    Optional ByVal ColumnDelimiter As String = vbTab, _
    Optional ByVal RowDelimiter As String = vbCr, _
    Optional ByVal NullExpr As Variant = vbNullString)
    'A sort of "inverse analog" of the ADO Recordset's GetString() method.
    
    Dim RS As ADODB.Recordset
    Dim ColumnStart As Long
    Dim ColumnLength As Long
    Dim ColumnValues() As Variant
    Dim Pos As Long
    Dim NewPos As Long
    Dim RowLimit As Long
    Dim I As Long
    Dim AtRowEnd As Boolean

    If (VarType(ColumnIds) And vbArray) = 0 Then Err.Raise 5 'Invalid procedure call or argument.
    
    With New ADODB.Command
        Set .ActiveConnection = CN
        .CommandType = adCmdTable
        .CommandText = TableName
        .Properties![Append-Only Rowset] = True
        .Properties![Others' Changes Visible] = False 'Doesn't matter when using exclusive access.
        Set RS = .Execute()
    End With
    
    ReDim ColumnValues(UBound(ColumnIds))
    Pos = 1
    Do
        RowLimit = InStr(Pos, StringData, RowDelimiter)
        If RowLimit = 0 Then RowLimit = Len(StringData) + 1
        I = 0
        AtRowEnd = False
        Do
            ColumnStart = Pos
            NewPos = InStr(Pos, StringData, ColumnDelimiter)
            If NewPos = 0 Or NewPos > RowLimit Then
                Pos = InStr(Pos, StringData, RowDelimiter)
                ColumnLength = RowLimit - ColumnStart
                If Pos <> 0 Then
                    Pos = Pos + Len(RowDelimiter)
                    If Mid$(StringData, Pos, 1) = vbLf Then Pos = Pos + 1 'Auto-handle CrLf.
                End If
                AtRowEnd = True
            Else
                Pos = NewPos
                ColumnLength = Pos - ColumnStart
                Pos = Pos + Len(ColumnDelimiter)
            End If
            ColumnValues(I) = Trim$(Mid$(StringData, ColumnStart, ColumnLength))
            If Not IsMissing(NullExpr) Then
                If ColumnValues(I) = NullExpr Then ColumnValues(I) = Null
            End If
            I = I + 1
        Loop Until AtRowEnd
        RS.AddNew ColumnIds, ColumnValues
    Loop Until Pos = 0
End Sub 

RE: Convertting a text CSV string to a Msaccess table.

Sure enough, another bug.

At the end of PutString the last line should rad:

CODE

Loop Until Pos = 0 Or Pos > Len(StringData) 

Without this change it worked when the final line had no trailing row delimiter but failed if there was one. I'd been testing both cases to make sure and somehow missed it on the last pass through.

RE: Convertting a text CSV string to a Msaccess table.

(OP)
SELECT CASE IDEAL
I would have thought at a Holiday Inn you'd be sitting at the poolside bar surrounded by tall shapely blondes, sipping your martini(s)?
ELSE
"What will you have, Sir?"
"One part adExecuteNoRecords, two parts ColumnDelimiter and InsertAt a sprig of StringData, shaken not stirred"
END IF

RE: Convertting a text CSV string to a Msaccess table.

Holiday Inn Express, sort of like a Motel 6 with cleaner sheets.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close