Results 1 to 24 of 24

Thread: Import file using mapping table

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Import file using mapping table

    Hi,

    I wish to import a file from a particular drive but instead of importing the whole file i want to only import the rows that are contained in my mapping table.

    The unique key in the mapping table and the file is site Id. Mapping table stored on Control tab and defined as Mapping_table.

    I want to import the headers as well and paste into a worksheet tab called H&S (current file open)

    Import file from path w:\.team documents\freehold team\freeholders managers\reporting\reports\Hs Assigned tasks - Boss.

    Worksheet contained in that file to import. HS Assigned tasks - Boss. Site id is in column A on the file I wish to import from.
    Attached Images Attached Images   

  2. #2

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Hi Westconn.

    Any ideas on how I can achieve the below?

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    Any ideas on how I can achieve the below?
    No, too many unspecified options as to what you might want

    would you just want the items in the table only if they match consecutive entries in main data
    or
    would you want all entries from the list that match each id in the table
    if not how would you know which entry you would want
    or
    would want one entry for each item in the table
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Hi,

    So basically.

    Example

    I want the macro to look for the first id in the mapping table on sheet.control. In this case AA. I have defined the range of the mapping table called mapping_table.

    Look for this ID in Column A in the specified file. I.e HS assigned tasks.

    Once found (Row 2) that ID in column A. Import all the information contained in that row and paste into tab H&S.

    I also want to import the headers and keep the formats the same as the specified file (HS assigned tasks)

    The specified file is saved in w:\.team documents\freehold team\freeholders managers\reporting\reports\Hs Assigned tasks - Boss.

    Hope this makes sense.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    Once found (Row 2) that ID in column A
    but when you get to FF do you just want the first row found, or all rows found assuming in the actual data there could be many more than 2 rows with the same ID
    as there is a 2nd FF do you want to import the first one again, or all, or what, do you need to keep a record of rows already copied so you only import once

    you need to be more clear about the object of this exercise
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Hello,

    Very good point you have raised in relation to duplicates.

    In terms of FF. When we get to FF - I want that row to be imported and all then proceed to the next row. If that row contains FF then import that row as well.

    So in other words. Import all rows if the unique ID in Col A is contained in the mapping table. So in the example I have provided I would expect to see 2 rows with the unique id
    FF. The same with DFDF.

    Is this ok?

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    post a sample workbook for me to test against
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Sure. How do I attach a workbook? thought I could only post images?

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    Sure. How do I attach a workbook?
    zip first then attach to post
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Here you go.

    I've submitted the workbook i posted earlier.


    HS Assinged tasks.zip

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    here is some code i tested, also using sql, so needs reference to ADO, appears to work as required, though i may be muddled as to where you want to data copied to

    Code:
    Dim cn As Connection, rs As Recordset
    Dim c As Collection
    Set cn = New Connection
    Set rs = New Recordset
    
    myxls = "w:\.team documents\freehold team\freeholders managers\reporting\reports\Hs Assigned tasks - Boss.xlsx"
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & myxls & ";" & _
                "Extended properties='Excel 12.0; header  = yes'"
                .Open
            End With
            Set c = New Collection
            rs.Open "select * from [HS Assigned tasks - Boss$];", cn, adOpenStatic, adLockOptimistic
            
            
    For Each cel In Sheets("control").Range("e8:E15").Cells 'mapping table
        rs.Filter = ""
        rs.Filter = "[" & rs.Fields(0).Name & "]='" & cel & "'"
        On Error Resume Next
        c.Add cel.Value, cel
        If Err.Number = 0 Then
            If cel.Address = Range("e8").Address Then
                For fld = 0 To rs.Fields.Count
                    Sheets("master").Range("a1").Offset(, fld) = rs.Fields(fld).Name
                Next
            End If
            Sheets("master").Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset rs
            Else
            Err.Clear
        End If
    Next
    cn.Close
    assumes the code is in the same workbook as sheets control and master
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Dim cn As Connection,

    Hit a compile error on the first line. User-defined type not defined.


    What do you mean by referencing to ADO?



    I want the data copied to a sheet called H&S Assigned which sits in the same workbook as sheet.master

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    What do you mean by referencing to ADO
    you need to add a reference to microsoft activex data objects

    menu > tools > references
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Re: Import file using mapping table

    Thank you for your updates.

  15. #15

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    I've changed the code where it says myxls to mycsv as the actual file I need to open is in CSV format.

    The code is breaking on .open.

    Error message says the 'External table is not in the expected format' Is this because I have changed the format?
    Last edited by kris01; Nov 30th, 2020 at 08:13 AM.

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    myxls to mycsv
    the name of the variable is irrelevant, but the code for working with excel is different to xlsx, what happens when you don't specify enough details

    The code is breaking on .open.
    check out your other thread, i posted options for both csv and xlsx in that thread, when you posted some sample data in a workbook, when you originally specified a .csv there, you should be able to convert the examples
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    ok this is my code now and switched the code to csv.

    Getting a different error message on this line now. - Could not find installable ISAM

    .Open

    Dim cn As Connection, rs As Recordset
    Dim C As Collection
    Set cn = New Connection
    Set rs = New Recordset

    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports\Hs Assigned Tasks - BOSS.csv"
    With cn
    .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
    "Extended properties='txt;hdr=no;fmt=csvdelimited'"
    .Open
    End With
    Set C = New Collection
    rs.Open "select * from [Hs Assigned Tasks - BOSS$];", cn, adOpenStatic, adLockOptimistic


    For Each cel In Sheets("Control").Range("J8:J93 ").Cells 'Mapping'"
    rs.Filter = ""
    rs.Filter = "[" & rs.Fields(0).Name & "]='" & cel & "'"
    On Error Resume Next
    C.Add cel.Value, cel
    If Err.Number = 0 Then
    If cel.Address = Range("e8").Address Then
    For fld = 0 To rs.Fields.Count
    Sheets("sheet1").Range("a1").Offset(, fld) = rs.Fields(fld).Name
    Next
    End If
    Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset rs
    Else
    Err.Clear
    End If

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    Could not find installable ISAM
    just means some part of the code is not correct

    you need to remove the name of the .csv file from the path, the .csv file is used for the name of the table, not the database
    with text files (includes .csv) the database name is the folder name
    unlike excel worksheets the .csv file names (table names) do not have a tailing $, but do have the .csv extension, sample in the other post

    again, if you post a correct sample file i will retest
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Hello,

    I've included two files in my zip attachment.

    1. Sample. Master file that requires the csv to be imported using the mapping table on the control tab.
    2. HS Assigned tasks.csv. CSV file that needs to be imported.

    Please import the csv file and copy onto the master tab.

    Let me know the new code.HS Assigned Tasks.zip

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    tested sample
    Code:
    Dim cn As Connection, rs As Recordset
    Dim c As Collection
    Set cn = New Connection
    Set rs = New Recordset
    mypath = "w:\.team documents\freehold team\freeholders managers\reporting\reports\"
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
                "Extended properties='text;hdr=yes;fmt=csvdelimited'"
                .Open
            End With
            Set c = New Collection
            rs.Open "select * from [HS Assigned Tasks.csv];", cn, adOpenStatic, adLockOptimistic
    For Each cel In Sheets("control").Range("e8:E15").Cells 'mapping table
        rs.Filter = ""
        rs.Filter = "[" & rs.Fields(0).Name & "]='" & cel & "'"
            On Error Resume Next
        c.Add cel.Value, cel
        If Err.Number = 0 Then
            If cel.Address = Range("e8").Address Then
                For fld = 0 To rs.Fields.Count
                    Sheets("master").Range("a1").Offset(, fld) = rs.Fields(fld).Name
                Next
            End If
            Sheets("master").Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset rs
            Else
            Err.Clear
        End If
    Next
    rs.Close
    cn.Close
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Hi,

    I'm using the exact code as you tested but still getting the

    Could not find installable ISAM. error message. Breaks on the .open line.

    As the code worked for you, but not for me.. could this be a set up issue? Some reference I need to install??

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    check your path and file names

    try copying the .csv to a local folder (as i am doing here) for testing, i do not like your share path, it may not be an issue at all, but it would be good to confirm there is no problem with that
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Import file using mapping table

    Ok. Not sure if the shared path is the problem. The path is the exactly the same as the other thread, so i know the path is ok.

    Saying that.... I moved the csv file to the - .Team Documents folder and I've got the same problem.

    Does this mean we have an actual problem with the CSV?

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,183

    Re: Import file using mapping table

    Does this mean we have an actual problem with the CSV?
    no idea, i can only test with what you post, it worked with the file i have here

    as i do not know what it took to fix the problems in the other thread, i can not really make any further suggestions
    Last edited by westconn1; Dec 9th, 2020 at 03:14 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width