Question Problem importing Excel into Access (1 Viewer)

Sean_F_Howard · Apr 12, 2011

I have a problem when importing an Excel workbook into an Access database.
Basically Access does not seem to completely import any row whose total number of characters is more than approx. 1800? I know this doesn't really make sense but the example should make things clearer.

You'll need to create a spreadsheet with 200+ columns each containing a fixed number of characters (say 10 characters). I created the following file :-

Code:

A1   ="column"&TEXT(COLUMN(),"0000")
A2   =REPT("1234567890",1)
A3   =LEFT(A2,LEN(A2)-1)
 ...
A11  =LEFT(A10,LEN(A10)-1)

B2   =A2

Now copy A1 across 220 columns (I decided against using all 255 columns might cause unforeseen problems)
Then copy B2 to fill in the remaining cells for the table.

If I link this spreadsheet into an Access database everything is OK.
If I import this spreadsheets into an Access database a couple of strange things happen

The order of the imported data is not the same as the data in the spreadsheet
I also have an "Import Errors" table
some of the data is missing from the first couple of rows

I have no idea whay the order of the data has chanegd (although it does not ALWAYS change and even when it does the changes are not consistent)

The "Import Errors" table shows that a couple of rows have an "Unparsable Record" which is strange coming from a spreadsheet. I could understand having some limit on text files (originally I was using tab delimited text files but moved to spreadsheets when this issue occurred) but spreadsheets?

The 1st row has 179 columns of data (179 x 10 = 1790 charters)
The 2nd row has 199 columns of data (199 x 9 = 1791 charters)
The 3rd row onwards are perfectly fine

I'm not sure that this is really an error, it might simply be a gap in my understanding but I would really appreciate some help here as I cannot even tell my users and maximum number of column?

The attached files containing the original data plus how it looks after importing into Access.

S.

PaulO · Apr 13, 2011

Personally I've not encountered problems with importing, in relation to numbers of characters, so you're perhaps analysing the wrong problem.

In terms of maintaining the same record order it will perhaps help if, in your spreadsheet, you inserted a column with a sequential (incremental) code and you can then easily sort by that field once the data is in Access.

When importing, any records not imported will be copied into a Import Errors table or similar ... generally the errors will relate to invalid or incompatible data types (particularly Dates) so you'll perhaps need to do some data cleansing in your spreadsheet.

Sean_F_Howard · Apr 13, 2011

PaulO said:
Personally I've not encountered problems with importing, in relation to numbers of characters, so you're perhaps analysing the wrong problem.

In terms of maintaining the same record order it will perhaps help if, in your spreadsheet, you inserted a column with a sequential (incremental) code and you can then easily sort by that field once the data is in Access.

When importing, any records not imported will be copied into a Import Errors table or similar ... generally the errors will relate to invalid or incompatible data types (particularly Dates) so you'll perhaps need to do some data cleansing in your spreadsheet.

Thanks for replying and I hear what you're saying dude, but I think you missed the point.

Firstly regarding the order of the imported data, I do not understand WHY the order changes when importing into a table with NO indicies.
Imagine if I was importing a series of instructions or even a letter. The fact that Access, could randomly alter the order of the sentences would make unusable for anything other than unordered data and I am certain that that is not true.

Secondly I cannot alter the input data before importing as this would mean another step in the process for the user (and potentially another place for human error). I could write the code to :-

open the spreadsheet
add an index column
save under a temporary name
import the temporary spreadsheet
sort by the index
delete the temporary file

but that really seems like a lot of work to "fix" something as fundamental as the Docmd.TransferSpreadsheet method

Thirdly I've interrogated the ImportErrors table and it only tells me where the errors occur but not why. As I mentioned I can see that there seems to be a limit of approx 1800 characters. What I don't know is why is there this limit and how can I circumvent it?

Finally I cannot help but feel that there is a gap in my knowledge of Access. I've been using it for almost 15 years and do not believe that I'm too old to learn something new. I'm not necessarily looking for a straightforward "this is the answer" solution, but at the very least some guidance as to what direction to follow.

S.

PaulO · Apr 13, 2011

I can't add too much ... but you could perhaps consider LINKING your spreadsheet as an External table rather than importing the whole data file. This then gives you ample scope to write Select, Append, and Make Table queries as required.

gemma-the-husky · Apr 13, 2011

if you really want to control it, save the spreadsheet as a csv, and import that,

much more reliable

Sean_F_Howard · Apr 14, 2011

PaulO said:
I can't add too much ... but you could perhaps consider LINKING your spreadsheet as an External table rather than importing the whole data file. This then gives you ample scope to write Select, Append, and Make Table queries as required.

Thanks a good point PaulO and to be honest it's exactly what I normally do.

Originally my source data came as tab-delimited text files, I have an ImportSpecification with 255 text columns and using that everything works fine.

So Linking text files was OK, but I needed to make some changes to the imported data BEFORE storing it elsewhere and you cannot edit a linked text file.
So I decided on importing text files instead. Here I met with a limit on the number of imported characters in any one line (approx 1800 characters).

So.

Why not use spreadsheets instead of text files as my source data, this would remove the line length limitation.

Now linking spreadsheets failed as Access makes assumptions about the datatype of each field which was not always correct. I need to see strings and numbers many a field BUT Access assumed that it was a numerical field and thus my strings were shown as errors.
Finally I tried Importing spreadsheets, but this failed as I met with EXACTLY the line length limitation that I found with text files (which I still find really strange)

I also tried using CSV files but got basically the same results as when using spreadsheets with the additional problem thatimporting csv filessometimes deleted records that did not fit into the chosen datatypes

Hence the question, what, why, how, 1800 character limitation

S.

gemma-the-husky · Apr 14, 2011

can you post a small example of a csv file with these long rows. I would like to try this.

Sean_F_Howard · Apr 15, 2011

gemma-the-husky said:
can you post a small example of a csv file with these long rows. I would like to try this.

Attached are examples of the type of data that I'm trying to import with 3 flavours :-

tab delimited text
csv
excel workbook

S.

gemma-the-husky · Apr 18, 2011

the csv appeared to import without problems

however, the issue is that to import a csv file, all rows need to have the same number of columns, and similar data types in each column.

the first few rows of your table are not formatted like this, and are stopping a successful import. Delete those, and you shouldn't have any problems.

Each comma represents a separate column. Acces splits the table, based on the commas, and imports every column into a separate field.

What you dont want to do, is to import the whole row into a single field, and then try and split iit into separate fields. That may be what is causing the problem.

Sean_F_Howard · Apr 25, 2011

gemma-the-husky said:
the csv appeared to import without problems

however, the issue is that to import a csv file, all rows need to have the same number of columns, and similar data types in each column.

the first few rows of your table are not formatted like this, and are stopping a successful import. Delete those, and you shouldn't have any problems.

Each comma represents a separate column. Acces splits the table, based on the commas, and imports every column into a separate field.

What you dont want to do, is to import the whole row into a single field, and then try and split iit into separate fields. That may be what is causing the problem.

Thanks for the input Dave, but truthfully it does not help my cause.

I CANNOT ask the user to edit the file before importing because they are USERS and will almost certainly screw things up at some point, so I am stuck with dealing with the data in it's original format.
I CANNOT use Access to edit the original data file and remove the initial rows because, this is currently unachievable (remember this is the point of my thread). I know and understand how Access "should" import comma delimited data BUT as I said in my previous post, Access DOES NOT IMPORT ENTIRE RECORDS for records whose length is greater that 1800 characters.
Your last point about importing whole rows into a single field might be the ONLY way to sole this problem as I could probably parse the field myself using the SPLIT() function. However, doing so would make the DoCmd.TransferText method redundant and I cannot believe that this is the answer.
Finally theoretically I can handle the idea of there being a limitation of the record length of text of CVS files but such a limit in a spreadsheet makes no sense at all.

I really hope that I am explaining myself well, but I'm getting the feeling that I'm asking too much from Access, which would be a shame.

S.

spikepl · Apr 25, 2011

With regard to the sequence of imported Excel-data I had a similar issue recently. The application worked fine during testing for months, but then when going live, we got Excel-fields prepared by somebody else.

The area to import was now in Excel formatted/set up as a table (with nice little thingies in the header line, so you could select/sort data) - and the sequence of the rows as appearing in the Access import table became rather random. Unformatting the table in the .xls did the trick.

thebellguy · May 9, 2012

I am having a similar issue, however I am not importing but have created a table in excel that links to my table in access. When the data is refreshed each month, the table data order changes. What shows up after refreshing the connection in excel is in both a different order than the previous month, and a different order than in the access table. Neither of these are sorted in any way and yet, access decides that it likes to 'mix things up' each month. Is there something somewhere that I need to look to fix this or is it another built in glitch. I've tried modifying the sort orders everywhere, and it does nothing. My issue is that I have associates entering data (explanations of each line) beside the table in excel, and if the table sort order changes, these explanation lines don't. That is a problem.

Pat Hartman · May 9, 2012

I have no idea whay the order of the data has chanegd (although it does not ALWAYS change and even when it does the changes are not consistent)

I always start this explaination with "Access is a relational database". It is not a spreadsheet and so it acts like a relational database and not like a spreadsheet. In a relational database (SQL Server, Oracle, DB2, Jet/ACE), tables are unordered sets. If order is important to you, you MUST include an order by clause in your query. People get fooled by the behavior of the interface. When you open a table in datasheet view, it looks like the rows are presented in primary key order. But since most people don't open huge tables and work with them this way, they don't ever see that this is not 100% the case. One of the tasks accomplished by compact and repair is to resequence every table into PK order. for those of you who work with other RDBMS', this has the effect of creating a clustered index on the PK. That's why when you open the table, it looks like the records are sorted. But, don't be fooled, they are not sorted unless you specifically sort them.
thebellguy,
You can't match the Access table to the linked spreadsheet by "line number". There is no such concept in a relational database. The two tables must have unique identifiers and your query must join on those identifiers. Then and only then will the match be reliable.

thebellguy · May 9, 2012

Thanks Pat.

When I re-read my post, I didn't really explain what I meant properly.

What I have in excel (2007) is a connection query similar to this:

SELECT * FROM `S:\Folder\MonthlyData.accdb`.`MyTable`

There are 20 columns, (A-T) and the access table is updated with an append query to add the data from the current month. In excel, the "table" that is created by the connection query is from column A-U allowing my team members to add comments to each line in column U that explain the root cause behind issues identified on that line. I haven't done a whole lot of database work since dBaseIII+ which is a while ago, but I have a feeling I will need to change the way this is done so that the comments remain on the same line as their associated problem.

Will simply specifying a sort order in the table ensure that the connection query brings them in in a consistently sorted order, or should I add it to the conn qry too? (of course the creator of the db thought that having a primary key was a bad idea so I would need to add a field to actually sort by)

Or maybe I'll just create a form that enables them to add comments directly in the database, if i can convince the big boss of that.

I really appreciate the quick response to what was sure to sound like a dumb question.

Pat Hartman · May 9, 2012

There needs to be some unique id that can be used to match the rows in the spreadsheet to the rows in the table. If there is such a column, create a query in the database that sorts on that column, link to the query.

rixonraphael · Aug 1, 2012

I am encountering an error "Property Not found" while importing excel sheet to access. I am using "external data => excel". Please help.

Question Problem importing Excel into Access (1 Viewer)

Sean_F_Howard

Registered User.

PaulO

Registered User.

Sean_F_Howard

Registered User.

PaulO

Registered User.

gemma-the-husky

Super Moderator

Sean_F_Howard

Registered User.

gemma-the-husky

Super Moderator

Sean_F_Howard

Registered User.

Attachments

gemma-the-husky

Super Moderator

Sean_F_Howard

Registered User.

spikepl

Eledittingent Beliped

thebellguy

New member

Pat Hartman

Super Moderator

thebellguy

New member

Pat Hartman

Super Moderator

rixonraphael

New member

Similar threads

Users who are viewing this thread