There are some web forms where people can submit work requests to my team. The data is fed into Excel in .csv format. Each request adds a new row to the spreadsheet. Nothing ever drops off.
I am having problems getting that data into my "final" Access tables without repeating the data with each subsequent import/append. There is no unique identifier in the Excel file, only the raw data, all fields of which could be duplicate across requests.(requestor, phone, request by date...) In my Access tables, I have the same fields as in the Excel file, along with an AutoNumber primary key and some additional fields where we can enter when work began and was completed and to who it was assigned.
Say for example 5 requests come in and thus create rows 1-5 in the Excel file. I then import the data into a temp table, and then append to my final table. Works. But say an additional 2 requests come in...I then do another import and append and my final table ends up with rows 1-5 AND 1-7.
I was thinking composite key of some sort, but none of the fields lend themselves to that. Plus I need the ability to edit some of the imported data once it hits my final table (ex: changing a request-by date or request detail), so a field by field comparison of the import table to my final table to determine uniqueness probably won't work either.
I'm thinking the solution would be to add a unique identifer at the source level, but I'm pretty much left to come up with a solution in Access as the web team's resources are unavailable for a few months.
We are submitting an Access db table with ID as primary key to an outside company for processing. We export it as csv file and that's how it comes back.
After importing it back, we noticed that our IDs, which previously had 'gaps' as a result of deleted records, had been changed in to a new continuous sequence (Autonumbered). This, of course, completely destroys our database since related tables depend on IDs being unchangeable.
We ***suspect*** that the IDs are being destroyed during the process of re-importing the csv file. We are using the Access import wizzard.
Access msg: "Access recommends that you define a Primary Key for your new table". We then are given the following options:
1Let Access ADD primary key 2Choose my own primary key 3No primary key
During Option 1, the ID column is displayed and high-lighted. But we do NOT want that option since it offers to ADD a primary key, whereas we already have one (ID). At most, we want to 'declare' or 'define' a primary key, but we do not want Access to ADD one.
So we try Option 2, hoping that this will give us the chance to CHOOSE ID as our primary key. But the moment we select that option, the ID column disappears. We are not given the chance to select ID as our primary key.
So we try Option 3, hoping to re-instate ID as primary key as soon as we have an Access table again. But the moment we select this option, the ID column disappears.
-----------------------
If we GO THROUGH with Option 1, we notice that Access has renumbered our ID, the IDs of all our 'deleted' records have been re-assigned. Or so it appears.
If we GO THROUGH with Option 3, the resulting table does not contain an ID column at all.
Of course, the damage may have been done by the outside company which processed our file, but if so, we have to prove it, and we have to take measures to avoid it.
Hi all, I currently have 2 databases: a main current database (Main.mdb), and a secondary database (Working.mdb).
They are identical, except the working database has no data, just the existing tables, forms, etc. from the main database.
When I (or a programmer) makes changes to the forms and some underlying calculations, they are first made in the Working database.
After the changes are made, i delete all the data from the tables in the working database (data from testing, etc.). I will then copy all of the data from the tables in the Main Database over to the Working database, then rename the Working Database to the Main Database.
The only problem I'm having right now is that the primary key in one of the tables (ClientID) is an autonumber. When I paste the data into the Working Database, all the ClientID numbers change. Is there any way to keep the numbers the same as the Main database?
Guys I don't know if any of you have done this. I need to import a formatted excel file into an access table, I know that I need to write a vba code to do it, can someone please give me a tip or an example. The excel file is a formatted form, it's not based on columns & rows!!!! Please Please assist.
I have a table that I would like to update with information from a spreadsheet. The table are all of the property records for the county I live in. The table has a unique data field for every record called a "PIN" (Property Indentifier Number). It is in this format: 12-3456-78-90. Because the PIN's were hand entered, sometimes there is a space between the dashes and the numbers, but each PIN is different for every record.
The data from the spreadsheet I want to import has updated sale information for the property records in my table. I want to associate the PIN's in the spreadsheet to the PIN's in my table and only import the new owners name, sale date, and sale price from the spreadsheet to the table.
How would I do this import? It is probably straight forward, but I am a newbie with Access.
I am concerned that because the PIN from the Speadsheet was typed differently from the one table, the import won't work. The numbers are the same, there are just spaces between some of the numbers and dashes.
OK.. any suggestions as to how I get my Excel worksheets imported in Access? I have tried and tried.. and it gives me a message saying that it was not imported.. I am new at access..
I am trying to import a sheet from excel into a new table in access. One of my columns has a date value which, when imported changes to the actual appliacation value (i.e. 1/1/2006 becomes 38718).
Is there a way to stop this happening or a function within acces that changes the value back to a date format?
as the program that we use to use was free and setup for what we needed. That has the ability to create a excel sheets with the 1600 people on.
Now all the fields are different, and i'm not sure how to do it.
When importing do you get the option to put the fields in the correct place, or do i need to edit them? and then import.
Another thing the address layout with fields will be completely wrong. I believe it used separate fields for each line of the address where in my DB i use 2 fields 1 address and 1 postcode.
Hi all, I have been using Excel data in Access either by importing the sheet as a table or directly linking to the sheet with no problems in the past but today I cannot seem to get the data to import in the right way.
The column on the Excel sheet is mainly populated by numbers but some of the values have letters in them. When I discovered this I formatted the column as text and tried to directly link to the sheet but the datatype kept insisting it is number. I then decided to import the sheet as a table and although it comes in as Text instead of seeing the value A1030573 in comes in as 7.1e+009 and this goes for the values that are purely numbers.
Is this a known bug, does anyone have a solution to to this ?
Hope this is the right section to post this question. I thank all for any assistance in advance.
We basically have a supplier's access database that contains one table of prices. In addition, there also exists an excel file that contains applicable discounts against the supplier's items. There's a common key, the part number.
There's a need to unify these on an on-going basis as supplier sends updates to their database, and our finance department (who will not move away from an excel worksheet) update their records. What I'm thinking of doing is creating another database with two tables; one whose characteristics are same as the supplier's table and another with fields matching the excel file. And write a script or something that when you execute it, pulls data from (predetermined location) database and excel file to the two tables. Queries can then be constructed using the relationship between the tables.
How can this be achieved? Did a search and no sugar. Or is there a better way. The users are access novices and would like to minimise their "workload".
Hi... i have a excel file..... from which i have imported records.... after importing.... the records sequence in my table has changed..... can anyone tell me how to preserve the sequence that i had in excel..........
I am trying to import some data from excel to a new table in access. When i am running the import wizard in access i.e. File->Get External Data -> Import, as soon as i select the excel file (which has a sheetname of Sheet1), i am getting an error that i can not import since there is an invalid character 'Sheet1$'.
I tried renaming the sheetname to various other names, however, everytime i am getting the same error with the dollar sign being added at the end of the sheetname.
I have a program I'm updating that I need to be able to import excel sheets from a lot of different sources and add them to a table in a db which sorts the normalization.my problem is the sheets come from so many sources and have different formats and column names/Locations.does anybody know a way where I could setup say a table specification and maybe run some automation with excel to move the data into the correct fields in access.or have any suggestions.thanksmickp.s. is there a way around the 63k rows limit for importing excel?
I am trying to import Excel data into an existing Access table. I keep my Excel spreadsheet in the same format as my Access table and when I import data that attaches at the end of my Acess table it works fine.
But I am now trying to import Excel data into the same existing table into rows that had cells left blank for entry later?
I'm not quite sure what's going on. Right now I'm trying to manually import an excel spreadsheet (File -> Get External Data) to make sure everything is running right before I put it into a macro. Well, if I tell it which table to dump the data into all it does is give me an error message saying there was an error and that it won't import. If I let Access create it's own table during the import process it imports fine. I thought maybe there was something wrong with one of my fields in my table so I copied the whole structure of the table Access created to create a new table and tried to use that one import the information into. It still won't import the information. Any ideas? :confused:
I'm trying to import an excel spreadsheet into an access table. The excell spreadsheet was created using the access table( by using the 'analyze using excel' feature)
The problem is that I cannot import the updated excell spreadsheet. I get the error message.." An error occured while trying to import the file..the file was not imported"
I have a table which I need to import from Excel into Access, but I only want to import the first character from each cell of one of the columns. Is this possible?
I'm trying to import an excel sheet to access and one field is y or n and I am not sure how to convert this so the value is right so I can query it.What should I put in my excel sheet "yes","no' or Y,N or numeric values??Access doesnt show it help please!!!
Hi, I am importing a custom field (containing six digit integers, e.g. 000099, 012000, 102300, ..) from Excel. This custom field in Excel is formatted 000000.
The corresponding field in Access is a text box with input mask !000000. (the Format property for this text box is blank). However, on importing, 000099 enters as 99, 012000 as 12000 etc.
What have I done wrong! Any help very much appreciated.
hi, i have struggled to import some data from an excel file into an sql database.. i have used phpmyadmin and certain code snippets but have failed miserably..
basically my excel file has lots of data, and within each cell, each bit of data is in single quotes...
eg. 'jonathan' '23' 'hardman' 'cheese'
there are no headings in the excel file (as in column or row titles) the data is just raw. once i have made the table (with the appropriate fields and datatypes for the csv file) how can i import that data into a table using ms access???
I will routinely be sent a Excel 2000 spreadsheet with multiple worksheets and each worksheets data is to be imported into a related named table in a MS Access 2000 database. No of course I have been using Transfer Spreadsheet to perform the imports but to make sure I get all of the data I use the range A1:AZ65536 (65536 of course being the maximum number of rows available in a Excel 2000 spreadsheet).
What I would like to do however, is just import the necessary number of rows not all 65536!!! Is there a way in code of working out how many rows in the A-AZ column range contain data?
It occured to me that if I linked a table to each worksheet then this would only display the necessary rows and I could count them however, once you have specified the spreadsheet location the code doesnt let me repoint the individual worksheet unless someone knows how to do this???
Hi Folks. As a newbie in Access, but not in Excel, I tried to 'get external data' from an Excel spreadsheet into an existing Access table. However, it brought in blank rows because those rows had a formula in them but nothing else. Any way to avoid the blanks being imported??
I wanted to know if anyone else is having issues with getting external data into access from excell? For some reason this function is not working for me today.
If the data in the spreadsheet is not in a standard row/column datasheet format how can I import it to Access, does anyone know, please help if you do. I have a formatted excel form and i need to import it to access!!!!!!
