Importing Excel Into Access (1 Viewer)

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
I am importing two spreadsheets into access for comparison. I am using this to import the spreadsheets as I do not know how many columns the workbook will have.

Code:
DoCmd.TransferSpreadsheet

Sometimes the workbook will have 20 columns of data, while others it has 8. The only thing that is constant is the column headers that exist in the workbook.
The issue I face is when comparing the two tables, table1.userID will import as a short text, but table2.userID will import as an int so the query fails since they are different data types that I am trying to join on.

What would be my best way to combat this?
 

Isaac

Lifelong Learner
Local time
Today, 02:30
Joined
Mar 14, 2017
Messages
8,738
Sometimes the workbook will have 20 columns of data, while others it has 8. The only thing that is constant is the column headers that exist in the workbook.
Those two sentences contradict each other
 

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
Those two sentences contradict each other

Sorry, did not mean to contradict.

The constant between different iterations of the workbook are workbook 1 will ALWAYS have headers, name, address, city, state, zip, orderID, userID
and workbook 2 will always have userID, orderId, managerID, orderstatus

Plus multiple other columns that do not matter to anything in this process.
 

GinaWhipp

AWF VIP
Local time
Today, 05:30
Joined
Jun 21, 2011
Messages
5,901
Hmm, I would import them into a Temp tables first and then push to the Live tables. This should also help you identify why the User ID comes in two different ways.
 

Anakardian

Registered User.
Local time
Today, 10:30
Joined
Mar 14, 2010
Messages
173
One explanation could be that when it imports the spreadsheeet, it takes a sample of the first 10 or so rows to determine the datatype.
If on of the useridd iss not a number in one of the sheets, it willl do exactly that.

It can be something as trivial as a trailing space that messses it up.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:30
Joined
Sep 21, 2011
Messages
14,050
If it is just for comparison, can't you just link them? :unsure:

Not sure how it would handle changing columns though?, I have never had that issue.?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2013
Messages
16,553
use a non standard join or a cartesian query (no joins)

e.g.

non standard join (create in the query builder, then go to the sql view to change the join)

SELECT *
FROM Excel1 INNER JOIN Excel2 ON clng(Excel1.ID)=Excel2.ID

cartesian (create in the query builder)
SELECT *
FROM Excel1, Excel2
WHERE clng(Excel1.ID)=Excel2.ID
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:30
Joined
Feb 19, 2002
Messages
42,981
Sadly, Access and Excel are in collusion. Therefore, they won't allow you to specify data types on import. If you can't make the data types consistent, you can do it though OLE automation. Using VBA, open the workbook that needs fixing to the sheet you want to adjust. Change the data type and save the workbook and close it. Then link the two workbooks. Do not import them. That just makes the database bloat
 

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
Hmm, I would import them into a Temp tables first and then push to the Live tables. This should also help you identify why the User ID comes in two different ways.

AH, I didn't think about this route. Sounds quick and easy, and I can then use VBA to change the DataType of userid in the temp table before pushing to the main tables.
 

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
use a non standard join or a cartesian query (no joins)

e.g.

non standard join (create in the query builder, then go to the sql view to change the join)

SELECT *
FROM Excel1 INNER JOIN Excel2 ON clng(Excel1.ID)=Excel2.ID

cartesian (create in the query builder)
SELECT *
FROM Excel1, Excel2
WHERE clng(Excel1.ID)=Excel2.ID

I've never done a Cartesian query, I'll have to look that up.

What would be sample syntax on an update statement using a Cartesian query?
 
Last edited:

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
Sadly, Access and Excel are in collusion. Therefore, they won't allow you to specify data types on import. If you can't make the data types consistent, you can do it though OLE automation. Using VBA, open the workbook that needs fixing to the sheet you want to adjust. Change the data type and save the workbook and close it. Then link the two workbooks. Do not import them. That just makes the database bloat

I have verified before import by right clicking the column and selecting format cells and selecting Text that in each workbook the column type is set to text. Access must not like some of the data in the column.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2013
Messages
16,553
I have verified before import by right clicking the column and selecting format cells
you need to understand that format is just a property - how you see the data. Formats are generally not imported, only the underlying data

What would be sample syntax on an update statement using a Cartesian query?
Your original question was about comparing data so depends on what you are updating (or do you mean appending?)- and may not work.
 

GC2010

Registered User.
Local time
Today, 02:30
Joined
Jun 3, 2019
Messages
120
@CJ_London - apology, I am getting ahead of myself here.

Let me get a sample datas together and post here so we can all see full picture
 

GinaWhipp

AWF VIP
Local time
Today, 05:30
Joined
Jun 21, 2011
Messages
5,901
AH, I didn't think about this route. Sounds quick and easy, and I can then use VBA to change the DataType of userid in the temp table before pushing to the main tables.
Correct.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Sep 12, 2006
Messages
15,614
So some imports files have a subset of the column names, but the column names are the same in all cases?

I would first check the column names you import to ensure you have the appropriate columns to deal with the import.
Definitely import to a temporary table first, and then take it from there.

If you can persuade everyone to adopt a standard set of columns, that might be easier still.

You also want to beware of unacceptable data. eg - You might have a date column, and those pesky users will include something that is rejected as a date. Make sure the data is clean and validated before you use it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:30
Joined
Feb 19, 2002
Messages
42,981
Select all the columns in the spreadsheet and change the type to text. If a column contains ONLY numeric values, Access may still change it to a numeric data type. I don't remember. If the type is General, Access will use the first 30 or so rows and assign a data type based on what is there.

You can force the issue by making the second row a dummy and filling all the columns with an x. That will convince Access to make all the columns text, including dates. You can remove the x row after the import or in the append query if that is what you are using.
 

Users who are viewing this thread

Top Bottom