It happens that Alok Singh formulated :
> Hi Garry,
>
> can you show me with code how You are inserting with maintaining 1st row as
> to make header of table ,and next other inserting fastest way into db...
> please show me your code view for that. little bit i m confuse over its
> inserting process into db directly into db.
> mine still its slow....
> please help me on this .
>
> Regards,
> Alok
Hi Alok,
I use VB6/VBA and so not sure what code you need. Basically, I use
standard file I/O functionality to read the entire CSV file into a
string variable and then dump this into 1D array using VB's Split()
function and specifying vbCrLf (carriage return line feed characters)
as the delimter.
If the first line of the CSV contains the column headings (a.k.a.:
Field Names) then this will occupy the first element in the array. <By
default this would be, for example, vaDataArray(0)> The next element in
the array will be the first record (line2 in the CSV)
If the first line of the CSV does not contain the headings then I
prepend the file contents in my string variable with a line containing
the headings and vbCrLf. Then I dump this into my 1D array as described
above.
Looping through the array gives me access to each record if I start the
loop at vaDataArray(1), thus the loop parameters of '1 To
UBound(vaDataArray)'.
I load the headings from vaDataArray(0).
Each record (including the headings) is a delimited string and so must
be parsed into single values. I might load each one into a temp 1D
array and loop it, OR use a For Each construct with the Split()
function. Either of these would give me access to the individual
values.
In the end, it's really quite fast. But this could not be the case for
lengthy files.
Sample Code: (VB/VBA)
Load the CSV contents into a string variable:
Dim sFileIn As String
sFileIn = GetTextFromFile(sFilePathAnd Name)
'GetTextFromFile: my function that uses standard VB/VBA I/O processes
To dump the file contents into a 1D array:
Dim vaDataArray As Variant
vaDataArray = Split(sFileIn, vbCrLf)
**If the headings are not included in the CSV then vaDataArray(0) will
not match your expected string value. (Suggest that you store the
expected delimited string for the headings somewhere in a constant)
'Check if headings are included; insert them if not.
'(This block of code will only execute if headings are missing)
If Not vaDataArray(0) = gsMY_EXPECTED_HEADERS Then '//not exact match
sFileIn = gsMY_EXPECTED_HEADERS & vbCrLf & sFileIn '//insert line
vaDataArray = Split(sFileIn, vbCrLf) '//reload array
End If
To process the headings from a delimited string:
Dim sz As Variant
For Each sz In Split(vaDataArray(0), ",")
'Add each heading to its appropriate column here.
'Use a counter to increment the column number for each iteration.
Next sz
To process the headings from a temp array:
Dim vaTemp As Variant, i As Long
vaTemp = Split(vaDataArray(0), ",")
For i = LBound(vaTemp) To UBound(vaTemp)
'Add each heading to its appropriate column here.
'A counter isn't needed here because you can work with the loop
'index to ref the columns.
Next
**I'm not familiar with how columns index in SQLite tables because I
haven't started using SQLite yet. If the first column index is '0'
(zero) then you'll have no problem using the loop index. If the first
column index is '1' (one) then your code will have to add 1 to the loop
index. For example: [ref to TableColumnHeader](i + 1) = vaTemp(i).
To process each record:
Dim i As Long, j As Long, vaTemp As Variant
For i = 1 To UBound(vaDataArray) '//records only (no headings)
vaTemp = Split(vaDataArray(i), ",") 'load individual data items
For j = LBound(vaTemp) To UBound(vaTemp) 'data items only
'Process same as shown above for headings.
Next '//item of data
Next '//record
Unfortunately, this is all the help I can provide for extracting data
from a CSV for parsing. Normally, I use the methods shown here to put
data into an Excel spreadsheet or grid control. I expect working with a
db table is not much different, though I would use ADO to do it since I
can dump the entire recordset into a table (assuming SQLite allows
this). This is how I work with mdb (MS Access) data tables. In this
scenario I still check for the expected headings string in the first
line of the CSV, and insert it if missing before grabbing the file into
a recordset with ADO. I would think you'd be better off going this way
for working with any large amount of data.
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
