Import Spreadsheet missing field

[cspyro]

I am having an issue when I attempt to Import an Excel spreadsheet into
Access. There are 18 columns and the right-most column is a free-form
text/memo field conatining an audit trail of user inputs (ie dates and text).
When I attempt to Import the spreadsheet, this column does not appear in the
Import Spreadsheet Wizard (I only see Fields 1-17).

 

[Jerry Whittle]

Instead of importing, try linking to the spreadsheet. See if that shows all
the columns. If so you could then create a make table or append query to put
records from the spreadsheet into a table.

 

[cspyro]

I did try to Link the spreadsheet as well and the same thing happens. What I
am actually looking to do is build a database that will prompt the user to
select an Excel file daily which will be run through Access to create an
identical output file for Export back to Excel with some aggregation logic
applied. This entire process is intended to apply logic that cannot be
captured in the firm's proprietary record matching engine. A secondary issue
to the above is the 255-character field limitation. Is there any way around
this using the TransferSpreadsheet method?

 

[Pete D.]

Have you named the range and is it included in the print area for the
spreadsheet? Also does the first few rows include data or are they blank?
Correct these and let us know.

 

[a a r o n . k e m p f]

if Access isnt' robust enough for you-- then I would reccomend moving
to SQL Server and learning a real ETL tool.
Like DTS or SSIS.

Access doesn't support code reuse.
I just wouldn't bother using JET, it's just not worth it.

A piece of crap database with a piece of shit toolset

why would anyone use an obsolete database?
and you have the AUDACITY to bitch when shit don't work?

put 2 and 2 together-- SQL Server conquers all.

 

[a a r o n . k e m p f]

if you want more than 255 columns, then you MUST move to SQL Server.
This is one of the main reasons I upsized my career-- a decade ago.

the 255 column limit is just plain stupid.
I don't know how you guys deal with it.

Shit -- MS Project almost has that many fields!!
Shit -- MS Project almost has that many fields!!

everyone should move to SQL Server

 

[cspyro]

Pete, I didn't have the ranges named but I stumbled accross the solution last
night. The input file I was using was from the firm's report web-based
application and it seems the Excel version it saves in is not in line with
the version Access was looking for. I put a line of code in to open and save
this file and the Import works perfectly now. Incidentally, do you know
where I can get more information on running Excel commands and macros from
Access? I need to enhance my process to modify the eventual Excel output
file of the database to format a sheet and run an Excel Macro. Thanks.

 

[Pete D.]

I'm sorry but although I import a lot from excel most are one time deals so
I don't automate much of it. I deal with a lot of old flat files. Pete

 

[a a r o n . k e m p f]

you should still use a real ETL tool
then you can scrub your data-- you can import your data in a way that
doesn't interfere with other users of your systems.

Does your system slow down when you import spreadsheets?

Then you should use a real ETL tool and schedule your ETL.