February 22, 2010 at 9:36 am
I've been tasked with importing data into our SQL Server database from an Excel table but I'm not entirely sure how to go about it. A coworker needed to enter site data off location and it wasn't possible to link to the network servers. Our database uses Access as a frontend database because it's easier for most of the people here to use. My coworker created a simple Excel table so that he could enter the data with the expectation that the Excel file could be emailed and then the data could be imported to the database once he was finished. The Excel table consists of data that is found in two separate tables in the SQL database. One SQL table is a list of the data from the species found in each site. The other table consists of basic site information including site ID and sampling date. The first table doesn't have any site id info, just a string of numbers (a unique string for each site) that points to the site info in the second table. How do I go about importing the table into SQL Server, especially if the unique code for each site is automatically generated in the Access data entry?
February 22, 2010 at 9:44 am
Have you looked into DTS? Take a look and see if that will do what you want.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2010 at 9:46 am
You stated that you are using Access, but you keep mentioning SQL Server. How many actual databases are you using? And why?
If you are using SQL as the back-end database. please give us a DDL of the tables in question and some sample data.
Pump data into a database table is very simple, but yours sounds very confusing as to where the data came from and where it is needed.
Andrew SQLDBA
February 22, 2010 at 11:07 am
Sorry for not being more clear about my situation, I'll attempt to clarify. I'm working with one database that is based on SQL Server tables, but database entry/queries at different workstations occurs through Access. Data entry for this particular set of data occurs on one form with a main form for entering info such as siteID and the date that the site was visited and then a subform for species abundance. The data entered into the main form goes into a table called BirdSiteInfo and all of the data entered into the subform goes into a table called Bird_Census. I've attached a simple example of the Excel table that I need to import. The two tables are linked by the column labeled BirdSiteInfro_ID, which is an autonumber field created when data is entered into the main form. The SortID field is an autonumber generated in the subform to create a unique identifier for each line of data.
Part of my confusion about how to proceed involves how to deal with the autonumber fields. I can divide the Excel table into separate tables and then import those tables into SQL Server pretty easily, but I have no idea how that would affect the autonumber fields.
February 22, 2010 at 11:16 am
For only that many records, enter them by hand. You could have been finished by now. Or better yet, get the person that did not perform their job correctly to enter them. You have shown only 8 rows of something like 15 columns. Let the front-end do all the work for you.
You would still have to use SSIS, or create some Insert statements or stored procedures to enter this data one at a time, since you have a foreign key constraint. You need the RowID from the parent table in the child table.
Andrew SQLDBA
February 22, 2010 at 11:21 am
The table I included is only a small fraction of the table that needs to be entered and was just a sample of the table's format. The actual table has over 5500 rows of data that need to be entered. The data were entered into an Excel table because he couldn't connect to our database remotely.
February 22, 2010 at 1:44 pm
First of all you might want to add this site to your bag of tips and how-to's
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
The site has excellent T-SQL procedures easily downloaded to your machine.
Next you could consider using OPENROWSET to import the data into a "staging" table, which would allow you to verifty that the "SiteId' is valid and not a "fat finger" mistake in inputting to Excel / or identifying a new SiteId to be added to your system.
Here is code I have used to import data from Excel
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=G:\ATestExcel\TestImport.xls;HDR=yes',
'SELECT * FROM [Sheet1$a4:c]')
My results from your sample sheet
GF2bird
G1bird
G1bird
G1bird
G1bird
G1bird
Of course you will want to alter the starting row, start and end columns to better suit your data.
After verification of the data then insert into the appropriate table(s) using T-SQL with the source being the "staging" table. When importing
is complete just truncate the "staging" table and wait for the next Excelmport task.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply