Tables :: Spreadsheet Weekly Upload - Setting Up Unique ID On Import
May 13, 2014
I have a user that uploads a spreadsheet weekly. I want to assign a unique id to each record that is the current date plus the start and end date for the data they are loading which would be the week prior plus a counter.
For example: Data from the week of 5/4-5/10 is loaded on 5/12.
Record one would be 050414-051014-1
Record two would be 050414-051014-2
Record three would be 050414-051014-3
and so on. How do I accomplish this in my table design? They will be deleting the prior week data and pasting the new data so the table design will not change.
Is it possible to import an excel spreadsheet which contains some merged cells. What I have got is a spreadsheet (like Raw Data in the zip file) and I need it looking like (Finish Data) but in a access table.
I have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.
Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.
Let's say I have a table called "users" and within that I have:
Surname Forename FieldA FieldB FieldC
FieldA has a value of either NULL or a 12-digit number FieldB has the values are "ENABLED", "DISABLED" and "N/A" FieldC contains a value of either "1" or NULL
This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.
Sooo... I'd like my query to categorise for me as follows:
Category1 = FieldA IS NOT NULL and FieldB="ENABLED" Category2 = FieldA IS NOT NULL and FieldB="N/A" Category3 = FieldA IS NULL and FieldB="ENABLED" Category4 = FieldA IS NULL and FieldB="N/A" Category5 = FieldA IS NOT NULL and FieldC = 1 ... etc.
I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.
The spreadsheet I've attached contains a ton of calculated cells and linked data. The people who created and utilize it aren't necessarily interested in changing the way they use it or populate it each quarter. That being said, my task is to somehow create a way that it can be sent to/seen by each person differently. First I'll point out that the 3 columns that are blank normally contain names, I deleted them for obvious reasons. The first of those columns, B, contains unique names for each agent in the firm, these people need to ONLY see their own data. The second column of names are basically managers, they need to see the records for each agent that falls under them. The 3rd column of names is irrelevant for these purposes.
Now if all this data were in access, and everyone had access to it I could easily just make records visible or invisible based on user name and I wouldn't be here right now. But they can't and that's not an option. I honestly don't know if this should be done solely in Excel, in Access or a bit of both. Currently this spreadsheet gets emailed to each manager and they have to review the data with each agent.
The first tab, worksheet, is basically instructions. It would be nice if those were images on the page and then all of the data were below that so that the agents could see them together and understand it better. As I'm sure you'll see it's a pretty complicated system so they have a hard time understanding it.
Note: There are actually about 3,000 records, I've deleted most of them for size purposes.
Hello - just joined today. I hope to be of some help to other users, but am kind of a newbie.
I have an excel ss that I regularly receive which needs to go into my access DB. I set up a macro with transferspreadsheet and that works, except for one problem. I have one field which is designed as text but has data that looks like numbers as well as data that looks like text. When I run the macro, the number-like data doesn't import as text (23602012345 imports as 2.3602e+010). FYI the field in access is already defined as text. Each time I import a ss, I am appending to the existing data in the table
I have tried formatting the excel column as text with no luck. If I remove the data that truly is text in that column (ABC1287567) and leave just the 236* data, and then format the excel column as text, it seems to work fine. It just doesn't like the mixing of the data...at least it seems to me.
Any ideas to avoid manual manipulation of my excel ss before running the macro would be very helpful.
Anyone out there have any ideas on how to Import an Excel File (SPECIFIC worksheet) to an Access Database
I am looking to do this at the click of a button or when the database opens...
What I really want to do is...... Is there a way to import this into an already existing table. Delete the existing records and import the new records from the Excel Spreadsheet?
WE are starting at the very basics. We used the db Wizard to set up a db for us. Now we are trying to import the data as a csv file. We keep getting an error Field 'Field6' doesn't exist in destination table 'Contacts'. We have looked at both the excel file and the table in Access and we cannot figure out what is holding us up. Please be patient, we are learning by the seat of our pants
Every month I download an Excel spreadsheet from a vendor and import it into Access 2003. Even tho the first row has column names, a dozen fields have incorrect names and data types and I have to manually change them each time. The spreadsheet has columns representing the amount spent for specific months. The column names are all like "May-02", "Sep-03", etc. and are all currency. After the import, many of the fieldnames are like "1/5/02 0:00:00" instead of "May02" and the data type is text. The column formatting is "mmm-yy" on all the columns, even the ones where the field names match the column names. How do I get all the fields to import correctly?
I am failry new to Access, and am trying to import an Excel spreadsheet into a Table. I go all the way through the import wizard, and it gives me an error that it cannont create, and then it goes back to the last screen, and I have no Idea what is wrong.
I have looked under many help's and online tips, and searched this site to no avail. As far as I can tell everything should be acceptable to import. I have unique column header titles, no blank rows, nothing over 255 characters, and it still does not work. Can anyone give me some advice on what is wrong in this spreadsheet causing it not to be imported?
I have a default directory that will contain Excle spreadsheets. This spreadsheets have the name of the table i want them to create when they are imported in Access.
I want first to count and present to the user the number of the spreasheets. then import one name the table with the name of the spreadsheet and then process. When done move to the next one until there is no more in the directory...
I have an assignment that is currently above my skill range in access.
Basicly i recieve a monthly excel spreadsheet with 2 sheets.
Both are now made into access tables.
what i want to do is make macros so that when we recieve the new spreadsheet file next month i can import the data.
my difficulty is that if i go file-->import --> select the spreadsheet, etc it allows me to choose which sheet inside of the excel file i want to import. But using the transferspreadsheet macro doesnt. so it spits me an error saying it couldnt find so and so field, etc.
basicly i need 2 macros.... 1 for each sheet. to import the new data into the respective tables.
also it would be awesome if on the import it could add a field "last imported" or "last modified", etc and put todays date, etc.
I have a database, in which I have three tables which hold 'static data', which will be used over and over again to record work and produce invoices.
The Tables are as follows:
tblManagedUnit MUID (Key) (One to Many with CircuitName below) MU Number
tblCircuitName CircuitNameID (One to Many with SpanDetails below) MUID (FK) (From tblManagedUnit) Circuit Name
tblSpanDetails SpanDetailID CircuitNameID (FK) (from tblCircuitName) Span From Span To
I have created the Managed Unit Data manually, of which there are 43. However I have a spreadsheet with 8,600 lines of repeating data in all three categories.
I have a procedure that imports excel spreadseet to access table ever today. today it failed. Forrmat was the same as yesterday.
I did 'copy/paste special/format' from yesterday's file; still today's file wouldn't import.
Tried to copy manually to the table, got the error message about 'field not being in the same format'
Went to table's design view, and changed everything to 'memo', the was able to do copy/paste. There wer no values with more then 20 characters I was able to change 'memo' back to text and date/time without loosing any date.
Has anyone ecountered this ? Going changing table, and then chaging it back doesn't sound like the best practice what can be done ? can abything be done to excel file ?
Ok I have a function that imports some spreadsheets into our db (it's a timer/scheduler on another db)> The file coming from oracle discoverer originally. Can't figure out in that 4i version how to remove the headers.
What could I add into my code in the function to import only the data and not the column names/headers? I can provide code if needed?
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
I'm trying to import an Excel file into access as a table so I can use the data in other tables I am building. When I try to import to file, I get an error message telling me that there are over 255 columns and not all my data will be imported. The file is a report I pull from another system at work and it is very large, is there a way to get past the 255 column limit?
I have an Excel spreadsheet. I need to import it into Access, preform calculations in several queries, create a new table (I will call it Table2), and then export it to a new text file. I have a data type issue though.
I need to do this monthly, using the same spreadsheet with updated data.
My steps are: -Update the linked Excel spreadsheet. (I will call it Table1) -Run a query to delete the data in Table2. -Run an append query to update Table2 with my calculations and data from Table1 ****I need my end result to have specific data types different from the Excel spreadsheet(Table1)**** -Export to a text file.
If I do an update query my data types change because I drop Table2. I need my data types in Table2 to be different from Table1.
With my append query I get a conversion type error. I know why I get this and that makes sense, but I do not know how to solve my problem.
Need to get these into Access from an excel spreadsheet (located on sharepoint). I'm using the spreadsheet fields to create the table fields in Access.
hello everyone, i really need some detailed help as the deadline is approaching and I need to find a solution for this. Any help would be greatly appreciated
I currently have a batch file that ftps a text file from a Red Hat Linux Server to my W2k C:. I would like to make another command in the batch file that imports this text file into an existing access table. I would like the text file to repopulate the table everytime it is imported. I do not want the data added on to the existing data in the table.
I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.
Code: Sub ExcelFormat() Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.worbooks.Open ("Z:DataTest.xlsx") End Sub
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference). Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
I have created about 7 tables in Access, which all have the same column names. I want to export all of the table's data at the same time into a Excel SpreadSheet using VBA.
Also I want specify where i want the data to go in the SpreadSheet e.g. All data will be exported to cell A4.
i have created a form and added a tab control with a spreadsheet on each tab,(ex. column headings in spreadsheet, date of service,minutes tab headings ot visits, pt visits, dr visits, , however, i will need each spreadsheet to be visable when printed because forms will be used a cover sheet with summary of data
I have been tasked with converting a spreadsheet to an access database. I work for a growing firm of Tree-Surgeons (no pun intended), who service a sizeable chunk of the power grid in the UK. At any time, we have up to 150 operatives in the field, cutting vegetation around power lines. Because of the obvious danger, the power company need advance warning of where any teams will be cutting on any given day.
Various team leaders call in each day and give their intended locations for the following day to an individual who's responsibility it is to log the info onto a central spreadsheet, an Excel workbook. Each workbook contains a worksheet for each day (Mon-Fri). Each worksheet contains the following fields:
1. Number (Unique Numerical Sequence for the individual) 2. Name 3. Phone Number 4. Working/Not Working 5. Type of Line (132kv, Extra High Voltage (EHV), High Voltage (HV), Low Voltage (LV)) 6. Area (in this case a lookup of 8 geographical areas) 7. SubStn Number (a name of exact location followed by nn/nnn) (LV Only) 8. Circuit and Pole Numbers (a location followed by nn) (HV Only) 9. Locality (nearest village/town) 10. Grid Reference (nn/nnn/nnn) 11. Time On-Site 12. Time Off-Site
So far I have created a table (Called Contact) for the individuals:
Team Members can move between teams, but Team Leaders remain largely static.I guess I should create a separate table for Team Leaders and Team Members, with a one-to-many relationship between them? All of the others could be around the network but would not be attached to a Team Leader, so I guess they can be in the same table as Team Leaders for the purposes of tracking their location.
Also, what is the alternative to using a look-up for some of the fields, for example the Area field? I was concerned about breaking one of the commandments, and cant see a reason why, but I await enlightenment!
