Help with editing CSV before import

Question

jay.barnes 4 Light Poster

13 Years Ago

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

csv edit import microsoft-access visual-basic-6

2 Contributors
42 Replies
746 Views
5 Months Discussion Span
Latest Post 12 Years Ago Latest Post by AndreRet

All 42 Replies

AndreRet 526 Senior Poster

13 Years Ago

Hehehe...:) There is a huge difference.

Below is the code for vb6. Let me know if it helped...

Option Explicit

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long

'Purpose     :  Saves pictures in image boxes (or similiar) to a field in a recordset
'Inputs      :  oPictureControl                 A control containing an image
'               adoRS                           ADO recordset to add the image to
'               sFieldName                      The field name in adoRS, to add the image to
'Outputs     :  Returns True if succeeded in updating the recordset
'Notes       :  The field specified in sFieldName, must have a binary field type (ie. OLE Object in access)
'               Save the image at the currect cursor location in the recordset.
'Revisions   :

Public Function SavePictureToDB(oPictureControl As Object, adoRS As ADODB.Recordset, sFieldName As String) As Boolean
    Dim oPict As StdPicture
    Dim sDir As String, sTempFile As String
    Dim iFileNum As Integer
    Dim lFileLength As Long
    Dim abBytes() As Byte
    Dim iCtr As Integer
    
    On Error GoTo ErrHandler
    
    Set oPict = oPictureControl.Picture
    If oPict Is Nothing Then
        SavePictureToDB = False
        Exit Function
    End If

    'Save picture to temp file
    sTempFile = FileGetTempName
    SavePicture oPict, sTempFile
    
    'read file contents to byte array
    iFileNum = FreeFile
    Open sTempFile For Binary Access Read As #iFileNum
    lFileLength = LOF(iFileNum)
    ReDim abBytes(lFileLength)
    Get #iFileNum, , abBytes()
    'put byte array contents into db field
    adoRS.Fields(sFieldName).AppendChunk abBytes()
    Close #iFileNum
    
    'Don't return false if file can't be deleted
    On Error Resume Next
    Kill sTempFile
    SavePictureToDB = True
    Exit Function
    
ErrHandler:
    SavePictureToDB = False
    Debug.Print Err.Description
End Function


'Purpose     :  Loads a Picture, saved as binary data in a database, from a recordset into a picture control.
'Inputs      :  oPictureControl                 A control to load the image into
'               adoRS                           ADO recordset to add the image to
'               sFieldName                      The field name in adoRS, to add the image to
'Outputs     :  Returns True if succeeded in loading the image
'Notes       :  Loads the image at the currect cursor location in the recordset.


Public Function LoadPictureFromDB(oPictureControl As Object, adoRS As ADODB.Recordset, sFieldName As String) As Boolean
    Dim oPict As StdPicture
    Dim sDir As String
    Dim sTempFile As String
    Dim iFileNum As Integer
    Dim lFileLength As Long
    Dim abBytes() As Byte
    Dim iCtr As Integer
    
    On Error GoTo ErrHandler
    sTempFile = FileGetTempName
   
    iFileNum = FreeFile
    Open sTempFile For Binary As #iFileNum
    lFileLength = LenB(adoRS(sFieldName))
    
    abBytes = adoRS(sFieldName).GetChunk(lFileLength)
    Put #iFileNum, , abBytes()
    Close #iFileNum

    oPictureControl.Picture = LoadPicture(sTempFile)
    
    Kill sTempFile
    LoadPictureFromDB = True
    Exit Function
    
ErrHandler:
    LoadPictureFromDB = False
    Debug.Print Err.Description
End Function


'Purpose     :  The FileGetTempName function returns a name of a temporary file.
'Inputs      :  [sFilePrefix]               The prefix of the file name.
'Outputs     :  Returns the name of the next free temporary file name (and path).
'Notes       :  The filename is the concatenation of specified path and prefix strings,
'               a hexadecimal string formed from a specified integer, and the .TMP extension


Function FileGetTempName(Optional sFilePrefix As String = "TMP") As String
    Dim sTemp As String * 260, lngLen As Long
    Static ssTempPath As String
    
    If LenB(ssTempPath) = 0 Then
        'Get the temporary path
        lngLen = GetTempPath(260, sTemp)
        'strip the rest of the buffer
        ssTempPath = Left$(sTemp, lngLen)
        If Right$(ssTempPath, 1) <> "\" Then
            ssTempPath = ssTempPath & "\"
        End If
    End If
    
    'Get a temporary filename
    lngLen = GetTempFileName(ssTempPath, sFilePrefix, 0, sTemp)
    'Remove all the unnecessary chr$(0)'s
    FileGetTempName = Left$(sTemp, InStr(1, sTemp, Chr$(0)) - 1)
End Function


'SAMPLE USAGE
'NOTE : Add a PictureBox control to a form before running this code
Sub TestLoadPicture()
    Dim sConn As String
    Dim oConn As New ADODB.Connection
    Dim oRs As New ADODB.Recordset
    
    On Error GoTo ErrFailed
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyPhotos.MDB;Persist Security Info=False"
    
    oConn.Open sConn
    oRs.Open "SELECT * FROM Copies", oConn, adOpenKeyset, adLockOptimistic
    'If oRs.EOF = False Then
        LoadPictureFromDB Picture2, oRs, "EmpPhoto"
    'End If
    oRs.Close
    Exit Sub
ErrFailed:
    MsgBox "Error " & Err.Description
End Sub

'SAMPLE USAGE
'NOTE : Add a PictureBox control to a form before running this code
Sub TestSavePicture()
    Dim sConn As String
    Dim oConn As New ADODB.Connection
    Dim oRs As New ADODB.Recordset
    
    On Error GoTo ErrFailed
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\192.168.0.199\cserver\EmployeeCopies.MDB;Persist Security Info=False"
    
    oConn.Open sConn
    oRs.Open "SELECT * FROM Copies", oConn, adOpenKeyset, adLockOptimistic
    'If oRs.EOF = False Then
        oRs.AddNew
        SavePictureToDB Picture1, oRs, "EmpPhoto"
        oRs.Update
        MsgBox "saved"
    'End If
    oRs.Close
    Exit Sub
ErrFailed:
    MsgBox "Error " & Err.Description
End Sub

Private Sub Command1_Click()
Call TestLoadPicture
End Sub

Private Sub Command2_Click()
Call TestSavePicture
End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

AndreRet 526 Senior Poster · Answer 1 · 2010-11-08T22:13:58+00:00

Have a look HERE on opening the csv file.

and HERE to manipulate the rows.

jay.barnes 4 Light Poster · Answer 2 · 2010-11-08T23:10:49+00:00

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!

AndreRet 526 Senior Poster · Answer 3 · 2010-11-08T23:17:23+00:00

spreadsheet, rather than how to open it in VB

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

jay.barnes 4 Light Poster · Answer 4 · 2010-11-08T23:31:10+00:00

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

AndreRet 526 Senior Poster · Answer 5 · 2010-11-09T01:04:39+00:00

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.:)

jay.barnes 4 Light Poster · Answer 6 · 2010-11-09T02:01:34+00:00

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

AndreRet 526 Senior Poster · Answer 7 · 2010-11-09T15:15:33+00:00

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.

jay.barnes 4 Light Poster · Answer 8 · 2010-11-09T21:14:12+00:00

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

AndreRet 526 Senior Poster · Answer 9 · 2010-11-10T00:38:32+00:00

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?

jay.barnes 4 Light Poster · Answer 10 · 2010-11-10T00:53:30+00:00

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

AndreRet 526 Senior Poster · Answer 11 · 2010-11-11T15:16:30+00:00

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

jay.barnes 4 Light Poster · Answer 12 · 2010-11-12T20:26:07+00:00

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?

AndreRet 526 Senior Poster · Answer 13 · 2010-11-12T20:32:42+00:00

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.

jay.barnes 4 Light Poster · Answer 14 · 2010-11-15T21:44:13+00:00

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

AndreRet 526 Senior Poster · Answer 15 · 2010-11-16T00:46:59+00:00

AndreRet 526 Senior Poster

13 Years Ago

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

jay.barnes 4 Light Poster · Answer 16 · 2010-11-18T22:49:19+00:00

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.

AndreRet 526 Senior Poster · Answer 17 · 2010-11-22T14:32:35+00:00

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.

jay.barnes 4 Light Poster · Answer 18 · 2010-11-30T21:06:01+00:00

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

AndreRet 526 Senior Poster · Answer 19 · 2010-11-30T21:20:30+00:00

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.:)

jay.barnes 4 Light Poster · Answer 20 · 2010-11-30T22:01:13+00:00

I'm pretty sure it's VB6 - whichever language that's used to script forms in MS Access 2002.

AndreRet 526 Senior Poster · Answer 21 · 2010-11-30T22:07:39+00:00

Ahhhrggg! Jay, ignore the above code. Totally irrelevant...

It is definitely time to shoot home. Don't know if its the tiredness or the beer...;)

jay.barnes 4 Light Poster · Answer 22 · 2010-12-01T19:49:57+00:00

Hehehe....whoa! Lost me on that one for a while! I think it may be the beer (in that, you probably need more). My professional recommendation would be to take (at least) two, and call me in the morning! :-)

AndreRet 526 Senior Poster · Answer 23 · 2010-12-02T19:32:37+00:00

This will help in your header problem, copying data from line 2 onwards -

Public Function USED()
USED = Application.CountIf(ActiveSheet.Columns(1), ">""") + Application.CountIf(ActiveSheet.Columns(1), ">0")
End Function 'This will get the maximum rows to be copied. There are other ways as well...

Dim Row As Integer, Max_Row As Integer, Max_Col As Integer

For Row = 2 To max_row 'you can change this to For row = 4 to max_row etc
  'Code to copy data here
Next Row

Have a look HERE at the heading "Merging a Range from All Workbooks in a Folder" with code on how to copy the data from different sheets....

jay.barnes 4 Light Poster · Answer 24 · 2010-12-02T20:01:36+00:00

So your "For Row .... Next Row" loop statement should replace my "Do while not EOF (f) ... Loop" loop?

AndreRet 526 Senior Poster · Answer 25 · 2010-12-02T22:22:52+00:00

Yes, because you can control where to start the loop from, in this case 2, which is line 3.

jay.barnes 4 Light Poster · Answer 26 · 2010-12-03T19:38:27+00:00

The thing is, while I see the function "USED" declared and defined in your code, where is the function called further down? I don't see where it fits in in the sub.

AndreRet 526 Senior Poster · Answer 27 · 2010-12-03T19:56:27+00:00

Have a look at the function heading, its called "USED":)

jay.barnes 4 Light Poster · Answer 28 · 2010-12-03T20:09:13+00:00

Yep, I caught that, but the "End Function" command closes the "USED" function, which, I assume ends the function's definition. At that point, while it is defined, it still needs to be called to be, well, used. I don't see it called anywhere in the code after it is created and defined.

I apologize if I'm missing something that should be obvious....maybe I need more beer? :-)

Help with editing CSV before import

Recommended Answers Collapse Answers

All 42 Replies

Recommended Answers