Importing Flat Spreadsheet Into Normalized DB

Nov 29, 2004

So I’ve been assigned to develop a database in Access which will house information on students who are participating in an internship program sponsored by my employer. I was given a spreadsheet with roughly 40 columns, so it is pretty detailed info about the students. For example personal student info (DOB, email, phone, etc), as well as school, major, year rank, school address, permanent address, etc.

Importing this given table is easy, just by using the import wizard. However I am faced with a challenge. The info I have is up to date at this point however in years to come there will be a need to import new student info. The only way my employer wants that to be done is by taking the new excel spreadsheet and importing that data into the already created database. Now this wouldn't be a problem if the DB is one flat, non-normalized, table because the columns would line up. However I feel that the DB needs to be normalized because of the vast amount of data that is repetitive, such as State or Major.

Is there a way to import an excel sheet into an existing normalized access database? Or even a way to automate it so that the excel columns would be broken up into the separate corresponding normalized tables?

Thanks!

View Replies


ADVERTISEMENT

Importing Or Appending Data To Normalized Tables

Jul 16, 2005

I'm fairly new to Access, but have set up a database containing 8 normalized tables. I would like to know if it's possible to update all those tables by importing an Excel file into each table, or importing the Excel file into one Access table and then appending that table to those 8 tables. AND STILL MAINTAIN THE RELATIONSHIPS. The Help directory only talks about importing or appending into one table.

View 7 Replies View Related

General :: Programmatically Importing Flat File Comma Delimited To Access Database

Feb 4, 2013

I have one Access Database and i want to import the flat file coming from Cisco Phone Logs, its a comma delimited that contains the column names in the first row, and in the second row, its the data type, then the succeeding rows contains the data of the logs which are in Comma separated values, I want to put it to my created table programmatically,I used Docmd.TransferText but this will not let me define the row which i wanted to start at row 3.

DoCmd.TransferText acImportDelim, , "tblImportTextFiles", Me.txt_SelectedDirectory & "/" & Me.lst_FilesInDirectory, -1

Attached is the text file i received from Cisco Call Log Applications.

View 3 Replies View Related

Importing An Excel Spreadsheet

Nov 29, 2005

Hello Peeps,

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"

Does someone have any clues as to how to fix this

View 1 Replies View Related

Importing Spreadsheet Data

Sep 5, 2005

i have to import sales figures from a branch to head office. the import facility in ms access 2.0 only allow for 1 table although the data that needs to be imported needs to be done into two tables. how would i be able to do that appending the data to a query already setup to bring in the data. it is just to import the data from the spreadsheet to the two different tables.

thanks melanie

View 2 Replies View Related

Importing A Spreadsheet Using A Dialog Box

Jun 23, 2007

I know that this is possible.

I need to import a spreadsheet via a button, I don't know the name of the sheet nor the location. I need to have the open dialog box appear. I tried the import code which will not work without a name and location.

DoCmd.TransferSpreadsheet acImport, 8, "GoldenViewIn", " ", True, ""

Help

View 2 Replies View Related

Only Importing Specific Spreadsheet - VBA

Sep 8, 2011

I have some code that imports an excel spreadsheet into access and creates a table. I recently received a new spreadsheet that has many sheets. I only need the data from one specific sheet, resources. How can I make adjustments to the code below so that only the data on the resources sheet is imported into a new table?

Code:
Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME

[Code] ....

View 1 Replies View Related

Importing A Sheet From A Excel Spreadsheet

Dec 29, 2005

Hi

I'm trying to import a sheet from a excel spreadsheet.

this is my "code"

The problem is that when i hit the run button, it imports blank sheets or more likely is not importing at all :confused: :(

On Error GoTo ErrorTrap
Dim dlgOpen2 As FileDialog
Dim countrystring2 As String

Set dlgOpen2 = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen2


If txtFile2 <> "" Then
.InitialFileName = txtFile2
countrystring2 = txtFile2 & "!RB_UPDATES"
Else
.InitialFileName = CurrentProject.Path
End If
.AllowMultiSelect = False
.Show
End With
If dlgOpen2.SelectedItems.Count <> 0 Then
txtFile2 = dlgOpen2.SelectedItems(1)
End If
Exit Sub

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "RB_UPDATES", countrystring2, True

ErrorTrap:
MsgBox Err.Description, vbExclamation + vbOKOnly
End Sub

View 1 Replies View Related

Tables :: Importing Spreadsheet - How To Convert Data To PK / FK

Mar 14, 2013

I have a spreadsheet of generators with associated data: Make, Model, etc.

When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).

Example:

tblGeneratorMake
GeneratorID (PK)
GeneratorMake

John Deere has PK of 1
Kohler has PK of 2

If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".

View 14 Replies View Related

Importing Excel Spreadsheet Into Access 2010

Dec 9, 2014

I have a spreadsheet that I'd like to import into Access 2010 however prior to the import, I'd like to delete some of the rows in the spreadsheet.The spreadsheet only has one workbook.It will likely always be rows 1-10.I have already set up the Excel 14.0 Object reference, if that's even necessaryHow can I accomplish this using either VB or query?

View 2 Replies View Related

Importing Data From A Spreadsheet To Existing Multiple Tables

May 30, 2006

First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.

OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.

How would I go about performing this task? I have never attempted anything in the past.

Thank you in advance for your considered help,

Steve

View 3 Replies View Related

Importing Excel Spreadsheet To Access Database Daily

Jun 1, 2006

I need some help on this one. :confused: I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets, type, model, locations, etc. The second spreadsheet has users assigned to them, phone numbers, etc. I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.

The way I have it setup now is through a linked table, then I use a “Make table query” to filter the data.

My problem is the filtered table has relationships set up that I have to delete then recreate every time I need to run the “Make table query” because it has to delete the old table first.

Is there a better way to make this happen?

View 2 Replies View Related

Tables :: Importing Excel Spreadsheet With Empty Rows

Oct 31, 2012

I am using Access 2007 and we are importing an Excel spreadsheet that is received from our customer on a regular basis.When I open the Access table I see over 9,900 empty rows at the start. Following this is the actual data from the spreadsheet.Is there an import setting somewhere that would eliminate all these blank rows?

View 3 Replies View Related

Importing Data In Spreadsheet Or Table Format Directly From Clipboard?

Apr 5, 2012

I work at a hospital and I have been able to copy and paste data from a patient list into an Excel spreadsheet into a list I can customize to track patients I work with. I had a macro that would take the data copied and pasted and organize it in the way I needed it in a design that works for me.

I'm trying to convert this system to Access to give me more control over the data and to try and make the process of creating the list a little more automated. I also have an Access database for "tracking", which is required by hospital management so I was hoping I could use the patient list database to streamline the process of entering interactions into the tracking database.

I'm not sure how to get the copied and pasted patient list into an Access in a way that will let me manipulate it in Access to do what I want it to do without having to add several steps of a) pasting it into Excel, b) importing the spreadsheet into the Access database, and c) cleaning the data that ends up in the final Access table.

Is there a way to import data in a spreadsheet or table format directly from the clipboard, which would allow me to skip Excel altogether? I'm using Access and Excel 2010.

View 5 Replies View Related

Queries :: Importing Spreadsheet On Daily Basis - Detect / Remove Blank Columns

Sep 18, 2013

I am importing a spreadsheet on a daily basis that has the same columns in every time but depending on the company I am importing it for different columns will be blank and not needed each time.

What is the best way of detecting and removing blank columns.

View 3 Replies View Related

Normalized?

Jul 27, 2007

i understand normalization is something that you get trained with after working for a whilie with databases

i have a member db
and i made a table for the country,state,city,zip

this tale is used by other tables like the member info.
how far does the normalization go?

should i make a table for country, state , city, and zip by themselves? or is that "normalized" enough???
thanks

View 5 Replies View Related

Which View Do You Use - Flat Or Threaded?

May 16, 2005

How have you set up this forum? To view as a threaded view or as a flat list?

Poll is attached.

View 14 Replies View Related

Migrating An Unnormalized Db To A New Normalized One...

Jul 25, 2006

Hello all...

How do you merge / append records from one database into another? The old database was very poorly designed (i.e. no normalization), but much of the data is the same.

Instinct from what I have learned says do this:

- create a query in each database that joins all fields together into a flat table
- run an update query that migrates the source data to the new database.

I really have no idea though, and to be honest... even if this is how it is done, I am very unsure how to proceed!

All help is very appreciated!

View 1 Replies View Related

Joining Flat Tables

May 7, 2014

simple join my Main Data Compiling Table and Secondary Table Delete from Main Data table (Located Under main Content Tables). Basically I want it to run like a flat data table which I cannot do because I have too many fields. I want ability to further expand it in the future.Somehow I need a one to one relationship with Referential Integrity enforced with cascade Update Related fields and Cascade Delete Related fields. Problem lies in it tries to create a one too many relationship.Both Main1ID and Main2ID are AutoNumber with Main One being primary key.

I have created a tabbed Navigation Form to show what is currently happening the first Tab is from Client Demographic Form generated by fields from Main Data Compiling Table and Second from Floral Information Form generated by fields from Secondary Table Delete From Main Data Table.

View 3 Replies View Related

Concatenate Text From Flat TXT Files

Jun 15, 2007

I receive reports formatted as a .txt (flat) file.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:

Date Time User Status Customer Name ID#

06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf

1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.

When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.

I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.

2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?

At the end of each record in the report there is a space separating the records.

I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.

View 1 Replies View Related

General :: Using Datediff In Non-normalized Database

Mar 12, 2013

I have a SharePoint list that tracks the date/time of each stop of an order from its origin to its destination. The number of stops varies depending on the type of order (you can use train as a metaphor). The goal is to calculate the average time between each stop (we're trying to figure out where orders typically get held up).

Let's say there are stops A, B, C, D, and E. Every order starts at A and ends at E. If they all made every stop, I could easily get the average of datediff("d", B, A) etc.

However, since they make different numbers of stops, what I need is the difference between the stop and the last stop they actually made. I need a crosstab query (or report) that lists the OrderType as the row, the StopName as the column, and the average(datediff) as the value.

In a perfect world, this would be normalized and I'd have separate tables for the stops and the order types, but trying to do that through SharePoint is Not Fun and is going to confuse the end users.

View 7 Replies View Related

How To Prepare Normalized Data Model For Forms...

Jul 10, 2006

Hello,

I have a fairly complete data model, my question is, how best to create tables for entry where I do not have subform after subform, and subforms in subforms. With subforms for the subforms and subforms on top of that.

I am about to start pulling out some teeth because at this point it sounds fun compared to this!

Do I need to use a query to base my table off of to build my Customer entry form? Or is my only option to use 15 frikin' Subforms? I I use a query, does that cause problems with data integrity?

I am having trouble finding useful reference to this, both online and in books I have purchased.

Normalization is fantastic, I understand.... But besides the crumby Northwind sample DB that everyone and their brother directs you to, is there an actual useful sample to look over that illustrates the use of a normalized data model?

I have grown more than a bit irritated with this, mainly because of my own inablilty to make progress in putting this project together.

I would like to create a simple to use, streamlined systems that promotes efficiency and speed, not a super convoluted system that has 85 subforms on every form in the DB.

I would REALLY appreciate some feedback/direction here, I am about to give up on normalized data and go with a simple flat table approach.

I would rather have redundant data than indecipherable forms that I have to answer incessant questions about.

Thank you,

- I am Rick's Utter Frustration

View 2 Replies View Related

Online Entry To DB - Views On Flat File (CSV)

Feb 13, 2007

Hi Guys,

Does anyone have any experience of writing to an Access database via Web.

Do you think it is feasible, for an online entry form, to have 'customers' input their details or wahtever, then this info is sent to some sort of flat file?CSV is it called?

Then the admin can check these, then import to DB quite easily?

View 6 Replies View Related

Relational Structure To Flat Excel File?

Jul 17, 2007

Hope someone can help with this.

Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.

For example :

tblFeatures :

FeatureID
FeatureName
etc

tblCategories
FeatureID
Category

So each feature can have many categories in a one-to-many relationship.

What I have a requirement to do is to pull that out as a flat file in Excel, ie

FeatureID, FeatureName, Category1, Category2
etc

What's the best way to go about this?

Cheers,
Iain

View 2 Replies View Related

What's The Best Way To Add A Record To A Normalized Database Utilizing Combo Boxes?

Jan 30, 2006

I have four tables with unique records as shown below. I'd like to add a record for a new item to the database by selecting the values from combo boxes for Supplier, Item and Unit. If the values do not exist the user would then type in a new value. SuppliersItemCode and Cost would always be new values. What's the best way to go about this? I am unsure how to add a record to a normalized database where you sometimes have to use / reference existing unique values in multiple tables via foreign keys for the new record.

The logic of the form would be:

1. Select existing or add new Supplier.
2. Select existing or add new Item.
3. Select existing or add new Unit.
4. Enter new SuppliersItemCode
5. Enter Cost

Suppliers
---------
SupplierID (primary key)
Supplier (indexed unique)

Items
------
ItemID (primary)
Item (indexed unique)

Units
-----
UnitID (primary key)
Unit (indexed unique

SuppliersCostsAndCodes
-----------------------
SuppliersItemCostCode (primary key)
Cost
ItemID (foreign key)
UnitID (foreign key)
SupplierID (foreign key)

Any advice or assistance is greatly appreciated, thank you.

View 5 Replies View Related

Queries :: How To Make Hierarchical Structure For Flat Table

Jun 23, 2013

I have 5 tables with below design

Table 1 : Docid (pk),DocNo

Table 2: Transid (pk),Transmittal No

Table 3: Docid (fk to table1),TransID(fk to table2)

Table 4: TransmittatoConID(PK), TransmittaltoCon

Table 5: Docid(fk to table 3),transID(FK to table3), TransmittatoConID(PK)
�combine fields�

I have made a query from top tables that returns the below data

DocNo TransmittalNo TransmittaltoCon

Doc-0001 tt-0002 con-0005

Doc-0005 tt-0002 con-0003

Doc-0001 tt-0002 con-0007

Now I like to make a query with adding new fields (parent) , (position) that shows

hierarchical structure of the fields

level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) ,

ID PARENT POSITION OPTION

1 0 0 DOC-0001 2 1 0 TT-0002 3 0 1 DOC-0005 4 1 1 CON-0007 5 3 0 TT-0002 6 5 0 CON-0003

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved