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.

 

[SonOfEmidec1100]

You are going about the wrong way, the code you are using is for a file of fixed record length (132 characters). A CSV file will almost certainly be variable length.

The code is for the click event of the import button. The first (Private Sub...) and last (End Sub) lines will be there by default

You need something like this, and add an error handler -

Private Sub btnLVImport_Click()

Dim ImportRec As String
Dim fnumber As Long
Dim Counter As Long

ON Error GOTO ErrorHandler
' {Connection string information}
btnLVImport.enabled = false   'prevent multiple clicks.
fnumber = FreeFile
Open "c:\LVImport.csv" For Input As fnumber

Do Until EOF(fnumber)
    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)
    Me.Refresh
    ' Reads and stores the text data into the database table
    Line Input #fnumber, ImportRec
    conn.Execute "INSERT INTO LV_Datalog yadda yadda   "
Loop
' Closes and destroys objects
conn.Close
Set conn = Nothing
Close fnumber

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

EXIT SUB

ErrorHandler:
'simplified
MSGBOX "Error " & Err.Description


End Sub

 

[trollacious]

I'd guess that each record is 132 bytes long, so the number of records would be the entire length of the file divided by the length of one individual record.

With a CSV file, individual fields are often variable length, so I don't use LOF to calculate the number of records.

Just rewriting the code quickly, I'd use:

Dim onerecord As String
Open "c:\LVImport.csv" For Input As #1

Do While Not EOF(1)
    ' Reads and stores the text data into the database table
    Line Input #1, onerecord
    conn.Execute "INSERT INTO LV_Datalog yadda yadda     
Loop

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

Lee

 

[johnwm]

NumOfRecords = LOF(1) / 132 ' Record size

will only work if you have fixed length records, which is generally not the case with csv files. You can read csv files directly from Access without any programming with File|Get External Data|Import

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[barbola]

Thank you. I get the Done message, but no data was put in. I'll figure it out from here (I hope!)

Thanks!
Barb E.

 

[Swi]

You could use ADO and some SQL to get the record count as well. Also, please take a look at the connection string as you can specify whether or not you have a header or not.

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""
rs.Open "SELECT COUNT(*) FROM Test.csv", _
conn, adOpenStatic, adLockReadOnly, adCmdText
MsgBox rs.Fields(0).Value & " records!"
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

Swi

 

[barbola]

I'll take anotherr look. There is a header, BUT I created a batch file that consolidates several individual csv files into one so there are headings throughout the file for each file that was appended. I don't know how to get rid of them or if they would even cause problems.

I guess I will find out once I get it working.


Thanks!
Barb E.

 

[barbola]

Okay the program sort of works (ie. doesn't crash) and gives me the number of records in a msgbox at the end.
However, the records in the Access table have no data! I don't know why. Here is a sample of the csv file:

Timestamp,Seconds Since Start of Logfile,Notes,Temperature Probe 1 (degrees Celsius),Temperature Probe 2 (degrees Celsius),Ammeter 1,Ammeter 2
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

Here is the access table - the first two records already existed, but the last two are all zeroes - it didn't copy in the data from the file:

Timestamp Seconds Since Start of Logfile Notes Temperature Probe 1 (degrees Celsius) Temperature Probe 2 (degrees Celsius) Ammeter 1 Ammeter 2 Id
10/6/2004 11:38:00 AM 77706 85.8 28.1 181 8 523
10/6/2004 11:43:00 AM 78006 85.8 28.1 171 6 524
12:00:00 AM 0 0 0 0 0 27034
12:00:00 AM 0 0 0 0 0 27035

Anyone know how I can find out why it's not working? Also, when I use EOF it only reads 74 records. There are over 13,000 records so I changed it to "do while c < 13112" and it reads all the records, but still gives me all zeros.





Thanks!
Barb E.

 

[gmmastros]

It seems like the recommended suggestions use Line Input. This will read the entire line from the file (until a vbCrLf) is found.

it could be that your sql string is treating all the data as one field. You should use the Split function on the 1 variable read from the file. Teh split function will put the data in to an array.

Then your sql would look like....

INSERT INTO LV_Datalog (Field1, Field2, ...)
Values(DataArray(0), DataArray(1), DataArray(etc...)

You'll need to be a little careful. This assumes that the same number of commas are found on each row of the file, and the data on each row is always ordered properly.

 

[pkailas]

Open [yourfile] for Input as #1
Do While Not EOF(1)
  Input #1, Timestamp, Seconds, Notes, Temp1, Temp2, Amm1, Amm2
  conn.Execute "INSERT INTO LV_Datalog ([timestamp], [seconds], [notes], [temp1], [temp2], [amm1], [amm1]) values ('" & Timestamp & "', " & Seconds & ", '" & notes & "', " & temp1 & ", " & temp2 & ", " & amm1 & ", " & amm2 & ")"
Loop
Close #1

Replace the strings inside the [] with the appriate column names and of course declare all the variables as well as your connection stuff.

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

 

[barbola]

Option Explicit
Private Type LVMonitorData
LogTimestamp As Date
Seconds As Long
Notes As String
Temp1 As Double
Temp2 As Double
Amm1 As Long
Amm2 As Long
End Type

So what exactly does this part of my code do as opposed to your suggestions?

Here is part of the rest of it:

Open "f:\mill\simplicity\LVImport.csv" For Binary As #1
Dim c As Integer
Dim onerecord As String
c = 1
Do While Not EOF(1)
c = c + 1
Line Input #1, onerecord
' Reads and stores the text data into the database table
conn.Execute "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 ( #" & ImportRec.LogTimestamp & "#, " & ImportRec.Seconds & ",'" & ImportRec.Notes & "'," & ImportRec.Temp1 & ", " & ImportRec.Temp2 & ", " & ImportRec.Amm1 & "," & ImportRec.Amm2 & ")"
Loop

I guess my biggest problem is not understanding what is happening.

Thanks!
Barb E.

 

[gmmastros]

onerecord is the variable you are using to get the data from the file. When inserting the record, you are using ImportRec. The problem is... You are not taking the data from the onerecord variable and parsing it in to ImportRec.

To highlight my point...

After the Line Input statement, put ...

debug.print onerecord
If you debug.print your ImportRec.(any field), you'll notice that the values there are empty.

You need to parse the onerecord variable in to the importrec variable before inserting in to the database.

Hope this helps.

 

[pkailas]

Input #1, Timestamp, Seconds, Notes, Temp1, Temp2, Amm1, Amm2

This code will read the line and parse it to those variable names.

In the Insert statment there are two parts..

INSERT INTO

-- self explanitory

([column1], [colum2]) are the LITERAL names of the columns that you are inserting data into. Needs to be spelled correctly.

VALUES (" & var1 & ", " & var2 & ")"
this part are the actual values you wish to insert into the columsn you just defined. Use the appropriate ' or # or whatever is needed for the data types.

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

 

[barbola]

Then what is the purpose of the Type..End Type statement? Do i still need that?

Thanks!
Barb E.

 

[pkailas]

I'd just change..

Private Type LVMonitorData
    LogTimestamp As Date
    Seconds As Long
    Notes As String
    Temp1 As Double
    Temp2 As Double
    Amm1 As Long
    Amm2 As Long
End Type

to

dim LogTimestamp As Date
dim Seconds As Long
dim Notes As String
dim Temp1 As Double
dim Temp2 As Double
dim Amm1 As Long
dim Amm2 As Long

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

 

[barbola]

Pkailas I tried your code and it doesn't work. It says i need to declare variables (ie LogTimeStamp)

Dim ImportRec As LVMonitorData 'LVMonitorData comes from the Type statement

Do While Not EOF(1)
c = c + 1
Input #1, LogTimestamp, Seconds, Notes, Temp1, Temp1, Amps1, Amps2
conn.Execute "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 ( #" & ImportRec.LogTimestamp & "#, " & ImportRec.Seconds & ",'" & ImportRec.Notes & "'," & ImportRec.Temp1 & ", " & ImportRec.Temp2 & ", " & ImportRec.Amm1 & "," & ImportRec.Amm2 & ")"
Loop



Thanks!
Barb E.

 

[pkailas]

ahh..

ok.

Private Sub btnLVImport_Click()
dim LogTimestamp As Date
dim Seconds As Long
dim Notes As String
dim Temp1 As Double
dim Temp2 As Double
dim Amm1 As Long
dim Amm2 As Long

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

 

[pkailas]

Private Sub btnLVImport_Click()
dim LogTimestamp As Date
dim Seconds As Long
dim Notes As String
dim Temp1 As Double
dim Temp2 As Double
dim Amm1 As Long
dim Amm2 As Long

also in the INSERT statement you must use these variables as they are populated with the values from your csv file


Question for you..

What are the colmun names in your database?

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

 

[barbola]

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 ( #" & ImportRec.LogTimestamp & "#, " & ImportRec.Seconds & ",'" & ImportRec.Notes & "'," & ImportRec.Temp1 & ", " & ImportRec.Temp2 & ", " & ImportRec.Amm1 & "," & ImportRec.Amm2 & ")"

The first set (insert into) are the column names and the second part (VALUES) are the variables read from the csv file. I think it is working right now....it's taking forever so I'll let you know.

Thanks!
Barb E.

 

[barbola]

Maybe it would help if I understood what I need to do in order.

-connection to Access (done)
-open access database (done)
-I need to open the csv file
-I need each line of the csv file to be treated as one record with 7 columns (There are13,111 lines)
-I need each of those records and columns to be inserted into an Access table.

Thank you for all your help so far. However, I have ended up with bits and pieces of code from above and it's a big mess I'm afraid. Can we start over please?



Thanks!
Barb E.