Convertting a text CSV string to a Msaccess table.

[tedsmith]

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?

 

[Andrzejek]

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.

 

[strongm]

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.

 

[Andrzejek]

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:[tt]
123,ABCD,1/1/2015,100.65,Bob Brown[/tt]

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.

 

[strongm]

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

 

[dilettante]

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:

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:

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.

 

[Andrzejek]

So it looks like to you do know (or hope to get) the string like:[tt]
Boston,42.4565,93.5678
Huston,43.456,92.654[/tt]
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.

 

[dilettante]

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

 

[Andrzejek]

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’ (who read other tedsmith’s posts) but elude my perception.

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. :-(


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.

 

[tedsmith]

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.

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[dilettante]

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.

 

[fredericofonseca]

one thing that may work.. but you will need to search and try it .. is to load the csv string into a stream object, and then load the recordset from the stream ... long shot. may or not work.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tedsmith]

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?

 

[dilettante]

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

 

[dilettante]

Oops, I meant Recordsets.

 

[tedsmith]

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?

 

[strongm]

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.

 

[dilettante]

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.

 

[tedsmith]

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.