954,582 Members — Technology Publication meets Social Media

1 2 3 >

Have something to say? Contribute New Article Reply to this Article

0

Help with editing CSV before import

Hello!

I am working with an Access Database (Access 2002) that contains a form that is supposed to import a CSV. However, because some of the integers contained in the CSV are too large for Access to handle (regardless of the number format I select for the field), I'm going to need to do some data transformations on the CSV before I can import it into Access.

An example of a row from this CSV would be:

| Client IP | Requests | Views | Time | Total Bytes | Bytes Received | Bytes Sent | ------------------------------------------------------------------------------------------ | 192.168.192.168 | 313170 | 7198 | 74330 | 5718744799 | 5416925116 | 301819683 |
So, this is the code that I have in place to perform the import:

Private Sub cmdImport_Click()

    Dim ReportDate As String
    ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear

If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "Please Select a CSV File Before Proceeding"
    Me.cmdSelect.SetFocus
    Exit Sub
End If
DoCmd.TransferText acImportDelim, "CSVImport", "tblUsage", Me.txtFileName, True

DoCmd.RunSQL ("UPDATE tblUsage SET [Report Date] = #" & ReportDate & "# WHERE [Report Date] IS NULL")

 
End Sub

However, before I can perform the import, I need to have this done to the CSV file: 1. Open the CSV 2. Walk through each row (starting with row 2) a. with column D (originally in minutes) I. if value < 60, do no operations on the figure, and append "Mins" label after it II. if value > 60 and < 1440, divide it by 60 to 1 decimal, and append "Hrs" label after it III. if value > 1440, divide it by 1440 to 1 decimal, and append "Days" label after it b. with columns E, F, and G (originally in bytes) I. if value < 1,024, do no operations on the figure, and append "Bytes" label after it II. if value > 1,024 and < 1,048,576, divide it by 1024 to 1 decimal, and then append "KB" label after it III. if value > 1,048,576 and < 1,073,741,824, divide it by 1,048,576 to 1 decimal place, and then append "MB" label after it IV. if value > 1,073,741.824, divide it by 1,073,741,824 to 1 decimal place, and then append "GB" label after it 3. Save the file 4. Close the file
Being fairly brand new to VB, I can't seem to figure out how to open a file like this, and make alterations to it. Does anyone have any suggestions on this?

Thanks for any help you guys can provide

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

Have a look HERE on opening the csv file.

and HERE to manipulate the rows.

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Hi, AndreRet

Thanks for the reply!

However, from what I see (unless I'm missing something), the first link illustrates how to import a CSV into an Access spreadsheet, rather than how to open it in VB.

The second link, while it does show you how to manipulate rows (to copy and paste), but it doesn't illustrate the two things I'm looking to do: to walk through each row, starting at row 2, and to perform the necessary operations on the values based on their contents.

Have you had any experience attempting to do either or both of those things I just described above?

Thanks again!

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

spreadsheet, rather than how to open it in VB

Does this refer to VBA, VB6 or VB.Net?

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

I think it's VBA - as I'm doing this solely through MS Access 2002

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

Jay, my apologies. I will have a solution for you first thing in the morning if no one else gave it. I'm knocking off home now, 21:00 on my side.:)

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Hey, no problem - thank you for your help, and have a good evening!

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

Hi Jay. Thanks:)

Have a look at THIS link for opening a file to write or read from in VBA. It covers all possible scenarios, just choose the one that will work for you.

I then found this that seems exactly what you are looking for with a solution sample code, enjoy:)

Find it HERE .

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Thanks for the suggestion! I think I've got something here, but it doesn't seem to like a couple lines (indicated by the "------>"), returning a Compile Error "Must be first statement on the line". I can't seem to figure out what the problem is there..

Private Sub cmdImport_Click()

    Dim ReportDate As String
    Dim CSVFile As String
    Dim CurrentRow As String
    
    'Assign values to the Dims
    CSVFile = Me.txtFileName
    CurrentRow = 2
    
    'Open the file based on the Dim name
    Open CSVFile For Output As #1
    
    'Walk through the rows where there is an IP address in column "A",
    'breaking out of the loop when you run out of entries
    Do While CurrentRow & "A" & IsNotNull
        
        CurrentDRowCell = CurrentRow & "D"

        'Run the time unit conversions on all entries in column "D"
        If CurrentDRowCell.Value < 60 Then
            CurrentDRowCell.Value = CurrentDRowCell.Value & " Mins"
        Else If CurrentDRowCell.Value >= 60 AND CurrentDRowCell.Value < 1440 Then
------>     CurrentDRowCell.Value = CurrentDRowCell.Value / 60 & " Hrs"
        Else If CurrentDRowCell.Value >= 1440 Then
------>     CurrentDRowCell.Value = CurrentDRowCell.Value / 1440 & " Days"
        End If
    
    Loop
    
    'Close the file after performing the necessary operations on it
    Close #1
    
    ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear

If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "Please Select a CSV File Before Proceeding"
    Me.cmdSelect.SetFocus
    Exit Sub
End If
DoCmd.TransferText acImportDelim, "CSVImport", "tblUsage", Me.txtFileName, True

DoCmd.RunSQL ("UPDATE tblUsage SET [Report Date] = #" & ReportDate & "# WHERE [Report Date] IS NULL")

 
End Sub

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

If I read this correct, I think that after the devision, it returns a number that corresponds with minutes and being in a loop it counts the row again, hence the error???

Move to the next row and then loop through the code again from the new row?

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

It's supposed to take the number currently in the cell, say, for the sake of argument, the number is 700
Since the number isn't less than 60, it will pass to the next step
Since the number is more than 60 but less than 1440, it will be converted from minutes to hours (divided by 60), and have the label "Hrs" appended to the resulting number, and then pass on to the next step.
Since the number now is less than 1440, nothing more will be done to the cell contents, and it should exit the loop.

...at least that's what I'm intending it to do... :-p

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

I should have read your code better, change the Else If to ElseIf.:)

Else If CurrentDRowCell.Value >= 60 AND CurrentDRowCell.Value < 1440 Then ------> CurrentDRowCell.Value = CurrentDRowCell.Value / 60 & " Hrs" Else If CurrentDRowCell.Value >= 1440 Then ------> CurrentDRowCell.Value = CurrentDRowCell.Value / 1440 & " Days"

ElseIf

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Hi, Andre

Thanks for catching my "ElseIf" goof. That seems to have taken care of that couple of lines. At this point, however, it doesn't seem to like my loop statement, or at least the syntax of it:

Do While CurrentRow & "A" & ISNOTNULL

I also just input this line before the "Loop" statement:

CurrentRow = CurrentRow + 1

to move the operation to the next row in the CSV file (I forgot to input that part initially)

Can you find anything wrong with these two statements that might be interfering with my script?

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

Change the following -

Do While CurrentRow & "A" & ISNOTNULL

to

Do While Not CurrentRow & "A" & IS NULL

I'm not sure how the "A" is defined or referenced, though, not sure about that part. I know it refers to coloumn A, but how is it declared?

The second part seems fine, row count + 1.

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Thanks, I'll try a few different things, and let you know what I find. :-)

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

No problem. Let us know how it went.:)

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Okay, I think I've got something here. This will walk through the CSV in question, but it doesn't seem to be picking up any of the values, and is returning a SQL syntax error, which I can't figure out:

Private Sub cmdImport_Click()

    'Make sure they enter a file before continuing
    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "Please Select a CSV File Before Proceeding"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

    'declare your database variable
    Dim proddb1 As Database

    'declare your script variables
    Dim f As Integer, strvalues, strSQL, RecordCount As Integer, RowCount As Integer

    'declare your database field variables
    'Dim ID As String
    Dim ClientIP As String, ImportDate As Date, ReportDate As Date
    Dim Requests As Integer, PageViews As Integer
    Dim BrowseTime As Integer, BrowseTimeUnit As String
    Dim TotalBytes As Integer, TotalBytesUnit As String
    Dim BytesReceived As Integer, BytesReceivedUnit As String
    Dim BytesSent As Integer, BytesSentUnit As String

    Dim CSVFile
    Dim CurrentRow As String
    
    'Assign values to the Dims
    CSVFile = Me.txtFileName
    ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear
    ImportDate = DateValue(Now)
    'MsgBox "Import Date: " & ImportDate
    'MsgBox "Report Date: " & ReportDate
    f = FreeFile
    RowCount = 1
    RecordCount = 0

    ' Set proddb1 = OpenDatabase(App.Path & "\internet_statistics.mdb", False, False, _
    ' ";pwd=myPassword")
    
    Set proddb1 = OpenDatabase("internet_statistics.mdb")
    
    'Open the CSV file based on the Dim name
    Open CSVFile For Input As f
    
    'Walk through the rows and perform the operation in the loop,
    'breaking out of the loop when you hit the end of the file
    Do While Not EOF(f)
        Input #f, ClientIP, ImportDate, ReportDate, Requests, PageViews, BrowseTime, BrowseTimeUnit, TotalBytes, TotalBytesUnit, BytesReceived, BytesReceivedUnit, BytesSent, BytesSentUnit
        'If Len(ClientIP) > 0 Then
        
            If BrowseTime < "60" Then
                BrowseTimeUnit = " Mins"
            ElseIf BrowseTime >= "60" And BrowseTime < "1440" Then
                BrowseTime = BrowseTime / "60"
                BrowseTimeUnit = " Hrs"
            ElseIf BrowseTime >= "1440" Then
                BrowseTime = BrowseTime / "1440"
                BrowseTimeUnit = " Days"
            End If
            
            If TotalBytes < "1024" Then
                TotalBytesUnit = "Bytes"
            ElseIf TotalBytes >= "1024" And TotalBytes < "1048576" Then
                TotalBytes = TotalBytes / "1024"
                TotalBytesUnit = "KB"
            ElseIf TotalBytes >= "1048576" Then
                TotalBytes = TotalBytes / "1048576"
                TotalBytesUnit = "MB"
            ElseIf TotalBytes >= "1073741824" Then
                TotalBytes = TotalBytes / "1073741824"
                TotalBytesUnit = "GB"
            End If
            
            If BytesReceived < "1024" Then
                BytesReceivedUnit = "Bytes"
            ElseIf BytesReceived >= "1024" And BytesReceived < "1048576" Then
                BytesReceived = BytesReceived / "1024"
                BytesReceivedUnit = "KB"
            ElseIf BytesReceived >= "1048576" Then
                BytesReceived = BytesReceived / "1048576"
                BytesReceivedUnit = "MB"
            ElseIf BytesReceived >= "1073741824" Then
                BytesReceived = BytesReceived / "1073741824"
                BytesReceivedUnit = "GB"
            End If
            
            If BytesSent < "1024" Then
                BytesSentUnit = "Bytes"
            ElseIf BytesSent >= "1024" And BytesSent < "1048576" Then
                BytesSent = BytesSent / "1024"
                BytesSentUnit = "KB"
            ElseIf BytesSent >= "1048576" Then
                BytesSent = BytesSent / "1048576"
                BytesSentUnit = "MB"
            ElseIf BytesSent >= "1073741824" Then
                BytesSent = BytesSent / "1073741824"
                BytesSentUnit = "GB"
            End If
        
            strvalues = Chr(34) & ClientIP & Chr(34) & "," _
            & Chr(34) & ImportDate & Chr(34) & "," _
            & Chr(34) & ReportDate & Chr(34) & "," _
            & Chr(34) & Requests & Chr(34) & "," _
            & Chr(34) & PageViews & Chr(34) & "," _
            & Chr(34) & BrowseTime & Chr(34) & "," _
            & Chr(34) & BrowseTimeUnit & Chr(34) & "," _
            & Chr(34) & TotalBytes & Chr(34) & "," _
            & Chr(34) & TotalBytesUnit & Chr(34) & "," _
            & Chr(34) & BytesReceived & Chr(34) & "," _
            & Chr(34) & BytesReceivedUnit & Chr(34) & "," _
            & Chr(34) & BytesSent & Chr(34) & "," _
            & Chr(34) & BytesSentUnit & Chr(34)

            strSQL = "INSERT INTO tblUsage (Client IP, Import Date, Report Date, Requests, Page Views, Browse Time, Browse Time Unit, Total Bytes, Total Bytes Unit, Bytes Received, Bytes Received Unit, Bytes Sent, Bytes Sent Unit) "
            strSQL = strSQL & "VALUES(" & strvalues & ") "
            
            'Execute INSERT statement to append this record to Access table
            If RowCount <> 1 Then
                MsgBox strSQL
                proddb1.Execute strSQL
            End If

            RecordCount = RecordCount + 1
            RowCount = RowCount + 1
            'Else: Exit Do
        'End If
    Loop

    MsgBox RecordCount & " Records Imported"

    Close #f

    proddb1.Close

End Sub




Private Sub cmdSelect_Click()

    Dim strStartDir As String
    Dim strFilter As String
    Dim lngFlags As Long
    
    ' Lets start the file browse from our current directory
    strStartDir = CurrentDb.Name
    strStartDir = Left(strStartDir, Len(strStartDir) - Len(Dir(strStartDir)))
    
    strFilter = ahtAddFilterItem(strFilter, _
                        "CSV Files (*.csv)", "*.csv")
    Me.txtFileName = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
                     Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
                     DialogTitle:="Select CSV File to Import")
        
End Sub

From what I see from the Msg window I have popping up to check the SQL statement, I have:

"INSERT INTO tblUsage (Client IP, Import Date, Report Date, Requests, Page Views, Browse Time, Browse Time Unit, Total Bytes, Total Bytes Unit, Bytes Received, Bytes Received Unit, Bytes Sent, Bytes Sent Unit) VALUES("131443", "12:00:00 AM","12:00:00 AM", "0", "0", "0"," Mins","0","Bytes","0","Bytes","0","Bytes")"

It seems as if the data from the CSV Row isn't making it to the variables, which aren't making it into the loop

One thing I think I should mention, is that I do have an auto-incrementing field at the beginning of the table in the DB, titled "ID". I'm not sure if omitting it from the SQL statement would affect it, as I figure it would just auto-increment upon inserting the rest of the record.

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

If I read all of this correctly, the problem lies with your strvalues. You are putting all the data into one string and then try to insert it into separate data fields.

Split your string values into their respective field values and then add it to your data fields.

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

0

Hi, AndreRet

Thank you for the suggestion. At this point, I have it importing the data, but the fields are being offset for some reason. Despite the fact that I'm instructing the script to ignore the first row of data (the field headers), it's starting the import about halfway through the first row.

Just to recap, the CSV I have is laid out as such:
"Client IP", "Requests","Page Views","Browse Time","Total Bytes","Bytes Received","Bytes Sent" "192.168.11.51","313170", "9790", "131443", "1748744799"," 1446925116" ,"301819683" "192.168.1.154","282194","13627","106904","4782312382","4533412944","248899438" "192.168.4.123","89338","6724","94321","795051302","728398881","66652421" "192.168.1.174","292182","13799","93801","2726315773","2486891259","239424514"

However, after interspersing the rest of the data, and performing the import, my DB table looks like this: ID | ClientIP | ImportDate | ReportDate | Requests | PageViews | BrowseTime | BrowseTimeUnit | TotalBytes |TotalBytesUnit | BytesReceived |BytesReceivedUnit | BytesSent | BytesSentUnit | (AutoNumber) | (Text) | (Text) | (Text) | (Number) | (Number) | (Number) | (Text) | (Number) | (Text) | (Number) | (Text) | (Number) | (Text) | --------------|----------------|------------|------------|------------|---------------|------------|----------------|------------|---------------|---------------|------------------|-----------|---------------| 13526 | Bytes Received | 11/30/2010 | 12/01/2010 | (Blank) | (Blank) | 0 | Mins | 0 | Bytes | 0 | Bytes | 0 | Bytes | 13527 | 1446925116 | 11/30/2010 | 12/01/2010 | 301819683 | 192.168.1.154 | 0 | Mins | 0 | Bytes | 0 | Bytes | 0 | Bytes | 13528 | 4533412944 | 11/30/2010 | 12/01/2010 | 248899438 | 192.168.4.123 | 0 | Mins | 0 | Bytes | 0 | Bytes | 0 | Bytes | 13529 | 728398881 | 11/30/2010 | 12/01/2010 | 66652421 | 192.168.1.174 | 0 | Mins | 0 | Bytes | 0 | Bytes | 0 | Bytes |

When it *should* look like this (ignoring mathematical and unit conversions for the time being): ID | ClientIP | ImportDate | ReportDate | Requests | PageViews | BrowseTime | BrowseTimeUnit | TotalBytes |TotalBytesUnit | BytesReceived |BytesReceivedUnit | BytesSent | BytesSentUnit | (AutoNumber) | (Text) | (Text) | (Text) | (Number) | (Number) | (Number) | (Text) | (Number) | (Text) | (Number) | (Text) | (Number) | (Text) | --------------|----------------|------------|------------|------------|---------------|------------|----------------|------------|---------------|---------------|------------------|-----------|---------------| 13526 | 192.168.11.51 | 11/30/2010 | 12/01/2010 | 313170 | 9790 | 131443 | Mins | 1748744799 | Bytes | 1446925116 | Bytes | 301819683 | Bytes | 13527 | 192.168.1.154 | 11/30/2010 | 12/01/2010 | 282194 | 13627 | 106904 | Mins | 4782312382 | Bytes | 4533412944 | Bytes | 248899438 | Bytes | 13528 | 192.168.4.123 | 11/30/2010 | 12/01/2010 | 89338 | 6724 | 94321 | Mins | 795051302 | Bytes | 728398881 | Bytes | 66652421 | Bytes | 13529 | 192.168.1.174 | 11/30/2010 | 12/01/2010 | 292182 | 13799 | 93801 | Mins | 2726315773 | Bytes | 2486891259 | Bytes | 239424514 | Bytes |

*note: just to keep things simple for the moment, I set all the date fields to "Text"

For some reason, the script seems to be doing all of the following:
1. It's taking the last two fields of the first row, and inserting them into the first two fields of the database that aren't pre-determined by the script.
2. It also seems to be failing to insert the numerical data for the BrowseTime, TotalBytes, BytesReceived, and BytesSent fields, leaving them all at "0"
3. It also seems to be ignoring any end-of-record markers in the CSV file, simply adding all the records as one long string. And moving on to the next record when it runs out of fields in the current one.

Here is the script as I have it so far. Again, thanks for your help and guidance on this issue!

Private Sub cmdImport_Click()

    'Make sure they enter a file before continuing
    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "Please Select a CSV File Before Proceeding"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

    'declare your database variable
    Dim proddb1 As Database

    'declare your script variables
    Dim f As Integer, strSQL, strvalues, RecordCount As Integer, RowCount As Integer

    'declare your database field variables
    'Dim ID As String
    Dim ClientIP As String, ImportDate As String, ReportDate As String
    Dim Requests As String, PageViews As String
    Dim BrowseTime As Integer, BrowseTimeUnit As String
    Dim TotalBytes As Integer, TotalBytesUnit As String
    Dim BytesReceived As Integer, BytesReceivedUnit As String
    Dim BytesSent As Integer, BytesSentUnit As String

    Dim CSVFile
    Dim CurrentRow As String
    
    'Assign values to the Dims
    CSVFile = Me.txtFileName
    ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear
    ImportDate = DateValue(Now)
    f = FreeFile()
    RowCount = 1
    RecordCount = 0
    
    Set proddb1 = OpenDatabase("internet_statistics.mdb")
    
    'Open the CSV file based on the Dim name
    Open CSVFile For Input As #f
    'Open CSVFile For Binary Access Write As f
    
    'Walk through the rows and perform the operation in the loop,
    'breaking out of the loop when you hit the end of the file
    Do Until EOF(f)
        Input #f, ClientIP, Requests, PageViews, BrowseTime, TotalBytes, BytesReceived, BytesSent
        If Len(ClientIP) > 0 Then
        
            If RowCount < 5 Then
                MsgBox "Row: " & RowCount & Chr(13) _
                & "Client IP: " & ClientIP & Chr(13) _
                & "Import Date: " & ImportDate & Chr(13) _
                & "Report Date: " & ReportDate & Chr(13) _
                & "Requests: " & Requests & Chr(13) _
                & "Page Views: " & PageViews & Chr(13) _
                & "Browse Time: " & BrowseTime & " " & BrowseTimeUnit & Chr(13) _
                & "Total Bytes: " & TotalBytes & " " & TotalBytesUnit & Chr(13) _
                & "Bytes Received: " & BytesReceived & " " & BytesReceivedUnit & Chr(13) _
                & "Bytes Sent: " & BytesSent & " " & BytesSentUnit & Chr(13) _
                

            End If
        
            If BrowseTime < 60 Then
                BrowseTimeUnit = " Mins"
            ElseIf BrowseTime >= 60 And BrowseTime < 1440 Then
                BrowseTime = BrowseTime / 60
                BrowseTimeUnit = " Hrs"
            ElseIf BrowseTime >= 1440 Then
                BrowseTime = BrowseTime / 1440
                BrowseTimeUnit = " Days"
            End If
            
            If TotalBytes < 1024 Then
                TotalBytesUnit = "Bytes"
            ElseIf TotalBytes >= 1024 And TotalBytes < 1048576 Then
                TotalBytes = TotalBytes / 1024
                TotalBytesUnit = "KB"
            ElseIf TotalBytes >= 1048576 Then
                TotalBytes = TotalBytes / 1048576
                TotalBytesUnit = "MB"
            ElseIf TotalBytes >= 1073741824 Then
                TotalBytes = TotalBytes / 1073741824
                TotalBytesUnit = "GB"
            End If
            
            If BytesReceived < 1024 Then
                BytesReceivedUnit = "Bytes"
            ElseIf BytesReceived >= 1024 And BytesReceived < 1048576 Then
                BytesReceived = BytesReceived / 1024
                BytesReceivedUnit = "KB"
            ElseIf BytesReceived >= 1048576 Then
                BytesReceived = BytesReceived / 1048576
                BytesReceivedUnit = "MB"
            ElseIf BytesReceived >= 1073741824 Then
                BytesReceived = BytesReceived / 1073741824
                BytesReceivedUnit = "GB"
            End If
            
            If BytesSent < 1024 Then
                BytesSentUnit = "Bytes"
            ElseIf BytesSent >= 1024 And BytesSent < 1048576 Then
                BytesSent = BytesSent / 1024
                BytesSentUnit = "KB"
            ElseIf BytesSent >= 1048576 Then
                BytesSent = BytesSent / 1048576
                BytesSentUnit = "MB"
            ElseIf BytesSent >= 1073741824 Then
                BytesSent = BytesSent / 1073741824
                BytesSentUnit = "GB"
            End If
        
            strvalues = Chr(34) & ClientIP & Chr(34) & "," _
            & Chr(34) & ImportDate & Chr(34) & "," _
            & Chr(34) & ReportDate & Chr(34) & "," _
            & Chr(34) & Requests & Chr(34) & "," _
            & Chr(34) & PageViews & Chr(34) & "," _
            & Chr(34) & BrowseTime & Chr(34) & "," _
            & Chr(34) & BrowseTimeUnit & Chr(34) & "," _
            & Chr(34) & TotalBytes & Chr(34) & "," _
            & Chr(34) & TotalBytesUnit & Chr(34) & "," _
            & Chr(34) & BytesReceived & Chr(34) & "," _
            & Chr(34) & BytesReceivedUnit & Chr(34) & "," _
            & Chr(34) & BytesSent & Chr(34) & "," _
            & Chr(34) & BytesSentUnit & Chr(34)

            strSQL = "INSERT INTO tblUsage (ClientIP,ImportDate,ReportDate,Requests,PageViews,BrowseTime,BrowseTimeUnit,TotalBytes,TotalBytesUnit,BytesReceived,BytesReceivedUnit,BytesSent,BytesSentUnit) "
            strSQL = strSQL & "VALUES(" & strvalues & ") "
            'strSQL = strSQL & "VALUES(" & Chr(34) & ClientIP & "," & ImportDate & Chr(34) & "," & Chr(34) & ReportDate & Chr(34) & "," & Requests & "," & PageViews & "," & BrowseTime & "," & Chr(34) & BrowseTimeUnit & Chr(34) & "," & TotalBytes & "," & Chr(34) & TotalBytesUnit & Chr(34) & "," & BytesReceived & "," & Chr(34) & BytesReceivedUnit & Chr(34) & "," & BytesSent & "," & Chr(34) & BytesSentUnit & Chr(34) & ") "
            Debug.Print strSQL
            
            'Execute INSERT statement to append this record to Access table
            If RowCount > 1 Then
                proddb1.Execute strSQL
            End If
            
            'If RowCount = 1 Then
            '    MsgBox strvalues
            'End If

            RecordCount = RecordCount + 1
            RowCount = RowCount + 1
            Else: Exit Do
        End If
    Loop

    MsgBox RecordCount & " Records Imported"

    Close #f

    proddb1.Close

End Sub

Light Poster

46 posts since Jun 2009

Reputation Points: 14

Solved Threads: 0

0

Just to help without going back to the beginning/first post, you are using vb6 or vba?

I'm shooting home now, will have a look tomorrow.:)

Senior Poster

3,922 posts since Jan 2008

Reputation Points: 334

Solved Threads: 350

This question has already been solved

You

1 2 3 >

© 2012 DaniWeb® LLC

Home | About Us | Contact Us | Terms of Service | Advertising Opportunities