Importing Excel Spreadsheets From Different Sources
Nov 20, 2007
I'm battling with importing sales data from different ERP systems into a SQL table. I have multiple spreadsheets, which have the same sheet name, column names and formats. I use a for each file loop container to loop through the different files. I use variables passed to a script to pull the relevant source information from the file name. I then use the variables to create derived columns in the flow to indicate the datadate and the company division.
My problem is even though all the data and metadata looks the same, there are a couple of columns between the two spread sheets that alway complain about the metadata not matching or a coumn needing to be added to the external metadata column collection and the same coumn name needs to be removed from the external metadata column collection.
These spreadsheets are coming from different countries but I have gone in and adjusted any fromats that are different from the excel spreadsheet used as the template. The same two columns out of 12 complain and I'm not sure what is causing the error. In the error is
Warning: 0x800470C8 at Read excel File, Excel Source [274]: The external metadata column collection is out of synchronization with the data source columns. The column "COGS" needs to be added to the external metadata column collection.
The "external metadata column "COGS " (2512)" needs to be removed from the external metadata column collection.
Eventually, I'll have 3 spreadsheets to load and need to find out why they are not viewed as the same data and metadata when they apear that way on the surface. I've searched numerous blogs out there (Even Jamie's) and can't find anything.
Thanks,
pkdenver
View 3 Replies
ADVERTISEMENT
Mar 24, 2008
I am trying to import data from named ranges in excel spreadsheets. I have no troubles if the named sheets have the same number of columns but I can not get the package to work when the spreadsheets have different number of columns. I am using the excel source as the input to my dataflow. I have tried setting it up with the maximum number of columns but if the named range has less than that, I am getting an error.
View 1 Replies
View Related
May 1, 2014
How can I use code (wither it be SQL or .Net Framework) to programmatically import 8 different Excel Sheets into One SQL Table (that currently does not exist)?
This is SQL Server 2005. Excel 2007.
View 14 Replies
View Related
May 17, 2007
Hi All.
Would you happen to know how one could convert some Excel spreadsheets to MSSQL? There is some commercially available software, but there must also be a way for me to manipulate the files.
Many thanks!
Nazli
View 3 Replies
View Related
Jun 4, 2015
We have a process here that produces several dozen Excel Spreadsheets every day from SQL Server using the bcp utility. the problem is that we cannot include the column headers when producing the Output. We are therefore trying to find a method where we can append the column Headers to the Spreadsheets; is there a way of doing this?
View 2 Replies
View Related
Jun 25, 2007
Hi, there
Some of the reports I am generating have tens of columns so the management decides to use Excel files only.
Is there any way that for a single report (not the whole project) I can disable printing and most of the exporting options (including PDF, HTML, TXT ...) and only leave the xls files available?
Thanks a lot.
Heng
View 1 Replies
View Related
Feb 13, 2006
Please help....
I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.
My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.
I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.
Thanks,
Dave
View 4 Replies
View Related
May 10, 2006
I am attempting to use the foreach loop structure in an SSIS package toloop through however many Excel files are placed in a directory andthen perform an import operation into a SQL table on each of thesefiles sequentially. The closest model for this that I was able to findin the MS tutorial used a flat file source rather than Excel. Thatinvolved adding a new expression to the Connection Manager that set theconnection string to the current filename, as provided by the foreachcomponent. That works just fine, but when I attempt to apply the samemethod to an Excel source, rather than a flat file source, I cannot getit to work. I see the following error associated with the Excel sourceon the Data Flow page: "Validation error. Data Flow Task: Excel Source[1]: The AcquireConnection method call to the connection manager "ExcelConnection Manager 1" failed with error code 0xC020200." I think thatit's just a matter of getting the right expression, and I thought thatperhaps I should be constructing an expression for ExcelFilePath ratherthan the Connection String, but I have fiddled with it for hours andhaven't come up with something that will be accepted. Has anybody outthere been able to do this, or can perhaps refer me to somedocumentation that contains an example of what I am trying to do?Thanks for any help you can give.
View 1 Replies
View Related
Oct 12, 2015
In SSIS 2008R2, I have a dataflow with an xlsx source and the destination is a SQL Server 2008R2 table. The files are delivered from a location where staff members 'work with' the source files. The files are produced monthly.
The dataflow that contains the file breaks upon the attempt to process subsequent monthly xlsx files with a message similar to the following:
--*************
[TNUQQ [16]] Warning: The external columns for component "TNUQQ" (16) are out of synchronization with the data source columns. The column "F12" needs to be added to the external columns.
The external column "county_taxable_sale_amount" needs to be updated.
The external column "city_taxable_sale_amount" needs to be updated.
The external column "district_taxable_sale_amount" needs to be updated.
The external column "QTY" (62) needs to be removed from the external columns.
--*************
I've noticed that some columns in the file ship with no data. A column with no data can be typed as datetime one month, and then float another month. I've tried to load xlsx to raw to table, but that does not work around this issue.
I've tried to set 'ValidateExternalMetadata' to 'False' on the  Excel source, but that does not work either. Aside from going back to the folks who ship the file to us, is there anything that can be done in SSIS to work around this issue, and still wind up with valid data?
View 5 Replies
View Related
Sep 22, 2015
I have a requirement where we have to connect to two different data-sources one is Database Table and another one is Excel Source. I know we can do this by creating a SSIS package and loading the excel data into database table and then based on some relationship between the tables we can create the data-set in SSRS. But I am looking for some other approach where I can do this without creating the SSIS package.
View 4 Replies
View Related
Mar 19, 2007
Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
Thnx :)
Wouter de Kort
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
