-
Nov 20th, 2020, 07:27 AM
#1
Thread Starter
Addicted Member
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.
-
Nov 25th, 2020, 04:55 AM
#2
Thread Starter
Addicted Member
Re: Import file using mapping table
Hi Westconn.
Any ideas on how I can achieve the below?
-
Nov 25th, 2020, 05:17 AM
#3
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
-
Nov 25th, 2020, 07:18 AM
#4
Thread Starter
Addicted Member
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.
-
Nov 25th, 2020, 03:20 PM
#5
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
-
Nov 26th, 2020, 04:59 AM
#6
Thread Starter
Addicted Member
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?
-
Nov 26th, 2020, 05:48 AM
#7
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
-
Nov 26th, 2020, 05:59 AM
#8
Thread Starter
Addicted Member
Re: Import file using mapping table
Sure. How do I attach a workbook? thought I could only post images?
-
Nov 26th, 2020, 06:19 AM
#9
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
-
Nov 26th, 2020, 06:40 AM
#10
Thread Starter
Addicted Member
Re: Import file using mapping table
Here you go.
I've submitted the workbook i posted earlier.
HS Assinged tasks.zip
-
Nov 27th, 2020, 06:20 AM
#11
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
-
Nov 27th, 2020, 09:34 AM
#12
Thread Starter
Addicted Member
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
-
Nov 27th, 2020, 03:53 PM
#13
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
-
Nov 29th, 2020, 02:27 PM
#14
Banned
Re: Import file using mapping table
Thank you for your updates.
-
Nov 30th, 2020, 07:20 AM
#15
Thread Starter
Addicted Member
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.
-
Dec 1st, 2020, 04:12 AM
#16
Re: Import file using mapping table
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
-
Dec 1st, 2020, 07:09 AM
#17
Thread Starter
Addicted Member
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
-
Dec 4th, 2020, 05:16 AM
#18
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
-
Dec 8th, 2020, 08:41 AM
#19
Thread Starter
Addicted Member
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
-
Dec 9th, 2020, 03:46 AM
#20
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
-
Dec 9th, 2020, 05:14 AM
#21
Thread Starter
Addicted Member
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??
-
Dec 9th, 2020, 05:28 AM
#22
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
-
Dec 9th, 2020, 09:25 AM
#23
Thread Starter
Addicted Member
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?
-
Dec 9th, 2020, 03:05 PM
#24
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|