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]

Easy to do.. Is the header consistent in it's wording? Post me a couple of lines so I can write that for you as well. Then I'll explain the code to you.

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

 

[gmmastros]

The first piece of data in the file is date, right? for the heading rows... the actual heading is not a date, so...

Before putting the data in to the DB, insert the line...

If IsDate(arLine(0)) then
'add to database
Else
' just skip the database stuff
End If

 

[barbola]

YOU solved this problem! I should have posted all my code after I said to start over.

thanks people! stars all around!

Thanks!
Barb E.

 

[pkailas]

yes, what mnastros says! I was going to look for a value but his works and will protect it from erroring out.

add this to the code

If IsDate(arLine(0)) = True then
  conn.execute sql
End If

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

 

[pkailas]

now if you would post the code you ended up with I will explain what it all does for you, if you wish.

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

 

[barbola]

I changed it to

If IsNum(arLine(6)) then
'add to database

because sometimes the first few fields are numbers and then the headings - I think because some of the files have no records. Anyways....

IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!

I got my "success" message "Done! 13016 records imported"

yaaaaaaaaaaaaaaaaaaaaaaa!!!!!!!!!!!!!!! can i go home now?


Thanks!
Barb E.

 

[pkailas]

heh... glad we got that all to work for you.

No you can't go home! you have to bake me some cookies!

well, wait.. you would probably have to go home to do that, right?

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

 

[barbola]

I baked some on Wed night....and they're all gone! lol

I now have to change all the stuff to the Live database from my test one. THEN I can go home!

Thanks!
Barb E.