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:

More topics

 

Importing Excel into Access with few requirements

Preview
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 Excel into Access with few requirements

Preview
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

Preview
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

Preview
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.
 

Import Excel File using a Macro

Preview
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.
 

Importing from Excel into Access

Preview
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.
 

Table import from Excel - only 52 columns??

Preview
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

Preview
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

Preview
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
 

Exporting more than 65000 Access rows to Excel

Preview
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.