Importing Specific Field Names From Excel into Access Table

[EzGoingKev]

I have an Excel file (XLSM) that has (498) fields. Row1 has field types. Row2 has the field names. Row3 is where the data starts.

I have an Access table that has (20) of those fields.

I am looking for a way to have the code skip the first two lines and import the data from the fields that match the field names in the table.

Is this possible?

 

[Pat Hartman]

For starters, Access can't handle a table with 498 fields. That means you are going to have to use OLE automation to open Excel and read through the workbook to create a table with just the columns you are interested in.

If you are comfortable with Excel, you can write a macro to create the slimmed down table in a separate sheet and link to that from Access.

 

[EzGoingKev]

For starters, Access can't handle a table with 498 fields. That means you are going to have to use OLE automation to open Excel and read through the workbook to create a table with just the columns you are interested in.

If you are comfortable with Excel, you can write a macro to create the slimmed down table in a separate sheet and link to that from Access.

I am not comfortable with Excel for anything complex. My goal is to not open the Excel file at all.

I know I can using SQL's INSERT INTO by manually listing the field names I want. Is there a way to automate something like that - instead of typing out all (20) field names code can select them from the table?

 

[plog]

Complexity has to exist somewhere.

You can manually do the complexity by a long intensive process of opening the Excel file, manipulating it such that it is ready for straight import into Access then doing the import within Access. Or you can write some complex code to do all of that for you such that you simply click a button and it executes. In either case, this is going to be complex for you.

I am not comfortable with Excel for anything complex. My goal is to not open the Excel file at all.

What are you comfortable with for anything complex? Python, R, VBA? It's gonna need to be one of those if you don't want to open the Excel file.
How often will you need to do this? If we are talking once a year or less, you just take the manual route. If it's everyday you start writing code.

 

[Gasman]

My goal is to not open the Excel file at all.

I think any file needs to be opened if you wish to see it's contents?

 

[CJ_London]

Do you know where these fields are out if the 498 columns? Providing they are grouped within 255 columns then should not be a problem

 

[EzGoingKev]

Do you know where these fields are out if the 498 columns? Providing they are grouped within 255 columns then should not be a problem

I know where they are right now.

The sheet is an export from a customer's system. For the stuff I need the names will be constant. The export is from customer's system. The customer is always adding/removing/moving fields around so I do not want to use a range.

 

[Uncle Gizmo]

If it's everyday you start writing code.

The other problem is where does the Excel file come from? If numerous co-workers are manually editing the Excel files then they are going to make mistakes! This means every time you get a new Excel file it might not have the same structure as the one you programmed against...

 

[GPGeorge]

"The customer is always adding/removing/moving fields around so I do not want to use a range."

You know what, in that context, you might be far ahead to have a heart-to-heart conversation with the client.

The current method, in which you are paid to futz with a constantly changing source file, costs the client X dollars (or pounds, or whatever currency you use) per day/week/month for the extra time you spend trying to extract the data. I assume you include all of the time needed to do so in your invoices to the client?

Now, if the client agrees to stop making the task more complex for you, i.e., if the client agrees to standardize on one usable format, they'll have to pay you for the time needed to code up an automated method. However, once it's automated -- and more importantly -- stable, they'll end up paying for less of your time going forward.

If the client prefers the Free Range approach to managing their data, then they need to cough up the appropriate ongoing Free Range costs.

 

[Edgar_]

You need VBA code there
1. Open Excel
2. Open Workbook
3. Find the data by referencing the sheet and range
4. Import what you need
5. Quit Excel

 

[MsAccessNL]

I don’t know much, but if you load the 20 fields into a dao recordset with a SELECT ., statement, wouldn’t it work? You can use dao sql on a $Sheetname. But may be it doesn’t work because the amount of fields? , but you can write vba to select the 20 fields/Columns before you load them into a dao recordset…

 

[CJ_London]

Your file sounds like something SAP would produce - very unfriendly for other systems.

Perhaps the client can put all the fields you require in the first few columns. Or you can use vba to scan the columns for the field names you require and note the columns number.

scanning code would be something like

dim rs as dao.recordset
dim sqlStr as string
dim i as integer
const fldNames ="customerID,customerName,InvoiceDate...."
dim fldStr as string 'or use an array/collection/whatever floats your boat



sqlStr="SELECT * FROM (SELECT * FROM [sheet1$A2:HZ2] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes])  AS XL"
set rs=currentdb.openrecordset sqlStr

for i=0 to rs.fields.count-1

    if fldNames like "*" & rs.fields(i).name & "*" then fldStr=fldStr & "," & i & ":" & rs.fields(i).name

next i

'check if all fields identified, if not, repeat for sheet1$IA2:SD2

once you have identified all the columns, check if the highest i value is no more than 255 greater than the lowest i value. If it is - you know your range so now you can construct your sql string to specify the column range and the fields required

If they are greater than 255 columns apart you will to add a unique ID column to the excel file - either repeat it for each range or place it somewhere in the middle of the required range - then run your append query twice.

 

[Pat Hartman]

I don’t know much, but if you load the 20 fields into a dao recordset with a SELECT ., statement, wouldn’t it work?

You only see the first 255 columns. If everything you need is to the left of that, then you can do the import without code.

If you can import the sheet directly into SQL Server which can handle that many columns, you can create a view that selects the columns you want and from Access link to the view. However, with column position a moving target, you would have to recreate your import each time the format changes. At least using VBA and opening the sheet with code, you can find the column name wherever it moved to so with VBA, you only have to worry about changing column names rather than moving columns.

It's worth spending some time with the client to see if they can standardize the sheet for you. Perhaps someone they have can create the macros to make the additional sheet you need that is formatted as a proper table and includes only the columns you need.