Topics Search

Excel rows sorted when imported to Access

Excel rows sorted when imported to Access
Views: 16
I imported an Excel 2007 sheet with 53 rows into an Access 2007 db. I expected Access to read a row and write a row yet the order of the rows was changed. Does Access sort when importing and if so what are the rules and can they be controlled
Sponsored Links:

Similar posts...

 

Importing Excel into Access with few requirements

Importing Excel into Access with few requirements Icon
I'm fairly familiar with access as I use it as a front end to my database. I have created a wizard which goes through couple steps from selecting Import file which is excel with maybe couple of rows with serial numbers in it.

This import file can contain but not have to couple of columns. Also it might contain Headers or not and lastly it might have some empty rows between each serial.

This data is then processed and then the user selects where to save the exported file with a lot of information in it.

Now I have solved the issue of prompting a user to choose which column the data starts at (ex. A1 or B1 etc.)

Now I have two questions that I need help with and I tried searching online but had no luck.

During the wizard stage I would like to include a check mark that would be IF its checked then it will know that this imported file has headers and it will start at A2 instead of A1!

Second Question:

My second question is about the blank rows, when I import the excel file if there are empty rows between filled in rows is there a way to skip them and then on the export sheet just showthe rows that were there on the first excel sheet?
 

Importing an Excel spreadsheet to Access 97

Importing an Excel spreadsheet to Access 97 Icon
I'm sure this is a very straightforward question, but I can't think of the answer. I am using a macro to import an Excel spreadsheet to an existing table in an Access 97 database (yes, we still use 97 in the office!). If I import the table manually, there is no problem and when I open the table, there are the expected number of rows (in this case 25724). However, if I use the macro, only 17413 rows are imported. I've tried removing the PK just in case (but as it works when I import manually, I didn't expect that to help (and it didn't). I've checked the imported data against the whole spreadsheet, and I can't see anything apparent in the rows that have failed to import. Can anyone help?

(I've reduced the macro to just that one function so I know the problem is somewhere in the TransferSpreadsheet action
 

Hyperlinks imported into Access 2007 not active

Hyperlinks imported into Access 2007 not active Icon
Using import wizard, I imported a worksheet of data containing hyperlinks from MS Excel to Access 2007, with data type in target column in Access set to hyperlink. The hyperlinks imported from the Excel sheet were shown up like hyperlinks (with blue letter and underline), but did not works to open the linked webpage at all.

I try to put # to both side of the links in excel as someone suggested earlier, but not work.

and I also tried to save the excel as web page before importing as .html document, but access seems not be able to find the document.
 

Importing from Excel into Access

Importing from Excel into Access Icon
I have a Customers table that has a field called: Event that is connected to the Event table. I know this probably sounds strange that the customer is not in the event table. This is for several reasons: Customer information will always be imported in from Excel with an event.

So for example: CustomerID, FName, LName, Address, City, Province, PCode, PhoneNo, Cell, Event. The customer only ever has one event connected to him.

My background with Access really wants to make the relationship the other way: Event with the customer connected using a form and subform, etc.

Anyhow, so this is the way I did it because info is always imported from Excel could be once a week.

My issue is that because the event field in the Customer table is a number field (because it connects to the primary ID key field in the Event table which is an autonumber field) when I try to import some sample data to test it, it always skips the Event information from Excel.

I tried tricking Access by formatting the field in Excel to be a number and even tried leaving the top rows in Excel as numbers and my real info underneath, it simply does not work.

If worse came to worse, the customer could be matched to the eventafter importing the Customer info, but I was hoping this does not happen because there are about 200 customers imported at one given time.
 

Import Excel File using a Macro

Import Excel File using a Macro Icon
I need to create a Macro in Access that will allow me to import an excel file into an access database.

The requirements are:
-The code should allow me to choose whether the first row has fields
-Browse the Excel file (cannot be specific because this excel file may change)
-Choose the Excel file and specific sheet to transfer (cannot be specific because this sheet may change)
-Skip 'n' rows in order to get to the data of the excel table
-Transfer the sheet into an access table
-Delete original file that was imported

I attached what I have so far but it's missing two requirements: Skipping rows and being able to choose a specific sheet instead of just a folder that contains the excel file.
 

Table import from Excel - only 52 columns??

Table import from Excel - only 52 columns?? Icon
I'm having problems importing a spreadsheet from Excel for a client.

They are using Access 2003 and Excel 2007, 3.5GB RAM
It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.

The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.

It returns error 3274: "external table is not in the expected format."

There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.

The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.

if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this. Am I missing something?
 

Changing names to ID numbers

Changing names to ID numbers Icon
I receive Excel worksheets with information and then import this information into a table I have in access. I verify the data and as a last step I append the data to a table called AllReportData2012 which is used to pull information for my reports.

Part of the Excel information that is imported to a new table includes the employee's name with work details in each row.

In my access report I do not use the employees name, I use their employee ID number. I have a table in my access database that contains 2 columns. The employees Name and their associated Employee ID number.

After I import the Excel information to Access how do I set up a macro or programming that will look at the name in each row and change it to the correct Employee ID. I could have anywherefrom 20 rows of recods to 600 rows of records.
 

Exporting more than 65000 Access rows to Excel

Exporting more than 65000 Access rows to Excel Icon
I have Office 2007 and am trying to export a table with 500,000 rows of data into an Excel worksheet. However it is not letting me do this.

Since Excel 2007 has capacity for upwards of 1 million rows why can't I export a huge Access table
 

Multiple Excel files and need to import them in to access

Multiple Excel files and need to import them in to access Icon
I have been given a very large number of excel files and need to get them imported into access. The first folder alone has 900 spreadsheets.

The sheets I have looked at have headers and the data doesn't start till row 12 or so. Only one sheet per file.

Here is what I need to happen:

The excel files, all of them, would need to be opened and have a copy paste special to get rid of the formulas in the excel files, then saved.

Then all of the files in a specified directory would need to be imported into a specified table in access.

I don't know much about doing this in vb, but I am thinking that is where this needs to be done.
 

Adding field to imported data

Adding field to imported data Icon
I am reasonably expert with Excel but very new to Access. I have an app I have written in Excel which I believe would be more functional in Access2010. My question is: I have imported an Excel table and want to add a new field. This field will have the same value for all 900 lines. How can I easily populate the table with a single value in each record? Is it easier to add in Excel are re-import the table or is there an Access trick I can use