Linked Table - Excel - Blank Column A (2 Viewers)

[razorking]

This is a weird one - may not be a great solution to it - wondering if anyone has any insight into this..

I have a database that uses an Excel file as a linked table. I have to use the Excel file as a data source as this information is sent from a vendor to us. The Excel file has column A that has nothing in it, ever. I'm probably not going to be able to get the vendor to do anything about that.

I have queries built upon the linked table. The practice has been to receive the email with the Excel file and to place the Excel file in a specific location and name the file the same so Access can utilize the link. For some reason (that I don't quite understand) sometimes the link table includes the blank column A and other times it does not. This, of course, is playing havoc with the queries as the data is shifting around based on if column A is there or not there. It also seems to be the case that if one person opens the database then the linked table might include column A but if another person opens it then the linked table might not have column A.

As a workaround I am asking the user that receives the Excel file to open it and put something (anything) into any cell in column A and then save the file to the location where it lives.

Maybe I just need to change it from a link to an import and use macros or VBA to import the data when clicking a button, etc.

Wondering if anyone has dealt with this before.

 

[GPGeorge]

What I would pursue as a solution is to import the the Excel data into a temp table in Access, regardless of what columns are or are not present on any given occasion. Then, in VBA, you can inspect the columns in the temp table and append only the valid values from valid columns into a production table from which further processing is done.

 

[razorking]

What I would pursue as a solution is to import the the Excel data into a temp table in Access, regardless of what columns are or are not present on any given occasion. Then, in VBA, you can inspect the columns in the temp table and append only the valid values from valid columns into a production table from which further processing is done.

Yes, that's probably what I am going to have to do. Thanks!

 

[Gasman]

Why not just delete column A ?

 

[CJ_London]

Or specify the columns you actually want

 

[razorking]

Or specify the columns you actually want

On the link it does not give me an option to specify columns. Import does.

 

[razorking]

Why not just delete column A ?

We can delete column A. The hope was to just save the file from the email to the specified location and have no other intervention. Otherwise it requires opening the file each time and then deleting the column and saving it to the location. It's one extra step but it's annoying.

 

[Gasman]

TBH, I would be asking the sender to omit the column.
Makes no sense sending an empty column. and a real PITA when they do and then they do not.
That would drive me around the bend.

Amazon allow me to go to my account and see my orders. yet when I click on a particular order to see the details, I have to log in?
Talk about shutting the barn door after the horse has bolted.

I suppose you could have two queries, one for nothing in column A and one with something in column A.
You check column A, and copy the respective query template as the base query template.

Then work from that base template?

 

[mike60smart]

We can delete column A. The hope was to just save the file from the email to the specified location and have no other intervention. Otherwise it requires opening the file each time and then deleting the column and saving it to the location. It's one extra step but it's annoying.

I would have thought an Import into a temp table and then create a query based on the Temp table where you only select the fields required
would sort this|?

 

[ebs17]

Do the columns in the Excel table have column headings? In linked tables and in queries, fields are addressed by their names, not by their position in the column enumeration.
The professional doesn't work with "SELECT *" anyway, but for the sake of efficiency you limit yourself to the required fields.

 

[Pat Hartman]

We don't know what the spreadsheet is for so we have no idea how much juice you might have with getting the ventor to make it consistent.

It is a lot of work for you to figure out on the fly how to handle the spreadsheet when the columns can be variable. It is probably important though to figure out WHY the columns are variable before deciding on how to handle the situation.

The practice has been to receive the email with the Excel file and to place the Excel file in a specific location and name the file the same so Access can utilize the link.

The naming part is not actually necessary. What I do is to have the new file downloaded to a standard folder. Each file I get has a different name and that is what I want. Typically, the name is standard with a date suffix and typically, I'll get one file at a time but it doesn't actually matter.
When I import files from foreign sources, I always create a logging mechanism. In the instructions below, when a workbook is selected, the path and file name are logged and a "batchID" is assigned. When I run an append query, I include the batchID so every record appended from any given file gets the same batchID. So if the generated batchID (usually an autonumber) is 823, then all 429 rows have the value 823 in the batchID column. This provides an easy way to audit the files or to back out an import if necessary.

WHEN THE COLUMNS ARE FIXED:
Method 1 - If you want the user to import the file specifically
You give him a common dialog interface so he can pick the file from the directory and press import. The import links the spreadsheet to the standard table name. It makes no difference whether or not the file name is consistent. What matters is that the linked table name is consistent and the columns are consistent. You then have an append query that ignores ColumnA and appends the other columns plus the BatchID. The user then imports additional files if there are any and as the final step, moves all files imported to the archive folder leaving the input folder empty.
Method 2 - if you want to automate the import
You have a table that defines the standard file path for the input file so instead of providing a file dialog to select one file at a time, you use FSO (File System Object) to select all files in the folder, one at a time and process them. You link the first filename, log the name and create the batchID and then run the append query with the included BatchID. Then move the file from the input folder to the standard archive folder (name also found in the paths table). Loop to the next file and continue.

WHEN THE COLUMN NAMES ARE NOT FIXED:
You still have the two methods - manual and automated but before you can actually import anything, you need to use OLE to open the spreadsheet with VBA and examine the column names in row 1. You determine if the required columns are present - you should have a table that contains only the desired columns and mapped to column names if they are different. Then you manually build the append query and that is what you use in the above two methods where is says to run the append query.

 

[razorking]

We don't know what the spreadsheet is for so we have no idea how much juice you might have with getting the ventor to make it consistent.

It is a lot of work for you to figure out on the fly how to handle the spreadsheet when the columns can be variable. It is probably important though to figure out WHY the columns are variable before deciding on how to handle the situation.

The naming part is not actually necessary. What I do is to have the new file downloaded to a standard folder. Each file I get has a different name and that is what I want. Typically, the name is standard with a date suffix and typically, I'll get one file at a time but it doesn't actually matter.
When I import files from foreign sources, I always create a logging mechanism. In the instructions below, when a workbook is selected, the path and file name are logged and a "batchID" is assigned. When I run an append query, I include the batchID so every record appended from any given file gets the same batchID. So if the generated batchID (usually an autonumber) is 823, then all 429 rows have the value 823 in the batchID column. This provides an easy way to audit the files or to back out an import if necessary.

WHEN THE COLUMNS ARE FIXED:
Method 1 - If you want the user to import the file specifically
You give him a common dialog interface so he can pick the file from the directory and press import. The import links the spreadsheet to the standard table name. It makes no difference whether or not the file name is consistent. What matters is that the linked table name is consistent and the columns are consistent. You then have an append query that ignores ColumnA and appends the other columns plus the BatchID. The user then imports additional files if there are any and as the final step, moves all files imported to the archive folder leaving the input folder empty.
Method 2 - if you want to automate the import
You have a table that defines the standard file path for the input file so instead of providing a file dialog to select one file at a time, you use FSO (File System Object) to select all files in the folder, one at a time and process them. You link the first filename, log the name and create the batchID and then run the append query with the included BatchID. Then move the file from the input folder to the standard archive folder (name also found in the paths table). Loop to the next file and continue.

WHEN THE COLUMN NAMES ARE NOT FIXED:
You still have the two methods - manual and automated but before you can actually import anything, you need to use OLE to open the spreadsheet with VBA and examine the column names in row 1. You determine if the required columns are present - you should have a table that contains only the desired columns and mapped to column names if they are different. Then you manually build the append query and that is what you use in the above two methods where is says to run the append query.

Thanks Pat! Very interesting. I will take some time and see what I can modify to incorporate the the information that you have provided into the process.

 

[Pat Hartman]

You're welcome. Please try to get clarification on WHY the spreadsheet varies before trying to work around it because the reason might be important.

 

[gemma-the-husky]

Are you definitely receiving an excel file, and not a csv file?. Csv files are easier to manage.

 

[CJ_London]

On the link it does not give me an option to specify columns. Import does.

you can use sql query instead of a linked table - modify sheet, path and filename to suit - the below assumes headers on the top row and you only want columns B, C & D but you can start on a different row if required, just specify the range

SELECT *
FROM (SELECT * FROM [sheet1$B:D] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS XL;

or

SELECT *
FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=C:\Path\filename.XLSX].[Sheet1$B:D] AS XL;

 

[razorking]

Are you definitely receiving an excel file, and not a csv file?. Csv files are easier to manage.

It is an .xlsx file

 

[gemma-the-husky]

I'm not sure I've seen that behaviour but as far as access is concerned the row order and column order is unimportant, so it may appear in the table with a different order.

If you have column headers then you could check that all the columns you expect to see are present. If you don't then it's harder. Also access will impute column types from the first few rows, which can also cause problems. I have an issue with data where a column is mainly numeric, but occasionally has a letter suffix and therefore has to be treated as a text field. Even when i import to an existing table with a text field access still determines the XL.data is numeric, and deletes offending items.

If you have to inspect the XLS first it defeats the object somewhat.

 

[razorking]

You're welcome. Please try to get clarification on WHY the spreadsheet varies before trying to work around it because the reason might be important.

It's a weird situation that I don't understand. When we open the file it has an A column that is compressed to the left to where you don't even notice it when you open the file. I'm sure that I was not aware of it when I first linked it to an Access table. Btw - the data is shipment information - items, quantities shipment dates, etc. After I first linked the table I then built queries off of it. When I linked it in Access the columns come in as F1, F2, F3, etc. That's fine as I just know that F2 is ship-from location and F3 is item# or whatever. So - a few queries were constructed joining on other tables and what have you. Everything worked fine for 2-3 months. Then one day I was notified that the queries were producing no data. It took me awhile to realize that the reason was that the columns had shifted and that the A column was sometimes present in the linked table and other times not. Btw, as far as I can tell, visually, column A never has any data in any cell.

Additionally, this may sound weird but I am pretty sure that in some cases person A can open the linked table in the database and column A is present and then person B can open the linked table and column A is not present. And that is using the same source file without any changes to it.

 

[gemma-the-husky]

That's another reason I don't like linking to data. I would much rather import the raw data file into my database, and be sure that nobody interferes/edits the data. It must be very awkward if there's no column headers.

 

[Pat Hartman]

Have you asked the vendor WHY? Maybe he can instruct his people in the proper construction of the spreadsheet if the difference is caused by carelessness.