Importing Excel To Database Problem

May 6, 2008

Hi,

I need to import data from excel to sql database. I have an excel that has a column called dept_name that is a string.
In the database I have a table called tb_depts with the following columns:
dept_id int, identity
dept_name nvarchar (200)

In addition I have another table called tb_extUsers with the following columns:
user_id int, identity
user_name nvarchar (200)
dept_id int

I need to import the excel into the table tb_extUsers however my problem is that the value in excel is the name of the department and not the department id and the column in tb_extUsers requires the id.
How can i insert the correct dept_id into tb_extUsers ?

Can someone please assist me?

Thanks






Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 4 Replies


ADVERTISEMENT

Need Help Importing Data From Excel To My SQL Database

Jan 16, 2008

Hey guys,I am trying to import data from an Excel spreadsheet into my SQLdatabase. I am running SQL 2005.I following Microsoft's instructions for creating a linked server, andit appeared to work. However when I run this query:SELECT * INTO test FROM OPENQUERY(EFORMS,'SELECT * FROM [Form Tracker Baseline$]')I get the following error:The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"EFORMS" reported an error. The provider did not give any informationabout the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider"Microsoft.Jet.OLEDB.4.0" for linked server "EFORMS".Test is the table that will receive the imported data. Eforms is thename of my linked server, and Form Tracker Baseline is the worksheetname.The error sounds like it can not locate my spreadsheet. Any ideas whyI am getting this message? Is there an easier way to do this import?Thanks,

View 4 Replies View Related

Importing Excel File Into Database

Sep 4, 2007

Hi, I am very new to ssis, I am trying to set up a package that I can set to run every hour or so, what it will do is look for all excel files in a certain folder and import them into a table on an sql server.

I managed to get it working but my problem is that my data is survey answers and some columns contain a comment.
I get these files on a weekly basis and some weeks the length of the longest comment makes ssis want to use a different data type for the comment column (sometimes it wants DT_NTEXT, other times it wants DT_WSTR).
as this column is filled out by a human I guess aposrtophies and other characters may affext this as well.

when I made the data flow task, I used the wizard on a file which contained about 8 weeks worth of data. when I use 1 weeks worth of data where the comment length is very low, the task gives a validation error saying the metadata is out of sync. if I go back and set the data type for that column to DT_WSTR and rerun the task, it works but then when it tries to process a different weeks worth of data it will fail again

here is an example of an error I get when it thinks the data type is wrong.
[Source - 'raw data$' [1]] Error: The output column "Question ID50# (Verbatim)" (439) on the error output has properties that do not match the properties of its corresponding data source column.

I played around with the data types for a while and managed to get it to process the first file and then try to process the secondfile, in the second file it got around the validation but then got this error:
[Source - 'raw data$' [1]] Error: Failed to retrieve long data for column "Question ID3# (Verbatim)".

is there a way to make it recalculate the data types by itself for each excel file?


I am stuck trying to figure this one out.
sorry if I havent provided enough information, I am not sure which direction to head with this

View 4 Replies View Related

Importing Data From Excel To SQL SERVER Database

Aug 23, 2006

Hello all:

I have gone through most of the question posed by people, about importing data from EXCEL sheet to some table using SQL server database. I have a slight variation of this problem.

My excel file contains some information apart from the normal data.
Lets say some 5-6 lines always gives me some info about the data, like its purpose, client info, date etc...
After this INFO my actually data start, which I want to load into table.

I have found some wizard for the same, "EMS SQL MANAGER 2005", which supports most of the file formats, and load data into the database.

But we are planning to not use this tool, instead everything should be done using TSQL.

If somebody can please gives me some idea how this problem can be tackle, it would be a great help. We won't be using any Third party tools, like scripting etc...

We want to stick with just TSQL for this problem.

Thanks a bunch,
sabegh

View 1 Replies View Related

Error Importing From Excel To Odbc Sql Database

Dec 17, 2007

Got an error of

Error during Transformation 'DirectCopyXform' for Row number 813. Errors encountered so far in this task: 1.
TransformCopy 'DirectCopyXform' conversion error: Destination does not allow NULL on column pair 1 (source column 'Customer' (DBTYPE_R8), destination column 'Customer' (DBTYPE_STR))

What does that mean?

I have an excel file named Customer List with fields such as Customer, Name, City.
I want to import it into a syspro table called SyproCompanyF.ArCustomer.

View 4 Replies View Related

Importing Data From Excel Sheets Into SQL Server Database

Feb 15, 2006

Hi,
Would like some help on how do I go about coverting an Excel File with columns of info into my SQL Server Database. The excel file will be uploaded from a user from my web application. I completely have no idea on where to start so any form of help is much appreciated thanks.

View 3 Replies View Related

Issue In Importing Data From Excel Into Database Table

Mar 7, 2008



Hi

I am facing issue while loading data from excel into Sql database table.
The format of spreadsheet is like

Date -
Name - A B C
Section Marks
X1 10 10 10
X2 20 30 10

X3

I need to load this data into table with columns

Date
Name
Section
Marks

Please share your thoughts on how to load this kind of data into table.

Thanks
Anshu

View 1 Replies View Related

Importing An Excel Spreadsheet Into A Sql Database Using Sql Server 2005 Express

Nov 29, 2006

hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?   

View 3 Replies View Related

Importing An Excel File And Save Data Into A Database In My Web Account

Aug 30, 2007

Hi everyone, sorry if this message is not supposed to be posted here.
I'm learning asp.net , and would like to know how I can insert data from a excel file into a database on my web account. Pretty much insert/update information in the database using excel file or a access file. 
Thanks a lot in advance

View 3 Replies View Related

Importing Data From Excel To Database - Unexpected Unrecoverable Error

Jan 1, 2015

I want to import data from excel (1.2 million rows in 1 sheet) to SQL Server 2014 database. I am using SQL Server 2014 Import Export Data functionality and getting this error - unexpected unrecoverable error.

3 buttons beside it - abort, retry, ignore - all having the import export to close.

But I could successfully import data from excel file having less rows (150000 rows)

View 1 Replies View Related

TSQL + VBA Excel 2003 - Importing Data From MS Excel 2003 To SQL SERVER 2000 Using Multi - Batch Processing

Sep 11, 2007

Hi,
I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000.
The string I need to import is composed by 5 different several blocks and looks like:



Code Snippet

CommandLine01 = "USE mydb"
CommandLine02 = "SELECT Block ..."
CommandLine03 = "GO
ALTER TABLE Block...
GO"
CommandLine04 = "UPDATE Block..."
CommandLine05 = "SELECT Block..."

The detail of the SQL string is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1



I am trying to implement OJ's suggestion:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1
to use multi - batch processing to import the string to SQL SERVER, something like:




Code Snippet
Dim SqlCnt, cmd1, cmd2, cmd3
'set the properties and open a connection

cmd1="use my_db"
cmd2="create table mytb"
cmd3="insert into mytb"

SqlCnt.execute cmd1
SqlCnt.Execute cmd2
SqlCnt.Execute cmd3

Below is the code (just partial) I have, and I need help to complete it.
Thanks in advance,
Aldo.




Code Snippet
Function TestConnection()
Dim ConnectionString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet

ConnectionString = "Driver={SQL Server};Server=myServer;Database=myDBName;Uid=UserName;Pwd=Password"
ConnectionString.Open

CmdLine01 = " USE " & myDB
CmdLine02 = " SELECT ACCOUNTS.FULLNAME FROM ACCOUNTS" ...

CmdLine03 = "GO
ALTER TABLE Block...
GO"

CmdLine04 = "UPDATE Block..."
CmdLine05 = "SELECT Block..."

RecordSet.Open CmdLine01, ConnectionString
RecordSet.Open CmdLine02, ConnectionString

ConnectionString.Execute CmdLine01
ConnectionString.Execute CmdLine02

'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next

ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet

'Close ADO objects
RecordSet.Close
ConnectionString.Close
Set RecordSet = Nothing
Set ConnectionString = Nothing

End Function






View 7 Replies View Related

Importing An Excel

Apr 20, 2001

I have an excel file with 5 columns. 1st column values are -

^^234
^^456
678
123
456

SQL 2000 DTS, doing straight copy into an Access table takes only first two
rows from 1st column ( see top 2 rows with trailing white spaces). How can I
import all of rows ?

Thanks for your assistance !

_Ivan

View 1 Replies View Related

Importing From Excel To Ms SQL Without DTS

Apr 25, 2006

Is there any way at all to import from Excel to MS SQL without using the DTS package and without doing it manually? I really do not like the lack of flexability that DTS has and I need something else that will always work. Basically I am importing shipping documents that are in excel and putting them into a table. This has 2 issues, 1 I don't know the file name, just the folder it is in and 2 there is a large amount of header data in the file, some of which I need, some of which I don't. Is there any solutions out there? Thanks!

View 1 Replies View Related

Importing From Excel

Aug 21, 2004

Hi there, could let me know how to reference to an excel database in SQL code?

I think

Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:MyExcel1.xls',Sheet1$)

Would import everything from a spreadsheet but im not sure how you can reference individual columns?

View 1 Replies View Related

Importing From Excel

Aug 26, 2004

I need to copy the data in two spreadsheets into two
tables in "SQL Server 2000". Below are the details:


One spreadsheet contains the data that needs to be added
to an existing table in SQL server. All field names in the
spreadsheet match the DB table column names.
Another spreadsheet contains the data that needs to be
copied to a new DB table. The table currently does NOT
exist in the DB.

I'm not sure how to accomplish this. ANy help would be
appreciated.

View 2 Replies View Related

Importing SQL To Excel Almost There

Oct 23, 2006

Hello Everyone,

Here is my code in Excel VBA:


Sub command_dyer3()
Dim conn As New Connection
Dim rec As New Recordset
Dim comm As New Command
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("command")
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "FromDyer.mdb;"
Set comm.ActiveConnection = conn
comm.CommandText = _
strSQL = "USE CHEC" & _
"SELECT DISTINCT" & _
"DAT01.[_@051] AS Branch," & _
"DAT01.[_@550] AS LoanType," & _
"DAT01.[_@040] AS Date," & _
"DAT01.[_@LOAN#] AS LoanNum" & _
"FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]" & _
"ON DAT01.[_@040] = [_@040]" & _
"INNER JOIN [SMT_BRANCHES]" & _
"ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]" & _
"WHERE" & _
"DAT01.[_@040] Between '06/01/2006' And '06/30/2006'" & _
"AND DAT01.[_@051] = '540' " & _
"And DAT01.[_@LOAN#] Like '2%' " & _
"And DAT01.[_@550] = '3' " & _
"Group BY" & _
"DAT01.[_@051]," & _
"DAT01.[_@550]," & _
"DAT01.[_@TP]," & _
"DAT01.[_@040]," & _
"DAT01.[_@LOAN#]" & _
"ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH," & _
"DAT01.[_@051]"
comm.CommandText = strSQL
'comm.Parameters(0) =
rec.Open comm
ws.[a1].CopyFromRecordset rec
rec.Close: conn.Close
End Sub


I get the following error mesage:

Command text was not set for the command object on the following line:

rec.Open comm

What am I doing wrong here?

TIA



Kurt

View 20 Replies View Related

Importing From Excel

Dec 14, 2007

I have a list (200+) that I would like to pull off of an excel sheet and insert into the table. I have the script,

INSERT INTO tax_jurisdiction SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:TaxJuris.xls;Extended Properties=Excel 8.0')...[TaxJuris$]

I get the error: Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

I'm at a loss. I've looked at all the fields and modified the deceimal fields to number format. What else should I be looking for?

View 2 Replies View Related

Help With Excel Importing

Jul 19, 2007

I have been working on some code to get excel data into a datagridview and also into my database.

What i have works to get the data but it does not display it in the view

It does show that there are records but will not show any data

It shows that there are 9 records that i can navigate but all it does it does is change the record number.

And it shows one blank row.



I have seen quite a few threads and examples but i can't quite put this one together.

Can someone help me get the data into the view?

Thanks



Dim opendlg As New OpenFileDialog

opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then

Dim pathname As String = opendlg.FileName

'MsgBox(pathname)



Dim connect As System.Data.OleDb.OleDbConnection

Dim adapter As System.Data.OleDb.OleDbDataAdapter

Dim dataset As New System.Data.DataSet()

connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=" & pathname & ";Extended Properties=Excel 8.0;")

'C: estoledb.xls

adapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", connect)

connect.Open()

adapter.Fill(dataset)



this is where the problem is

Me.Table1DataGridView.DataSource = dataset

adapter.Fill(Me.Database1DataSet.Table1)



connect.Close()

MsgBox(CType(dataset.Tables(0).Rows(0).Item(0), Object).ToString)



End If

View 2 Replies View Related

Importing Excel

Jul 17, 2007

Hi,



I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.



Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.

View 10 Replies View Related

Need Help Importing Excel

Oct 4, 2006

Hello,

I am very new to sql server 2005, and i wanted to know if anyone can help me in creating a SSIS packge that can do the following:

1. Import an excel sheet
2. Delete uneeded columns
3. Merge two fields together, only if first column is not null, and if it is null delete that record.
4. Dedupe records
5. export into dbf format
6. If possible, records that get exported get marked as exported in database.

Any help would be very much appreciated.

View 3 Replies View Related

Importing And Excel File

Jul 31, 2007

How do i import a Excel file into a table i have created in my database in SQL server 2005??? 

View 10 Replies View Related

Importing Data From Excel Into Sql

May 23, 2008

Hi i have an excel spreadsheet in which I want to take the data and put them in a table, the table and excel speadsheet have the same unique-ID, what i need to do is retrieve the extra fields of the excel spreadsheet and match them up with the table. Is this possible, if so how?

View 6 Replies View Related

Importing Excel To Sql Server....

Jun 27, 2005

Dear AllI am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables'  list but when i execute the code again, system throws an exception that table already exists.On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.Regards<code>Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\annieanna.xls;Extended Properties=Excel 8.0;")ExcelConnection.Open()'For existing Table.................METHOD 1Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection)
'For new Table......................METHOD 2Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)ExcelCommand.ExecuteNonQuery()ExcelConnection.Close()</code>

View 3 Replies View Related

Importing Excel Data

Feb 3, 2006

Here is the scenario: I have an excel spreadsheet that contains 182 columns, and I need to move this data into a semi-normalized database for reporting. The SQL Server database schema has 11 tables. Some of the tables are going to use identity columns for their PK, other tables are using a value that comes from this spreadsheet for their PK values.Anyway, I have never done a DTS package of any significance before, and know I most likely need to write some VBScript to handle sticking data into the proper data tables, etc.I am just hoping someone can point me at a good resource, give me an alternative means of doing this (this is a process that will need to happen whenever a new Excel spreadsheet is dropped into a folder or on a schedule, either one). I would love to write some C# code to handle these things, but a DTS package would probably be the best, I just don't know where to start.Thanks,

View 2 Replies View Related

Importing Excel To Sql Table

Jan 24, 2008

i used to be able to import Excel spreadsheets and convert them into SQL tables using MS SQL Server Enterprise Manager

however, i haven't been able to find out how to do this in Visual Studio 2005 - does anyone know how to do this (or whether it can be done at all) ?

View 1 Replies View Related

Importing Data From Excel

Apr 17, 2006

I have an excel document that I need to import into a table. The format of the Excel spreadsheet has three columns:

|First Name | Last Name | Zip code|

This data is not normalized. I want to convert the data in the spreadsheet to foreign key values for two existing tables.

The table I'm importing to will contain records with a many-to-many relationship.

For example,

|John|Smith|77079

imports as:

|1|1|

Where "Smith" is the first record in the 'rep' table and "77079" is the first record in the 'zip' table.

How can I bring the data in and then convert the de-normalized data into ID values from my existing tables?

I have thousands of records that I need to do this for and want to automate the process.

Thanks for any help.
Regards,
-D-

View 1 Replies View Related

Importing Excel Into MS SQL Table

Sep 12, 2006

Hi.

I have done this successfully the last time but just couldn't get it to work this time round. Please help.

I was trying to import data from an Excel file into MS SQL table. NONE of the numeric or value fields (right justified) works because they shown <NULL> in MS SQL table after the import via DTS. Those string fields were able to import ok into the table. DTS didn't show any error message during the DTS run.

Any advise? Thank you.


Best regards
Teck Boon

View 3 Replies View Related

Importing Excel Xp To Sql 2000

Feb 3, 2004

i have a spreadsheet with 4 workfiles and i want to import into sql. i want to make one workfile into one table in sql
how do i go by doing this? and spreadsheet has a lot of macro too
thanks

View 1 Replies View Related

Importing Data From Excel To SQL

Apr 14, 2008

I am using SQL 2005 the developer edition and need to import a couple thousand records while I try and create a web tool that will filter the data.

I know unltimately I will need to create some sort of application to move the data over. But I am not going to waste my time do that unless I am successful on the web site side.

What is an easy way to import the data. I tried the import wizzard multiple times and have recieved an error...multiple times.

View 6 Replies View Related

Importing Tables From Excel

Sep 26, 2007

Hello,
Am very conversant with MS SQL 2K, however i just downloaded 2005 express edition and will like to know how to

1. How to import tables using DTS graphic view to Management studio

2. How to get to Query Analyzer ?

thanks
Mary

Yes O !

View 1 Replies View Related

Importing From Excel Using SSIS

Dec 17, 2007

Hi am trying to import data from a excel file into my 2005 DB using a SSIS package.

This first thing i've done is create a Excel source and then a derived column task as i need to format my date, so am using substring to format the date but the expression am using will not work am geting a error on it

the data in the excel file is like 8122007
here is my expression

substring(date,1,1) +"/"+ substring(date,2,2) +"/"+ substring(date,4,7)

Any idea i think it's something got to do with the data type

View 9 Replies View Related

Importing From Excel Problems

Dec 11, 2007

What do others do if you need to import excel files into SQL Server?My main problems are1) zipcode formatting issues. If the column is a mix of zip and zip+4,I have problems retrieving all zipcodes.2) If the last column contains NULL no information is imported.All this with using the Management console using Import data in SQLServer 2005. I am simply trying to import the data into NEW databases.The excel files vary in structure. Right now I am working on case bycase basis.Does anyone see these types of problems?What I am doing now is converting the excel file to a tab delimitedfile and that seems to work.TIA.

View 3 Replies View Related

Best Way Of Importing Excel To SQL Server

Jul 20, 2005

Hello,I nee to write something that will transfer excel data into an SQL Servertable.I have for another database application I wrote have it importing Excelspreadsheet data using cell by cell, row by row method. This is fullyautomated so the user can choose whatever spreadsheet they want to importand press a button which sits on a VB6 frontend.This has been good for that situsation but it can be very slow when thereare large amounts of data to process.I am just wondering are there any faster, better alternatives that wouldstill enable a user to select which excel spreadsheet to importas the application I am writing now will sit on a website frontend, usingASP, and I'd really like to try and speed things up if I could.any advice would be much appreciated.Thankyou,Oh, and hello, this is my first post here!Jayne

View 4 Replies View Related







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