number of records in csv file 4

[barbola]

I have found a few threads on this, but the code is difficult for me to follow. Most of the time I have no idea where to put it. I'm taking VB courses but it's not at the point where my project at work requires me to be, so I'm kind of on my own here.

I have a project and a form. The form has two buttons. I got one to work (yay). The other button takes a csv file (LVImport.csv) and imports it into Access. I can't get the code to work. The code I copied has this statement:

NumOfRecords = LOF(1) / 132 ' Record size

I don't know where the 132 comes from or how to find what my number should be. The same number of fields exist in the file but the number of records varies.

Can you please help me with this code and tell me exactly where I need to put it?

Private Sub btnLVImport_Click()

{declared variables}
{Connection string information}

Open "c:\LVImport.csv" For Binary Access Read As #1

NumOfRecords = LOF(1) / 132 ' Record size

Do
Counter = Counter + 1
' Displays counter in label on the main screen
' so the user can see something is happening
Label1.Caption = "Records Imported = " & CStr(Counter)
DoEvents
' Reads and stores the text data into the database table
Get #1, , ImportRec
conn.Execute "INSERT INTO LV_Datalog yadda yadda
Loop Until Counter = NumOfRecords

' Closes and destroys objects
conn.Close
Set conn = Nothing
Close #1

' Prompts the user processing has completed
MsgBox "Done!", vbInformation

End Sub


Thanks!
Barb E.

 

[pkailas]

Open [yourfile] for Input as #1  '- this opens your file

Do While Not EOF(1) '-beginning of loop

  Input #1, Timestamp, Seconds, Notes, Temp1, Temp2, Amm1, Amm2  '- This reads one line parsing each column into a variable

  conn.Execute "INSERT INTO LV_Datalog ([timestamp], [seconds], [notes], [temp1], [temp2], [amm1], [amm1]) values ('" & Timestamp & "', " & Seconds & ", '" & notes & "', " & temp1 & ", " & temp2 & ", " & amm1 & ", " & amm2 & ")"

this inserts each "record" or "line" into your access table

Loop    '-loop
Close #1  '- closes your file when you are done

In the INSERT statement the first grouping of () contains the literal names of the columns (Fields) in your access table. In your case if you go to design mode of your access table you should see:
Timestamp
Seconds Since Start of Logfile
Notes
Temperature Probe 1 (degrees Celsius)
Temperature Probe 1 (degrees Celsius)
Ammeter 1
Ammeter 2

if not, you need to type in the INSERT statement exactly what the field names are.

in the second ( ) grouping are the values you want to insert. In the above code, those would be contained in variables used in the
Input #1, Timestamp, Seconds, Notes, Temp1, Temp2, Amm1, Amm2

statement.. Of course you'll need to delcare these varibles.

I hope this makes it more clear.

_______
I love small animals, especially with a good brown gravy....

 

[barbola]

Error: Overflow

Thanks!
Barb E.

 

[barbola]

I get this error if I use
do while c <= 13111 (I used 100 to test it)

if I use do while not EOF(1)

then i dont' get all the records.



Thanks!
Barb E.

 

[pkailas]

Well let's see if we can debug this...
does it error out immediately?

let's put in some error trapping

put in the rest of your code.

Private Sub btnLVImport_Click()
dim strInsert as string
On Error goto ErrorHandler

some more addtions

strInsert = "INSERT INTO LV_Datalog ([timestamp], [seconds], [notes], [temp1], [temp2], [amm1], [amm1]) values ('" & Timestamp & "', " & Seconds & ", '" & notes & "', " & temp1 & ", " & temp2 & ", " & amm1 & ", " & amm2 & ")"
 
conn.Execute strInsert

Finally...

Close #1

ErrorHandler:
if err.number <> 0 then
   msgbox err.description & vbcrlf & strInsert
end if

Hopefully when/if it errors you will be able to see what it was trying to insert. or if it is crashing reading the csv file you'll see the last successful insert.

Do you have the counter routine still in? Is it erroring at the end of the file?

_______
I love small animals, especially with a good brown gravy....

 

[pkailas]

the code I gave you will only work if there are 7 records in each line. Even if there isn't any data for a column or field, there has to be a "placeholder" for it, ie. , ,

how many lines get processed with the EOF(1) ?

Perhaps I could get a copy of your file to see what may be wrong with it? I do this every day, all day just about...

_______
I love small animals, especially with a good brown gravy....

 

[barbola]

Earlier this morning I ran it and it took a long time to get the overflow error. Now it occurs immediately. I put a break after c=c+1 and did a watch on c but it said there was nothing there.

There are 7 records in each line and blank ones are ,, so that is okay.

I will try with the error handler (this was covered in our course, but I still have no clue how to use it).

Thanks!
Barb E.

 

[barbola]

It says:
Overflow
INSERT INTO LV_Datalog ([Timestamp],[Seconds Since Start of Logfile],Notes,[Temperature Probe 1 (degrees Celsius)],[Temperature Probe 2 (degrees Celsius)],[Ammeter 1],[Ammeter 2]) VALUES ( #12:00:00 AM#,0,",0,0,0,0)

I dont' know if it's two single quotes or a double quote (Notes field).

I'm going to try it with a smaller file with no headings and see if that works.

Thanks!
Barb E.

 

[pkailas]

It doesn't look like it read any valid data from your file. You mentioned "headers". Is the first row header names?

also let's add the value of the counter to the error handler

ErrorHandler:
if err.number <> 0 then
   msgbox err.description & vbcrlf & strInsert[COLOR=red] & vbcrlf & c [/color]
end if
[/code.

_______
I love small animals, especially with a good brown gravy....

 

[barbola]

I'm starting to think it's the date field.

Access Table LV_Datalog:
FieldName = TimeStamp
Data Type: Date/Time
Format: General Date
Input Mask: "mm"/"dd"/"yy hh":"nn":"ss "a\m/"pm"

VB:
dim LogTimeStamp as Date
"INSERT INTO LV_Datalog ([Timestamp],[Seconds]...
VALUES ( #" & LogTimestamp & "#, " & Seconds...

Sample Data without headings:
2/1/05 2:24:40 PM ,0,, 73.9, 21.5,113,32
2/1/05 2:29:40 PM ,300,, 73.9, 21.5,116,33
2/1/05 2:34:40 PM ,599,, 74.8, 21.5,124,32
2/1/05 2:39:40 PM ,899,, 74.8, 21.0,119,32

And the error shows it trying to insert
VALUES ( #12:00:00 AM#,0,",0,0,0,0)




Thanks!
Barb E.

 

[barbola]

It reads 2 records.


Thanks!
Barb E.

 

[pkailas]

Ok, I ran some tests.. it is indeed your date.. so here's the way to do it.. we'll parse each line into an array using the comma as the separator

Dim arLine
Dim fso As FileSystemObject
Dim ts As TextStream
Set fso = CreateObject("Scripting.Filesystemobject")
Set ts = fso.OpenTextFile("[yourfile")
Do While Not ts.AtEndOfStream
  arLine = Split(ts.ReadLine, ",")
c = c + 1
  sql = "INSERT INTO LV_Datalog ([timestamp], [Seconds Since Start of Logfile], [notes], [Temperature Probe 1 (degrees Celsius)], "
  sql = sql & "[Temperature Probe 2 (degrees Celsius)], [Ammeter 1], [Ammeter 2]) values (#" & arLine(0) & "#, "
  sql = sql & arLine(1) & ", '" & arLine(2) & "', " & arLine(3) & ", " & arLine(4) & ", " & arLine(5) & ", " & arLine(6) & ")"
conn.execute sql
Loop

ErrorHandler:
If Err.Number <> 0 Then
    MsgBox Err.Description & vbCrLf & sql & vbCrLf & c
End If
End Sub

_______
I love small animals, especially with a good brown gravy....

 

[barbola]

I get the error

User defined type not defined
and it stops on
Dim fso As FileSystemObject


Thanks!
Barb E.

 

[pkailas]

not a prob!

Open your vb project.
Click Project.
Click References.
Put a check mark in 'Microsoft Scripting Runtime'

_______
I love small animals, especially with a good brown gravy....

 

[gmmastros]

Did you copy/paste my response from another post. Looks identical to my post with 'Microsoft Scripting Runtime' instead of 'Microsoft ActiveX data Object 2.x Library'.

If so, I'm glad I could save you a couple keystrokes.

 

[pkailas]

heh, you weren't supposed to be snooping on me! lol

_______
I love small animals, especially with a good brown gravy....

 

[gmmastros]

And did you notice that I got a star for typing a 4 line response? lol

 

[gmmastros]

yeah, I solved his problem. Luckily, it was something simple. Have a start from me for being so persistent.

 

[pkailas]

Why thank you. I'm probably an idiot for not asking her to post all her code... trying to do it piece meal.. argh.. thanks for the star though..

_______
I love small animals, especially with a good brown gravy....

 

[barbola]

THANKYOUTHANKYOUTHANKYOU :O) I am so happy! Not that I have a clue what the code is doing (well just a small clue!).

The only problem I have now is with the larger file that has consolidated many smaller files and each one has a heading and it crashes when it finds a heading. But I'm sure I'll come up with a way to do that - i will probaby find it in here somewhere!



Thanks!
Barb E.

 

[pkailas]

yeah, but you did solve his problem!

_______
I love small animals, especially with a good brown gravy....