Page 1 of 2 12 LastLast
Results 1 to 15 of 30

I need to fix a VBA code

  1. #1
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72

    Question I need to fix a VBA code

    Hello,

    I have an access file (see the attached file) which contains a VBA code that creates access files from csv files like the ones in the attached zip file. Let's name this access file the "converter".

    Basically, the converter :
    - analyzes the csv file and selects the data it is going to import to access following a few rules.
    - creates an access file with the same name as the original csv file
    - creates a table in the access file (with specific characteristics)


    - imports the data from csv to access

    If you want to try it :
    a/ download the csvfileupload.accdb somewhere in your hard drive
    b/ create a new folder called "csv" in the same path as the csvfileupload.accdb and copy the csv files from the attached zip file into this "csv" folder
    c/ create a new folder called "mdb" (for the created mdb files) in the same path as the csvfileupload.accdb
    d/ open csvfileupload.accdb and in the form, press "upload data". The mdb file will be added to the "mdb" folder once the import is done

    I'd like to implement 2 changes (the most important is #2) :
    1. The import of the very first line from the csv (csv header) file doesn't work :
    - for this line, the value in the ID field should be the concatenation of the 1st and 2nd columns of the csv file with "_" in between. (in access, ID is built like this = CSVcolumn1&"_"&CSVcolumn2)
    - for this line, the other fields show "0" instead of the csv header which should be imported if the selection criteria are respected (this means that only csv columns whose header starts with "Raise", "Bet" or "Call" should be imported in Valeur1, Valeur2, ... Valeur6 fields).
    2. It is supposed to process automatically several csv files in a row (every csv file located in the "csv" folder), but it stops after the 1st one.


    Given that I can't code in VBA at all, it would be great if someone could do help me !

    Thank you!

    Nick

  2. #2
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    it seems that the files were lost, so I attached them again with this post
    Attached Files Attached Files

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,566
    So whoever built this is no longer available to fix?

    The button Click event turns off warnings but warnings are never turned back on. Modify like:
    Code:
    Private Sub cmd_update_Click()
    DoCmd.SetWarnings False
    ListESY
    MsgBox "Table created and data inserted"
    DoCmd.SetWarnings True
    End Sub
    Suggest getData, CreateTable, getdataintotable can each be Sub instead of Function then use Call to run them.

    Code is just repeatedly deleting and creating the first file and choking on the createdb procedure. Modify ListESY procedure:
    Code:
    Public Sub ListESY()
    Dim oFile As Object
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFiles As Object
    Dim strFPath As String, strTable As String, strFile As String
    strFPath = Application.CurrentProject.Path & "\csv"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(strFPath)
    Set oFiles = oFolder.Files
    For Each oFile In oFiles
        strFile = Dir(strFPath & "\" & oFile.Name, vbNormal)
        strTable = Left(oFile.Name, Len(oFile.Name) - 4)
        Call getData(strFPath, strFile, strTable)
    Next
    End Sub
    Code is naming fields like Valeur1, etc. and the CSV header names are inserted into single field of record. Don't you want to use the CSV headers as field names? The code is only looking for "BET" header. None of the sample files have this header so only 0 is populated into every field. The fourth file has an additional field "Raise 70". One line of code appears to expect 7 fields, not 8.

    Modify CreateTable procedure:
    Code:
    Public Sub CreateTable(RS As ADODB.Recordset, strFile As String)
    Dim count As Integer, i As Integer
    Dim dbs As DAO.Database, tbl As TableDef, fld As Field
    Dim exists As Boolean
    Set dbs = CurrentDb
    Set tbl = dbs.CreateTableDef(strFile)
    Set fld = tbl.CreateField("Flop_Hand", dbText, 255)
    tbl.fields.Append fld
    count = RS.fields.count
    For i = 2 To count - 1
        Set fld = tbl.CreateField(RS.fields(i).Name, dbInteger)
        tbl.fields.Append fld
    NextDim exists As Boolean
    exists = doesTableExist(strFile)
    If doesTableExist(strFile) Then
       DoCmd.DeleteObject acTable, strFile
    End If
    dbs.TableDefs.Append tbl
    dbs.TableDefs.Refresh
    DoCmd.SelectObject acTable, strFile, True
    End Sub
    Modify getdataintotable procedure:
    Code:
    Public Sub getdataintotable(rs1 As ADODB.Recordset, tablename1 As String)
    Dim strquery As String
    Dim no2 As Integer
    Dim i As Integerrs1.MoveFirst
    Do While Not rs1.EOF
        strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0) & "_" & rs1.fields(1) & "',"
        For i = 2 To rs1.fields.count - 1
            no2 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
            If no2 > 0 Then
                strquery = strquery & rs1.fields(i) & ","
            Else
                strquery = strquery & "0,"
            End If
        Next
        strquery = Left(strquery, Len(strquery) - 1) & ")"
        CurrentDb.Execute strquery
        rs1.MoveNext
    Loop
    'createdb (tablename1)
    End Sub
    I simply commented out the call to createdb in getdataintotable procedure for now. Do you want to create an Access file for each table?

    Module header should have following two lines and the public variable fields is not needed in the header if suggested edits are implemented:
    Option Compare Database
    Option Explicit


    Be sure to run Debug>Compile after code edits. And also Compact & Repair periodically.
    Last edited by June7; 09-23-2017 at 08:00 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Hello June7,

    Thank you very much for your help!

    Quote Originally Posted by June7 View Post
    Do you want to create an Access file for each table?
    Separately for each one of these csv files, the converter should create an access file with the same name as the csv file (the extension would be the only difference), containing only 1 access table using a specific format :
    The table name should remain the default one "Table1".
    I think the converter does this properly even if I have seen an error message once saying the name of the csv was not a valid name (whereas I could not find any issue with the name).

    Quote Originally Posted by June7 View Post
    Code is naming fields like Valeur1, etc. and the CSV header names are inserted into single field of record. Don't you want to use the CSV headers as field names? The code is only looking for "BET" header. None of the sample files have this header so only 0 is populated into every field. The fourth file has an additional field "Raise 70". One line of code appears to expect 7 fields, not 8.
    The access table should always contain 7 fields. The filed names in the access table are : "ID", "Valeur1", "Valeur2", "Valeur3", "Valeur4", "Valeur5", "Valeur6".
    The "ID" field is the primary key (it's filled with the values we get after concatenating the first 2 fields from the csv which make it a unique ID).

    In the csv files, the number of columns may change. But the general structure remains the same :
    -the first 2 columns are the ones we use to build the key ID for each line. Each ID should be built like this = column1&"_"&column2. So for example, if the content in A2 is "2h 2d 2c" and the content in B2 is "4c3c", the key ID for this line should be "2h 2d 2c_4c3c"
    -columns 3, 4, 5, 6 are useless
    -columns titled as "CHECK" or "FOLD" are useless
    -every other column should be incorporated in the access file.

    The useless columns are always in the same position in the csv file : they are columns 3, 4, 5, 6 and the last one (whose header is either "CHECK" or "FOLD").

    The csv header whould be imported as any other line from the csv. If the csv header is not imported properly because of the treatment of the data before being imported (I think the code tries to remove the "." and the digits after the "." from the data importes in the Valeur1, Valeur2, ... Valeur6 fields), then this treatment can be removed from the code.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,566
    A little confused. You want table to have 7 fields. If only 3 columns of each CSV are of interest why have 7 fields in each table? Why populate with zeros?

    Which additional column header of the 9-column CSV should be ignored?

    You want the column headers to be imported as data in first row? Right now they are just saved as a single string into the ID field and other fields have zero.


    BTW, why bind form with no data entry controls to a table with no data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    A little confused. You want table to have 7 fields. If only 3 columns of each CSV are of interest why have 7 fields in each table? Why populate with zeros?
    It's been done like this so that the converter can handle up to 6 data fields + the ID. If there are less columns than the 6 (maximum number of supported columns), then the fields which are not needed should be empty, but if they are filled with "0", it's not a problem. Regarding the access file size, i guess it's better if the fields are empty.


    Quote Originally Posted by June7 View Post
    Which column header of the 9-column CSV should be ignored?
    The first 2 columns are the ones we use to build the key ID for each line, so they are needed but only to build the ID primary key.
    Columns #3, 4, 5, 6 are useless and columns whose header is either "CHECK" or "FOLD" are useless too.
    All the other columns should be imported. Their header starts with "BET*" "RAISE*" or "CALL".


    Quote Originally Posted by June7 View Post
    You want the column headers to be imported as data in first row? Right now they are just saved as a single string into the ID field and other fields have zero.
    Yes, that's the issue #1 tried to explain in the first post.
    For this line, the ID should be built like this : column1&"_"&column2. In the 4 examples I have attached, the ID for the header line should be "Flop_Hand".
    The other fields should not be filled with "0", for each imported column, the other fields should contain the header name, like for example "RAISE 30" or "BET 30" or "CALL".


    Quote Originally Posted by June7 View Post
    BTW, why bind form with no data entry controls to a table with no data?
    sorry, I don't understand this question. I don't know much about access and I think that even if I understood the question, I would have no idea what the answer is.


    Thank you for your help June7 !

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,566
    Okay, I am making progress. But still some questions.

    The number fields are created as integer type which means decimals are dropped - why not save the original value?

    So far I've gotten each CSV to import to table. Example below. I am still not clear what should be done with the column headers. Why would they not be used as field names? They can't be input as values in the ValeurX fields because those are number type.
    ID Valeur1 Valeur2 Valeur3 Valeur4 Valeur5 Valeur6
    +Flop+Hand+Weight OOP+OOP Equity+OOP EV+OOP EQR+CALL+FOLD+Flop+Hand+Weight OOP+OOP Equity+OOP EV+OOP EQR+CALL+FOLD+Flop+Hand+Weight OOP+OOP Equity+OOP EV+OOP EQR+CALL+FOLD+Flop+Hand+Weight OOP+OOP Equity+OOP EV+OOP EQR+RAISE 70+CALL+FOLD 0 0 0 0 0 0
    2h 2d 2c_KdKc 12 44 0 0 0 0
    2h 2d 2c_KhKc 12 44 0 0 0 0
    2h 2d 2c_KhKd 12 44 0 0 0 0

    That much was accomplished by changing the code at end of ListESY to:
    Code:
    For j = 1 To i - 1
        tempval = Split(vaArray(j), ".")
        strFile = vaArray(j)
        x = getData(GetDBPath1, strFile, tempval(0))
    Next
    As well as this line in CreateTable:
    no1 = InStr(RS.fields(i).Name, "BET") Or InStr(RS.fields(i).Name, "RAISE") Or InStr(RS.fields(i).Name, "CALL")

    And in getdataintotable:
    no2 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")


    You have a table named test in the db. The table is referenced in the form's RecordSource property - that means the form is bound to the table. However, there are no data controls (textbox, combobox, etc) to allow data entry.
    Last edited by June7; 09-24-2017 at 11:41 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    Okay, I am making progress. But still some questions.

    The number fields are created as integer type which means decimals are dropped - why not save the original value?
    The idea was to avoid getting huge access files because of useless decimals. So by to either rounding the original value as integer (but it seemed to slow down the process) or removing the "." and the 2 digits after the ".", the access file should be smaller.



    Quote Originally Posted by June7 View Post
    So far I've gotten each CSV to import to table. Example below. I am still not clear what should be done with the column headers. Why would they not be used as field names? They can't be input as values in the ValeurX fields because those are number type.
    The fields named Valeur1, Valeur2, ... Valeur6 should be text type in order to allow the import of the csv header.
    The csv header line should be imported like any other line.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,566
    Decimals really use that much storage? The code is not rounding numbers nor moving the decimal point, it is just dropping the decimal part. It is also setting the fields as number (integer) type, not text. I would set the unused fields to Null instead of saving 0. The values will be saved as text and don't think saving decimal part will really use that much more storage. If you want to save original value, code in red would be changed: strquery = strquery & rs1.fields(count1 - 1) & ",".

    Change dbInteger to dbText to create text type fields.

    Remove or comment following code from CreateTable (I show it commented):
    Code:
    'count = RS.fields.count
    'For i = 0 To count - 1
    '    fields = fields & "+" & RS.fields(i).Name
    '    no1 = InStr(RS.fields(i).Name, "BET") Or InStr(RS.fields(i).Name, "RAISE") Or InStr(RS.fields(i).Name, "CALL")
    '    If no1 > 0 Then
    '         count1 = count1 + 1
    '    End If
    'Next
    Revised getdataintotable:
    Code:
    Public Function getdataintotable(rs1 As ADODB.Recordset, tablename1 As String)
    Dim strquery As String
    Dim no1 As Integer
    Dim count1 As Integer
    Dim count2 As Integer
    Dim count3 As Integer
    Dim temp() As String
    Dim strval As StringstrValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "',"
    For i = 0 To rs1.fields.count - 1
        no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
        If no1 > 0 Then
            count1 = count1 + 1
            strFields = strFields & "Valeur" & count1 & ","
            strValues = strValues & "'" & rs1.fields(i).Name & "',"
        End If
    Next
    strFields = Left(strFields, Len(strFields) - 1)
    strValues = Left(strValues, Len(strValues) - 1)
    CurrentDb.Execute "Insert into [" & tablename1 & "](ID, " & strFields & ") VALUES(" & strValues & ")"
    count1 = 0
    count2 = 0
    rs1.MoveFirst
    Do While Not rs1.EOF
        strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0).Value & "_" & rs1.fields(1).Value & "',"
        For i = 0 To rs1.fields.count - 1
            count1 = count1 + 1
            no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
            If no1 > 0 Then
                count2 = count2 + 1
                strval = Str(rs1.fields(count1 - 1).Value)
                temp = Split(strval, ".")
                If temp(0) <> " " Then
                    strquery = strquery & CInt(temp(0)) & ","
                Else
                    strquery = strquery & CInt(strval) & ","
                End If
            End If
        Next
        
        strquery = Left(strquery, Len(Trim(strquery)) - 1)
        count2 = count2 + 1
        count3 = 7 - count2
        For j = 1 To count3
            strquery = strquery & ", Null"
        Next
        strquery = strquery & ")"
        CurrentDb.Execute strquery
        count1 = 0
        count2 = 0
        count3 = 0
        rs1.MoveNext
    Loop
    createdb (tablename1)
    End Function
    Again, the public variable fields is not needed and can be removed from the header. Also, remove the fields argument from call to getdataintotable.
    z = getdataintotable(RS, tablename)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by June7 View Post
    If you want to save original value, code in red would be changed: strquery = strquery & rs1.fields(count1 - 1) & ",".

    Change dbInteger to dbText to create text type fields.

    Remove or comment following code from CreateTable (I show it commented):
    Code:
    'count = RS.fields.count
    'For i = 0 To count - 1
    '    fields = fields & "+" & RS.fields(i).Name
    '    no1 = InStr(RS.fields(i).Name, "BET") Or InStr(RS.fields(i).Name, "RAISE") Or InStr(RS.fields(i).Name, "CALL")
    '    If no1 > 0 Then
    '         count1 = count1 + 1
    '    End If
    'Next
    Revised getdataintotable:
    Code:
    Public Function getdataintotable(rs1 As ADODB.Recordset, tablename1 As String)
    Dim strquery As String
    Dim no1 As Integer
    Dim count1 As Integer
    Dim count2 As Integer
    Dim count3 As Integer
    Dim temp() As String
    Dim strval As StringstrValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "',"
    For i = 0 To rs1.fields.count - 1
        no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
        If no1 > 0 Then
            count1 = count1 + 1
            strFields = strFields & "Valeur" & count1 & ","
            strValues = strValues & "'" & rs1.fields(i).Name & "',"
        End If
    Next
    strFields = Left(strFields, Len(strFields) - 1)
    strValues = Left(strValues, Len(strValues) - 1)
    CurrentDb.Execute "Insert into [" & tablename1 & "](ID, " & strFields & ") VALUES(" & strValues & ")"
    count1 = 0
    count2 = 0
    rs1.MoveFirst
    Do While Not rs1.EOF
        strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0).Value & "_" & rs1.fields(1).Value & "',"
        For i = 0 To rs1.fields.count - 1
            count1 = count1 + 1
            no1 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
            If no1 > 0 Then
                count2 = count2 + 1
                strval = Str(rs1.fields(count1 - 1).Value)
                temp = Split(strval, ".")
                If temp(0) <> " " Then
                    strquery = strquery & CInt(temp(0)) & ","
                Else
                    strquery = strquery & CInt(strval) & ","
                End If
            End If
        Next
        
        strquery = Left(strquery, Len(Trim(strquery)) - 1)
        count2 = count2 + 1
        count3 = 7 - count2
        For j = 1 To count3
            strquery = strquery & ", Null"
        Next
        strquery = strquery & ")"
        CurrentDb.Execute strquery
        count1 = 0
        count2 = 0
        count3 = 0
        rs1.MoveNext
    Loop
    createdb (tablename1)
    End Function
    Again, the public variable fields is not needed and can be removed from the header. Also, remove the fields argument from call to getdataintotable.
    z = getdataintotable(RS, tablename)
    Hi June7,

    I am sorry, but I am affraid I don't know how to do all this :-( I don't know anything about VBA and Access. I can copy and paste if I find the place where it's coded, but that is pretty much it.
    Does it work on your PC?
    Would you mind sharing the modified version please?

    Thank you for your help !

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,566
    I would rather you do the edits. They aren't that involved and I think instructions I provided are clear enough. I expected you would be capable of copy/paste which is why I posted entire procedure. Open VBA editor and read through the code. Isn't much there. Use Find to locate specific text. You can even do Find/Replace dbInteger > dbText. A lot like editing a Word document.

    Yes, code works for me.

    Reminder, add line: DoCmd.SetWarnings True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    ok, I'll try right now and I'll post here if something goes wrong.

    Does it process all the csv in a row on your PC?
    Or is this another code to add?

  13. #13
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    removed to make the thread easier to read (misunderstanding).

  14. #14
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Sorry, i can't find how to do this :

    Quote Originally Posted by June7 View Post
    Module header should have following two lines and the public variable fields is not needed in the header if suggested edits are implemented:
    Option Compare Database
    Option Explicit

  15. #15
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    removed to make the thread easier to read (misunderstanding).

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-22-2016, 02:37 AM
  2. Replies: 20
    Last Post: 10-13-2015, 09:05 AM
  3. Replies: 3
    Last Post: 10-16-2014, 08:49 AM
  4. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  5. Replies: 1
    Last Post: 05-04-2013, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums