imports table structures in SQL 2000 into Excel

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

AgustinaRun this in Query Analyzer: (common data types, add the the case statement for more)

select name,
case xtype
 when 56 then 'Int'
 when 127 then 'BigInt'
 when 167 then 'VarChar'
 when 175 then 'Char'
 when 60 then 'Money'
 when 58 then 'SmallDateTime'
 when 104 then 'Bit'
 when 173 then 'TimeStamp'
 when 61 then 'DateTime'
 when 48 then 'TinyInt'
 else 'Other' end,
length
from syscolumns
where id = (
select id
from sysobjects
where name = 'TheTableName')
order by colid

|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '<DATABASE NAME>' AND TABLE_SCHEMA = '<DB OWNER>' AND TABLE_NAME = '<YOUR TABLES NAME>'

sql

importing to swl server from a flat file PK problem

I am trying to import into sql server from a .csv flat file i made in excel. The problem is the Primary Key. It is set to auto increment.
When i leave an empty column i get:
"Directcopyxform conversion error:destination does not allow null"

when i just omit the column entirely, i get a wrong datatype error because it basically tries to copy all the columns 1 shift to the left

How am i supposed to represent and auto PK in .csv file. ThanksUse a staging table. Where you can load the information to, perform any processing, then insert into your table. Finally cleaning out the staging table at the end.

Importing non-hidden rows from Excel?

I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.

Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?

Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.

Thanks!

Matt Michuta

No, there is no setting like this. You at the mercy of the Excel OLE DB Provider here and it doesn't possess functionality like you are requesting. You will have to filter those rows out in the pipeline.

Not sure about the sheet index problem - I don't ever use the Excel provider. I suspect you can't do that either. Perhaps try the data access forum if no-one here knows: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

|||You should be able to do this with a little bit of creative vba coding in excel. You can write a macro that will copy all the data to a sheet that has a static name and link the action to an event or button. Since you can use sheet indexes and row properties in vba you can get exactly what you need, and since the sheet you're copying to has a static name you can use the excel source in SSIS to import it.

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names
.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Importing multiple Excel Files

I have a client who is sending me 800+ excel files each month with sales data. Each of the files is identical in structure, but has sales data for different stores. I receive all these files at the same time.

Is there a method with Data Transformation Services where I can have it work off of all the files in a given directory. I can set up DTS to work off of specific Excel files with no problem, but what I would like to do is set up a DTS so it could pull from each of the 800+ files.

Is this possible, or do I need to look at a solution outside of SQL to consolidate the Excel files first?

The Excel file would have columns similar to the following: store_id, zip_code, sales, transactions.There are lots of different ways to handle this kind of problem.

By far my largest concern with 800+ Excel files is GIGO (Garbage In, Garbage Out). Even one sheet of undetected junque data, and you can't get a correct answer! Before I can even suggest a technical solution, I need to understand what the risks of bad data are for you, and how much manpower you are willing to commit to either detecting or correcting the bad data... That answer will strongly influence what I suggest for a solution.

-PatP|||In terms of the quality of the data, it comes directly from my client, so as long as what I have stored in our database matches what they gave me, I am in good shape. I run some additional reports from our application to spot anomalies in the data quality.

In terms of confidence in the file structure integrity: the client isn't very sophisticated (i.e. they cannot generate one file with all the stores in it), but their result file is very simple and routine. Last month was the first time I had gone through this process with them, and all 843 files were clean.

In terms of manpower... I'm a one man technical staff for a company with 50--70 employees, and several clients, and the database management / design is only one part of my responsibilities. Considering I would be doing this particular task once a month, 30 min - 45 min of effort would hopefully be the max I would have to spend.

Having done manipulation of customer / client files for several years now, I know to expect the unexpected; and GIGO is always one of my biggest concerns. Having no support staff to help, limits the effort I can make, but I need to know that what I received is what I loaded. If something cannot be loaded, identifying that would be great.

I don't mind loading the data into a staging area... and then doing some post-load validations in SQL before moving the data to the intended tables in the database. From my perspective, the key is getting it out of the Excel format, which is terribly unfriendly for me to validate against, into a format / structure that I can.

Does that help guide you?|||I am not sure about DTS, because scripting is my preference, so there's gotta be someone that would come up with a DTS solution (I've seen a couple of samples on the net that demonstrate ennumeration of files in a directory from a DTS/SSIS package).

But in scripting world you can have a batch file that would dump a list of files into a file queue table, and then extract a script-like output into a TSQL script that you can run in the next step with osql/sqlcmd.

When I had to deal with Excel files in the past, I ended up building linked servers on the fly for each file in order to be able to handle everything in TSQL (plenty of references on the net as to how to create a linked server to an excel spreadsheet).|||You can do this in DTS by defining a filename variable and using VB to cycle through the files. So what you do depends upon whether your comfort zone is in VB or in scripting.

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

sql

Importing from Excel to Sql Server 2005

I have about 50 Excel files from which I have to import data with some transformations to Sql Server. My first approach was to use Excel Source component in a data flow to read the data . However, as it turned out column X in some files was being converted to a DT_NTEXT blob and in other files it was mapped to WSTR. The reason I guess is because column X contained string of varying sizes - some greater than 255 characters while others less than 255 ( max was 3000 ) . My package used a ForEach loop to iterate over all the Excel files in a directory and feed that to the data flow task. I played around with IMEX and TypeGuessRows setting but they didnt help me . In my second approach I used 2 Excel sources ; one set up for the blob type and the other for the string type . I joined them together using a precedence contraint. This worked but I then figured out that there were 2 other columns in my data that exhibited the same behavior. I couldnt continue with the mulitple Excel source approach cause I would then have 8 Excel source components. Finally, I played around with Execute SQL Task . I selected the columns X,Y and Z , initialized 3 variables of type Object , used a ForEach to enumerate over the dataset and feed that to a script component that converted the objects to Strings. This seems to work for all types of data in the mulitple columns.

My question - has anyone encountered such problem ? What was the solution ? Just thought I would share this with the rest of the community. I cant seem to recall what the exact error I was getting ..but it was something like "cant convert long data to string " or something . I also keep getting annoying error icons in my Excel Source components used in the foreach loop. Something to do with acquire connection failed even after I set DelayValidation to "true".

Thanks

Another approach would be to use a conditional split component to redirect the two sets of files to two different data conversion components. You can then use the conversion components to convert the column X in both sets of files to Strings.

Carla

|||

Hi:

You have hit the most troublesome issue with the Jet Excel engine. I have found no easy way around for this problem. I have worked around the issue by having a dummy row in the second row of all my files. This dummy row contains dummy text >255 chars for all columns that can have >255 chars. My typeguessRows registry key is set to 2 and my connection strings have IMEX=1. This forces all these fields to type DT_NText.

I tried casting datatype in my select query, but Jet does not seem to support it. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1758786&SiteID=1

Please let me know if you find a solution.

HTH

Kar

|||

The solution was to use the third option which is using Execute SQL Task and mapping the troublesome columns to variable of Object type . You would then use a script component to cast the columns to String type. That seems to work right now.

Let me know if you have any further questions.

Importing from Excel problems

What do others do if you need to import excel files into SQL Server?
My main problems are

1) 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 SQL
Server 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 by
case basis.

Does anyone see these types of problems?

What I am doing now is converting the excel file to a tab delimited
file and that seems to work.

TIA.On Dec 11, 12:32 pm, scoots987 <scoots...@.gmail.comwrote:

Quote:

Originally Posted by

What do others do if you need to import excel files into SQL Server?
My main problems are
>
1) 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 SQL
Server 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 by
case basis.
>
Does anyone see these types of problems?
>
What I am doing now is converting the excel file to a tab delimited
file and that seems to work.
>
TIA.

Hi scoots987,

I usually use a dummy row in my excel files to force the correct data
types and column mappings (typically I import everything as text and
convert it downstream). One of the big problems with importing data
from an Excel file is that SQL Server (well .. the I think it's
actually the OLEDB driver) only looks at the first 8 rows of data to
determine what data types to use. To force it to look at more rows,
you need to change a couple registry settings, which in my experience
is usually off-limits in a managed production environment (check out
http://blog.lab49.com/?p=196 for info .. it's not a SQL blog, but it
explains the issue well).

Good luck!
J|||I've recently had great experience importing Excel into the DB using
SSIS (SQL Server Integration Services) and the OLE DB Excel Connection.
It has transforms and all sorts of goodies to make the import easy and
omplete.

HTH

aj

scoots987 wrote:

Quote:

Originally Posted by

What do others do if you need to import excel files into SQL Server?
My main problems are
>
1) 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 SQL
Server 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 by
case basis.
>
Does anyone see these types of problems?
>
What I am doing now is converting the excel file to a tab delimited
file and that seems to work.
>
TIA.

Importing from Excel into Datbase tables

Hi
I have data in excel file which I wanted to import into sql tables, and I
wanted to do this programatically since I have to move the data first to
master table and then use the Master record key to insert into child table s
o
that i can have relationship data.
How can I do this with DTS?
Sample code will be much appreciated...
MakarandYou can use OpenRowset() to query data from excel like a *normal* table.
This should allow you to do insert/update/delete to your sql table.
select * from
OPENROWSET('Microsoft.Jet.OLED_B.4.0','Excel
8.0;Database=C:\Data\test.xls'_,Sheet1$)
--
-oj
"Makarand Keer" <MakarandKeer@.discussions.microsoft.com> wrote in message
news:8B4688A0-C5BE-48BC-A023-63284616DC7B@.microsoft.com...
> Hi
> I have data in excel file which I wanted to import into sql tables, and I
> wanted to do this programatically since I have to move the data first to
> master table and then use the Master record key to insert into child table
> so
> that i can have relationship data.
> How can I do this with DTS?
> Sample code will be much appreciated...
> Makarand|||Hi
This sounds like you want to load the data into a holding table and then use
queries to distribute it from there.
Loading into the holding table is just the same as any import from Excel,
you can use the import wizard to initially create the package or do it
manually.
To moving data from the holding table is probably best undertaken if you
wrote a stored procedure. In your backage you could as and "Execute SQL
Task" to execute the procedure. You can test the procedure out from query
analyser without having to call/step the package all the time. Using
statements such as INSERT...SELECT will allow you to choose which columns
from the holding table map onto the destination columns of the live tables.
Books online has example of the INSERT statement and information about DTS.
This site also has many examples http://www.sqldts.com/default.aspx?101
John
"Makarand Keer" <MakarandKeer@.discussions.microsoft.com> wrote in message
news:8B4688A0-C5BE-48BC-A023-63284616DC7B@.microsoft.com...
> Hi
> I have data in excel file which I wanted to import into sql tables, and I
> wanted to do this programatically since I have to move the data first to
> master table and then use the Master record key to insert into child table
> so
> that i can have relationship data.
> How can I do this with DTS?
> Sample code will be much appreciated...
> Makarand

importing from excel

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.use DTS
If its one time work you can just query the Excel tables and inser the data to existing table
or Import and Export data|||They want to append the data, but this is the problem I'm having now...

The first row of the spreadsheet matches the column heading in the table - but when I go to import the spreadsheet, it is showing me THREE seperate tables or views on the Select Source tables/views screen. I don't know why this is if I'm importing from ONE spreadhsheet... unless the spreadsheet is formatted incorrectly. I went ahead and selected the first one and set my destination table and I when I run it, i get an error: Violation of Primary Key constraint 'PK_Faultresolutions' cannot insert duplicate Key in FaultResolutions.

maybe this is somethig simple.. but can anyone help??sql

Importing from excel

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?Select column_name from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:\MyExcel1.xls',Sheet1$)
where .....

Importing from Excel

I am having a problem with DTS importing NULL values from an Excel spreadsheet when there are numbers and text in the same column. Other than that, the import works fine. I am using VBScript in an ASP web page to create and execute a DTS package to import an Excel file.

I have read Allan Mitchell's article about using IMEX=1 (http://www.sqldts.com/default.aspx?254). I added IMEX=1 and modified the registry setting, but when I do that NO data at all is imported.

Any ideas? My code is very long, so I'll only post it if you'd really like to see it.

Much thanks in advance!

BillI would export the excel file into a delimited file - the ms driver for excel causes too many headaches. Also, did you modify the TypeGuessRows setting in your registry ?|||Originally posted by rnealejr
I would export the excel file into a delimited file - the ms driver for excel causes too many headaches. Also, did you modify the TypeGuessRows setting in your registry ?

I did modify the TypeGuessRows setting to 0. Is there a way to programmatically export the Excel file to a CSV file?

Thanks,

Bill|||It has been a while since I used the TypeGuessRows - but I believe setting it to 0 will not work - you have to explicitly supply a value (something big enough to sample your data).

Programmatically - Do you mean from within sql server or any executable ?|||Originally posted by rnealejr
It has been a while since I used the TypeGuessRows - but I believe setting it to 0 will not work - you have to explicitly supply a value (something big enough to sample your data).

Programmatically - Do you mean from within sql server or any executable ?

I read somewhere to set it to 0 and use IMEX=1. I just tried setting it to 16 and 8, but neither of those worked either.

Actually, when I said programatically I meant VBScript, but I guess that's for the VBScript forum!

Thanks,

Bill|||You can create an excel object - open the workbook - use the saveas method for the workbook.|||Originally posted by rnealejr
You can create an excel object - open the workbook - use the saveas method for the workbook.

Where can I find information on using the excel object's saveas function? I'm sure there are a number of options, and I'd like to take a look at them.

Thanks!

Bill|||Here is the snapshot from the help (Also, look for a vbaxl9.chm file on your computer):

Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.

Syntax 1

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Syntax 2

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)

expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2).

Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you dont, Microsoft Excel saves the file in the current folder.

FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.

WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isnt supplied when the file is opened, the file is opened as read-only.

ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.

CreateBackup Optional Variant. True to create a backup file.

AccessMode Optional Variant. The workbook access mode. Can be one of the following XlSaveAsAccessMode constants: xlShared (shared list), xlExclusive (exclusive mode), or xlNoChange (dont change the access mode). If this argument is omitted, the access mode isnt changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.

ConflictResolution Optional Variant. Specifies the way change conflicts are resolved if the workbook is a shared list. Can be one of the following XlSaveConflictResolution constants: xlUserResolution (display the conflict-resolution dialog box), xlLocalSessionChanges (automatically accept the local users changes), or xlOtherSessionChanges (accept other changes instead of the local users changes). If this argument is omitted, the conflict-resolution dialog box is displayed.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel.

TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel.

The following are the available file formats:

xlAddIn
xlCSV

xlCSVMac

xlCSVMSDOS

xlCSVWindows

xlCurrentPlatformText

xlDBF2

xlDBF3

xlDBF4

xlDIF

xlExcel2

xlExcel2FarEast

xlExcel3

xlExcel4

xlExcel4Workbook

xlExcel5

xlExcel7

xlExcel9795

xlHTML

xlIntlAddIn

xlIntlMacro
xlSYLK
xlTemplate

xlTextMac

xlTextMSDOS

xlTextPrinter

xlTextWindows

xlUnicodeText

xlWJ2WD1

xlWK1

xlWK1ALL

xlWK1FMT

xlWK3

xlWK4

xlWK3FM3

xlWKS

xlWorkbookNormal

xlWorks2FarEast

xlWQ1

xlWJ3

xlWJ3FJ3|||Awesome, thanks!

So if I do this:

<code>
set xlApp = CreateObject("excel.application")
xlApp.Workbooks.Open <filename>
xlApp.ActiveWorkbook.SaveAs <newFileName>, xlCSVWindows
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
</code>

It would save it as a CSV file?

Thanks!

Bill|||Yes, that looks good. Let me know if there are any problems.|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Thanks, I'm going to try to give it a test today!|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Okay, I tried that code and I'm getting the following error message:

Microsoft Excel error '800a03ec'
SaveAs method of Workbook class failed

Any ideas?

Thanks!

Bill|||Originally posted by rnealejr
Yes, that looks good. Let me know if there are any problems.

Okay, I may have found the issue, but I'm not sure. I saved the current workbook file format value to a variable and printed it to the screen. The value was -4143. Is the fileformat value supposed to be a number or the values from the help file (i.e., xlCSV)?

Thanks for all your help!

Bill|||I do not have to vb at the moment to confirm but the xlCSV value should be and the xlWindowsCSV is 23.|||Also, sorry for the delay - your post got buried.|||Originally posted by rnealejr
I do not have to vb at the moment to confirm but the xlCSV value should be and the xlWindowsCSV is 23. \

No problem on the delay, I really appreciate the help - is there a list of these values anywhere? I've been looking, but I must not be using the right terminology.

Thanks!

Bill|||This link might be helpful:

link (http://techsupt.windowware.com/TS/T000001033005F9.html)

importing excel xp to sql 2000

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
thanksLook into DTS. Check SQLDTS.com for tips and tricks.

Importing excel to sql server....

Dear All
I 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 ExcelConnectionAsNew System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\annie\anna.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()
'For existing Table.............METHOD 1
Dim ExcelCommandAsNew 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 2
Dim ExcelCommandAsNew 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>

You are missing linked server because Excel only knows Access SQL not T-SQL. Try the code below. Hope this helps.

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||Hi
Thanx for reply but i dont want to create tables own my own because their are more than five hundred excel sheets to import. I need fastest mechanism to imort excel data to sql server!!!
And second problem is that when i export data from sql server to excel then excel is deformating the data e.g. i have a string "000001" in sql server table but excel sheet used to make it 1 (numeric). I am using clipboard to copy data from sql server to excel sheet (It is the only fastest way to export data from sql server to excel).
Please help.|||Execl is not a RDBMS it is a flat file application to do what you want you have two choices DTS on DTSRUN exe or DTS on xp_cmdshell and Sharepoint Portal. Hope this helps.

Importing Excel saved as XML

Hi guys!

I'm having troubles trying to import an excel file saved as XML.

Tried XML Source, Excel Source, OLEDB Source with no success.

Could someone point me to the right direction?

Thanks in advance.

I cannot see how you would do it without a custom script component as a source which opens the file with Excel automation (Not ideal).

You could (maybe) create your own XSD to allow SSIS to understand the document and create proper column / rows for you but you might end up doing a lot for nothing.

Can you not get it as a normal XLS file?

Importing Excel Into MS SQL Table

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 BoonDo the values in the cells in the Excel-sheet have a ' in front of them?

This character tells Excel that it should leave the value in the cell alone, and when they are imported they are considered to be text.|||Hello oneleg_theone.

Thanks for your email reply. There isn't a ' in the cell. The problem was there were many other unwanted text at some point in the file. After removing them, I saved the file as a .csv file everything works. I was able to import into my SQL table using DTS.

Best regards
Teck Boon|||I don't know if this applies to this situation or not, but wanted to throw it out there as an FYI. Using DTS to import Excel, DTS will look at the first 8 rows (I think it's 8 rows anyways) of Excel data to look for certain characteristics, and base how it will do the import on that. I know that number can be changed, possibly even ignored. However, it's been several years since Ive had to deal with this, so my apologies for not being able to recall how to go about it.

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich