UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Linking To An Excel Spreadsheet But Only To Certain Columns, Access 2013    
 
   
dflak
post Feb 23 2018, 03:08 PM
Post#1


Utter Access VIP
Posts: 6,202
Joined: 22-June 04
From: North Carolina


First the big picture. People email us reports. I know that these reports were probably generated by a database somewhere, but we get the human-readable version and I either screen scrape or manipulate the downloaded attachments and put them into excel tables.

The project has grown and new requirements come in every other day. So I have three huge reports and within these reports is code to create the data from the sources. I also have some stand-alone programs to pull data from sources and then copy and paste the results into several different reports.

In other words I have bits and pieces of code scattered all over my project to pull data from various sources and then copy and paste from one workbook to another when the workbooks use the same data.

Evolution sometimes produces dinosaurs like this.

So I decided that I would extract all these data gathering programs into stand-alone programs that produce the excel tables I need, and then link access tables to these excel tables. Then I have all my data in one place and can access it from whatever reports need whatever pieces and whatever new reports crop up next week.

Every Excel table has two additional fields at the very end: Composite and Duplicates. The source data introduce duplicates (and I have to decide which duplicate is valid, but that’s another story). So I do a duplicates check to remove them.

I don’t need these two fields in my database. I was wondering if there were a way not to link to them. Most of what I read so far about fixing this imports the data rather than linking it. This sounds like it might be a slower and definitely more complicated process than just linking to the excel table and its extra baggage.

This is not an issue that really needs to be solved. I suspect it can’t be solved, and I can live with it. I was just wondering if someone knew something I didn’t.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
geomark
post Feb 23 2018, 04:37 PM
Post#2



Posts: 14
Joined: 1-November 02



In certain cases, I will do this using a stored Query against a source file rather than creating an Attached Table. Using SQL, you can pluck the desired columns from a file rather than all of them.

Here are a few examples of the SQL statements behind a direct query to a CSV and an XLS:

SELECT id, name FROM [counties#csv] IN '' [Text;FMT=Delimited;HDR=YES;CharacterSet=437;DATABASE=C:\localdev\test];

In the CSV example, the CSV file is named counties.csv and the DATABASE is the path to the file.


SELECT [IBD$].F1 AS Symbol, [IBD$].F2 AS CompanyName, [IBD$].F13 AS IndustryGroupName FROM [Excel 8.0;IMEX=1;Hdr=No;Database=F:\Access\markettrack\IBD\IBD 7-03-06.xls;].[IBD$];

In the XLS example, the DATABASE is the full path, including the filename, and IBD is the Worksheet name.


Play around with it - perhaps it will help you accomplish what you are looking to do.

G
Go to the top of the page
 
dflak
post Feb 23 2018, 04:58 PM
Post#3


Utter Access VIP
Posts: 6,202
Joined: 22-June 04
From: North Carolina


This is very similar to what I had read. I think I will implement it mainly to learn how to do it. The issue is that I have a dozen tables and I'd have to program each. While in this case, I think I can afford to be lazy and just link and accept the two extra columns (they don't hurt much), I can see where I might need this someday with much "wider" tables.

Thanks for the response.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
tina t
post Feb 23 2018, 09:13 PM
Post#4



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
I don’t need these two fields in my database. I was wondering if there were a way not to link to them.

hello, Dan, i'm wondering if this makes a difference. it's a link, after all - the data is never actually in the Access database. and even if you write the link into a query, you're still linking to the entire text file, or worksheet, even though only displaying specific columns in the query's view.

it seems like six of one, half dozen of the other, to me - so i'm also wondering if i'm missing something. is there some overhead associated with each column in a linked file? and being able to not include that column would save space, bandwidth, whatever? i'm seriously asking, because there's a whole lot i don't know, and i'd like to learn what i can.

tia, tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
dflak
post Feb 26 2018, 04:10 PM
Post#5


Utter Access VIP
Posts: 6,202
Joined: 22-June 04
From: North Carolina


There never was much of an issue other than some minor "clutter." I'm doing just fine with the way things are. This sure beats copy and paste form scattered workbooks into scattered workbooks. Fortunately the project is still early enough that I can remember where the data are used.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 10:24 AM