Import data from other files

[honkin]

hi

I have some code which has been working really well importing data from other files into a central file. Here is the code:

=IF(ISERROR(MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A3)*('VDW-November-2017.csv'!J$2:J$10000=D3),),0)),"",INDEX('VDW-November-2017.csv'!P$2:P$10000,MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A3)*('VDW-November-2017.csv'!J$2:J$10000=D3),),0)))

The code basically matches the date and name in both the source and destination files and then brings in the data in a particular cell. So depending on when I want the data for, I simply change the filename, column and/or row references and the data is imported. I then copy and paste the data down the whole column it is required in. Once that is done, I highlight it all and copy, then paste special and choose paste values. This removes the link to the source file, as I only require the data.

All of the data I have imported has gone into one single file, but I now have need to start a new file which will be home to a greater amount of data; 5 years all up, so there are very many more rows; 120,000 or so, compared to around 5,000 in the other central file. Due to this file being so large, I have made it an .xlsx file.

Yesterday I tried to import the first lot of data into this file and was greeted with nothing; a blank cell. Here is the code:

=IF(ISERROR(MATCH(1,INDEX((INT('January-2017.csv'!A$2:A$10000)=A102957)*('January-2017.csv'!L$2:L$10000=K102957),),0)),"",INDEX('January-2017.csv'!D$2:D$10000,MATCH(1,INDEX((INT('January-2017.csv'!A$2:A$10000)=A102957)*('January-2017.csv'!L$2:L$10000=K102957),),0)))

The date is in column A in both source and destination and the name is in column L in the source and column K in the destination. The data I want is in column D

As I indicated, the new file is an .xlsx file and the source data is a .csv file. Is there some issue here which would stop the data from being input? If I try and bring the same data into the original .csv file I was using, it imports fine.

The dates and names match, so I can't see what is possibly stopping the import, unless it is the format of the files.

I did just try one last thing. I save the new .xlsx file as a .csv file and then tried the data import; still showing a blank cell.

Any suggestions on what I might be doing wrong?

cheers

 

[honkin]

If it helps, when the source file is closed, the full location of the file is revealed, so maybe there is something in that, though I doubt it. Just trying to give as much information as possible

=IF(ISERROR(MATCH(1,INDEX((INT('/Volumes/DOCUMENTS/My Documents/Horse/Football Advisor/Research/[January.csv]January'!A$2:A$10000)=A2)*('/Volumes/DOCUMENTS/My Documents/Horse/Football Advisor/Research/[January.csv]January'!L$2:L$10000=L2),),0)),"",INDEX('/Volumes/DOCUMENTS/My Documents/Horse/Football Advisor/Research/[January.csv]January'!D$2:D$10000,MATCH(1,INDEX((INT('/Volumes/DOCUMENTS/My Documents/Horse/Football Advisor/Research/[January.csv]January'!A$2:A$10000)=A2)*('/Volumes/DOCUMENTS/My Documents/Horse/Football Advisor/Research/[January.csv]January'!L$2:L$10000=L2),),0)))

As indicated previously, the date is in column A, source A5, destination A2. The name is in column L; source L5, destination L2. The required data is in column D; D5.

What does seem odd is this; when you normally close the source file, you get the longer code above, when you open it again, it reverts to the shortened version. But when I opened the source file again, nothing changed, indicating that the link is not working correctly. This is something I have no idea how to fix.

Has anyone experienced this sort of behaviour before and what was the workaround to have the file linked correctly?

Thanks in advance

 

[honkin]

OK, tried the most obvious test of the lot. Importing data from a source file which I have already used to import data to my original working file.

=IF(ISERROR(MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A3)*('VDW-November-2017.csv'!J$2:J$10000=D3),),0)),"",INDEX('VDW-November-2017.csv'!P$2:P$10000,MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A3)*('VDW-November-2017.csv'!J$2:J$10000=D3),),0)))

This results in the data being imported to my original working file without issue

Now to the new destination file

=IF(ISERROR(MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A107924)*('VDW-November-2017.csv'!J$2:J$10000=K107924),),0)),"",INDEX('VDW-November-2017.csv'!P$2:P$10000,MATCH(1,INDEX((INT('VDW-November-2017.csv'!A$2:A$10000)=A107924)*('VDW-November-2017.csv'!J$2:J$10000=K107924),),0)))

Nothing happens, yet the source file location is the same as are the source column references. Only the destination references have changed

What could possibly be causing it?

cheers