Import of SPSS-files

Hello

i'm trying to find out if SSIS is a right toolbox for importing of many SPSS-files into the large market research data warehouse.

A simplified version of SPSS-datasheet looks like this:

- many columns (up to 3000). some of them are interview attributes (InterviewID, Date, etc.) and variables, representing survey questions

- relative few rows with "cases" or "interviews"

- in cells there are answers to the questions (in columns) by respondent (in rows)

As first step i would like to unpivot the dataset in the following way:

- Pass-Through: all interview attributes, that shouldn't be unpivoted

- Input Columns: rest 2990 columns

- Destination Column: Question

- Pivot Key Value Column Name: Answer

It works allright, except that i has to manually define destination column for all 2990 input columns, which takes a lot of time (multiplied by the number of SPSS-files i want to import). Is there a way to automate this (default value for destination column and/or scripting?)

Many thanks for your help!

If you hook your data flow up to an OLE DB destination, there is an option to create the table to match the incoming meta data. This works well and I use it all of the time. Just click the "New..." button next to the table name drop down box.|||

Thanks for your tip. But I think i cannot upload the data without unpivoting it first. I run into 1024 columns pro table restriction of SQL Server.

Again, is there a way to set up "destination column" for all input columns of unpivot transformation automatically?

Many thanks.

|||

Denis Zorenko wrote:

Thanks for your tip. But I think i cannot upload the data without unpivoting it first. I run into 1024 columns pro table restriction of SQL Server.

Again, is there a way to set up "destination column" for all input columns of unpivot transformation automatically?

Many thanks.

Oh, sorry, I misunderstood. No, there is not. Perhaps you could write your own application to build an SSIS package programmatically, but using the out-of-the-box functionality will not allow you to do what you wish.|||

Many thanks Phil!

Does that mean that the script component won't do the job?

|||Sure. The script component could do it, but it'll be a long, drawn out challenge, I'd presume. Well, the problem is that the script component would need to know its output columns upfront, so you'd still have work ahead of you.|||

Well, the output columns are known. They are InterviewID, Question, Answer.

"InterviewID" is a column of the datasheet (pass-through). Its name could be passed as variable.

"Question" contains all other column names (pivot key value column name)

"Answer" contains the values of the datasheet cells (destination column)

What would be the best way to start out?

Many thanks

|||

Check this post and see if it works for you:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

Seems like it would be a good fit for your scenario.

|||Thanks. That will help me to start out.

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a

resource for SMO and importing legacy DTS packages from SQL Server

2000.

We are getting ready to upgrade our

SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on

continuing to use in 2005. I found a script I used to export all of the old DTS

packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ '

/E '

+ '

/N '

+ '"' + name + '"'

+ '

/F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ '

/!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to

import them into 2005.

I have been reading that I should be

using SMO to do my database scripting in 2005. As I have been going through the

libraries, almost everything seems to be geared for importing SSIS packages, and

not legacy DTS packages.

Does anyone know of someone or some

resource that might be able to help me out.

Thank

you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a resource for SMO and importing legacy DTS packages from SQL Server 2000.

We are getting ready to upgrade our SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on continuing to use in 2005. I found a script I used to export all of the old DTS packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ ' /E '

+ ' /N '

+ '"' + name + '"'

+ ' /F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ ' /!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to import them into 2005.

I have been reading that I should be using SMO to do my database scripting in 2005. As I have been going through the libraries, almost everything seems to be geared for importing SSIS packages, and not legacy DTS packages.

Does anyone know of someone or some resource that might be able to help me out.

Thank you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

sql

import issue

I am importing an excel file into the DB and one of the column in the excel sheet has the data shown below.

1111
2222
nnnnn
llll
3kkk
4fff
yyyy
xxxx

WHen i import this data i could not get the data nnnnn,llll,yyyy,xxxx into my db.

And after few attemts i found that if i have the first element in the list start with an alphabet its importing all the information start with alphabets(nnnnn,llll,yyyy,xxxx).
If the first element in the list start with a numeric value its only importing the following values(1111,2222,3kkk,4fff).
But how can i import all the records into my DB.

Thanks.What are you using to perform the import? DTS?|||yes i am using DTS.|||DAMN...another reason to not use DTS...

I Just went through that with someone else...it loads them as null, right?

I'd say bcp

What the layout of the file?|||The simplest answer is to export to a text file, then import that text file using DTS. See KB#236605 (http://support.microsoft.com/default.aspx?scid=kb;en-us;236605) for more explanation. The problem lies in how DTS attempts to process mixed rows (some text, some numeric cells), where if you convert everyting to text it becomes a non-issue!

-PatP

Import in table identity column

I am importing data in the table which has primary key as identity.
Data file doesn't has the data for the column.
I created view which has the column except the identity column trying
to import but getting error
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'MyTable; column
does not allow nulls. INSERT fails.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.
Please help.
Thanks
--
FarhanUnless I am missing something, you should be able to import into the table
normally. Don't try to put anything in the Identity column. It will assign
the values automatically. Forget about creating a view to import into.
HTH
Paul
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>|||This works fine.
use northwind
go
create table t (colA int not null identity(1, 1) unique, colB varchar(25))
insert into t (colB) values('a')
insert into t (colB) values('b')
insert into t (colB) values('c')
execute master..xp_cmdshell N'bcp "select colB from northwind.dbo.t"
queryout d:\temp\test.tbl -Smyserver -T -c'
select * from t
delete t
go
create view myview
as
select colB from t
go
bulk insert northwind.dbo.myview
from 'd:\temp\test.tbl'
with
(
fieldterminator = '\t',
rowterminator = '\n'
)
go
select * from t
go
drop view myview
go
drop table t
go
execute master..xp_cmdshell N'del d:\temp\test.tbl'
go
AMB
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>sql

Import from XML File to SQL Table

Hi ,

I am importing data from an xml file that validates itself against a schema. I need to import some of the values to the corresponding columns in the table. Other elements will be inserted to the a column of type XML.

The size of the file is about 500+MB. I have already tried SSIS. It does not validate my schema, in spite of the fact that the schema is valid. I tried to copy using SQLXML Bulk Insert, but this does not suit to my particular situation.

I am using SQL Server 2005.

What are my options? Any examples / code samples will do.

Thank you,

Zaboo

What is it about SQLXML that doesn't suit your needs?

How about some sample data, ddl, etc.?

Import from excel - Could not find installable ISAM

I'm trying to quickly create a way of importing data from an excel sheet on my C drive to a table on a sql 2005 db hosted by a provider.

I set this up according to the following:

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

The article is in C# but I write in VB. I think I got it except I get this error:

Could not find installable ISAM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Could not find installable ISAM.

Source Error:

Line 23: Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)Line 24:Line 25: connection.Open()Line 26: Line 27: ' Create DbDataReader to Data Worksheet

Microsoft is not much help. A couple of folks say to contact my host. Before I do I just want to be sure there code I have is correct.

Could someone take a quick look at this.

Thanks,

Here is the code behind the button.

ProtectedSub BTNImport_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTNImport.Click

' Connection String to Excel Workbook

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"""

' Connection to Excel Workbook

Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Create DbDataReader to Data Worksheet

Using drAs Data.Common.DbDataReader = command.ExecuteReader()

' SQL Server Connection String

Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString

'Origina from David Hyden's site - Dim sqlConnectionString As String = "Data Source=.;Initial Catalog=Test;Integrated Security=True"

' Bulk Copy to SQL Server

Using bulkCopyAs Data.SqlClient.SqlBulkCopy =New Data.SqlClient.SqlBulkCopy(connectionString)

bulkCopy.DestinationTableName ="ExcelData"

bulkCopy.WriteToServer(dr)

EndUsing

EndUsing

EndUsing

EndSub

Are you running this code on your local machine?

|||

Yes

|||

Actually, I'm using Visual Studio 20005 and am using Sql server express edition to test it.

|||

Maybe this will help

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

|||

I read this before. This relates to Access.

I did push the page up to production in the even that it was my local machine. But, when I hit it online I still get the error.

|||

Got it. I think it was the comination of two problems.

First - I may have needed the following namespace:

Imports System.Data.OleDb

Previously I only had System.Data

Second- I revised the connection string as follows:

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"

Works like a charm now.

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

import from binary file

I am importing data from binary data files into SQL Server. This is the code I am using:

Do While Not EOF(1)
Get #1, , NonStdCurrRecord
adoRS.AddNew
adoRS!Field1 = CDate(NonStdCurrRecord.Field1)
adoRS!Field2 = CSng(NonStdCurrRecord.Field2)
adoRS!Field3 = CSng(NonStdCurrRecord.Field3)
adoRS!Field4 = CSng(NonStdCurrRecord.Field4)
adoRS!Field5 = CSng(NonStdCurrRecord.Field5)
adoRS!Field6 = CSng(NonStdCurrRecord.Field6)
adoRS!Field7 = CSng(NonStdCurrRecord.Field7)
adoRS.Update
Loop

Unfortunately, it takes about 8 mins to import a file with 180k records. Is there a faster way to do this?

maybe bcp?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a4-0ce39c30ec38.htm
The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

Hope, it helps.

|||Thank you for the tip but I don't like requiring users to fiddle with command line utilities. This really has to be done programatically.|||

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/00d0311f-8b71-4ad6-824d-0e89119347a3.htm

maybe this link would be helpful?

Thanks

Import Excel data to SQL Server

Hi,

I have a problem of importing excel data into the SQL database. When I import the database (finish all importing steps), it tell me "Fail to copy 1 table" and I go to view the error message say that "Error at Source for Row number 19. Errors encountered so fat in the task: 1. Data for source column 2 ('Notes') is too large for the specified buffer size."

I try to remove that line record but still have many lines have the same problem. So, can I change the importing buffer size in SQL Server to make me import data becomes successful??

Thanks a lot.you're probably loading it to a pre-made table right? I'm assuming you have a varchar field that isn't large enough. Try maxing it out to 8000 to make sure. It's kind of a pain to load in batch. It always makes me nervous|||Thanks for reply. I try it but I got the same error message. Is it need to set more bigger buffer size for SQL server importing the data?|||Oh... I got it.
Because the SQL will specify the buffer size on that column field of the first eight rows. So, I moved the field which have many character to the second row. Then it works.
Thanks for help :)

Import Errors from MS Access

I encountered errors while importing tables from an Access. The only tbales
that errored had date/time fields and it seems SQL didn't like them. The tab
les work just fine in Access so I'm surprised SQL didn't like them. Anyone s
ee this before?Alan,
Most likely cause: MS Access supports dates as early as Jan 1, 100, while
SQL Server only supports dates back to Jan 1, 1753. You probably have a date
prior to Jan 1, 1753. Before you dismiss this idea, keep in mind that a
simple data entry error can morph Jul 15, 1999 to Jul 15, 199 :-)
Use an Access query to locate dates earlier than Jan 1, 1753.
Chief Tenaya
"Alan Fisher" <anonymous@.discussions.microsoft.com> wrote in message
news:838D8D52-BB99-416A-A4D2-4781DCE7040F@.microsoft.com...
> I encountered errors while importing tables from an Access. The only
tbales that errored had date/time fields and it seems SQL didn't like them.
The tables work just fine in Access so I'm surprised SQL didn't like them.
Anyone see this before?|||On Tue, 23 Mar 2004 16:01:06 -0800, "Alan Fisher"
<anonymous@.discussions.microsoft.com> wrote:
Different versions of Access have different levels of support for
upsizing. Try with Access XP or 2003.
When push comes to shov, it's not so hard to build those few tables
yourself, and write append queries to copy over the data.
-Tom.

>I encountered errors while importing tables from an Access. The only tbales that er
rored had date/time fields and it seems SQL didn't like them. The tables work just f
ine in Access so I'm surprised SQL didn't like them. Anyone see this before?

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
Mark
Hi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/produc...84085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
MarkHi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/produ...584085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
MarkHi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/product4.nsf/wdocs/3243f3d81944584085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly:)
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks much
CORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegr oups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>
|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:[vbcol=seagreen]
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the dbf
> and attempt the import, but it is only importing the first 20 rows of 16,000
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegr oups.com...
|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
[vbcol=seagreen]
|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
>
>
|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegr oups.com...
|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan
|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/d...displaylang=en .
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegrou ps.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks muchCORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the dbf
> and attempt the import, but it is only importing the first 20 rows of 16,000
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> > (dbase) (dbf) files to SQL Server?
> >
> > I have inherted this task from a former employee. I have been using an
> > Access database with an ODBC link to SQL Server for the import task -
> > and it worked fine until last week. For some reason, it just quit
> > importing one of the dbase files.
> >
> > So, I tried use "db workbench" to convert the dbase file to a text file
> > first, then tried to import it into Access. It apparently "worked", but
> > the data got totally corrupted in the process.
> >
> > Now, I am back at square one - and I need a tool I can use to either
> > 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> > tool for converting the dbase file to a text file, csv, or xls file for
> > importing into SQL Server.
> >
> > Any ideas/suggestions greatly appreciated!
> >
> > Thanks much
> >|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
>> > I am wondering if anyone knows of a reliable tool for importing Foxpro
>> > (dbase) (dbf) files to SQL Server? ...|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>> Update - I just discovered the *easiest* way to do this! You can open
>> DBF files with Microsoft Excel - then just save as an xls - then import
>> - viola - works!
>> > I am wondering if anyone knows of a reliable tool for importing Foxpro
>> > (dbase) (dbf) files to SQL Server? ...
>
>|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> > Update - I just discovered the *easiest* way to do this! You can open
> > DBF files with Microsoft Excel - then just save as an xls - then import
> > - viola - works!
> >> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> >> > (dbase) (dbf) files to SQL Server? ...|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> > Update - I just discovered the *easiest* way to do this! You can open
> > DBF files with Microsoft Excel - then just save as an xls - then import
> > - viola - works!
> >> > I am wondering if anyone knows of a reliable tool for importing Foxpro
> >> > (dbase) (dbf) files to SQL Server? ...|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en .
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegroups.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>> Hi!
>> Yes, older format DBFs can be opened with Excel but you will lose the
>> content of any Memo fields. As an alternative you can download and
>> install
>> the FoxPro and Visual FoxPro OLE DB data provider from
>> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
>> Import Wizard or set up a linked server.
>> --
>> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
>> cindy@.cindywinegarden.com
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>> > Update - I just discovered the *easiest* way to do this! You can open
>> > DBF files with Microsoft Excel - then just save as an xls - then import
>> > - viola - works!
>> >> > I am wondering if anyone knows of a reliable tool for importing
>> >> > Foxpro
>> >> > (dbase) (dbf) files to SQL Server? ...
>|||*** Sent via Developersdex http://www.developersdex.com ***

IMPORT DBASE IV (DBF) FILE TO SQL SERVER 2005

Hi,
I am wondering if anyone knows of a reliable tool for importing Foxpro
(dbase) (dbf) files to SQL Server?
I have inherted this task from a former employee. I have been using an
Access database with an ODBC link to SQL Server for the import task -
and it worked fine until last week. For some reason, it just quit
importing one of the dbase files.
So, I tried use "db workbench" to convert the dbase file to a text file
first, then tried to import it into Access. It apparently "worked", but
the data got totally corrupted in the process.
Now, I am back at square one - and I need a tool I can use to either
1.) import the dbase file directly to SQL Server, or 2.) a reliable
tool for converting the dbase file to a text file, csv, or xls file for
importing into SQL Server.
Any ideas/suggestions greatly appreciated!
Thanks muchCORRECTION: I used "DBF Viewer", not "db workbench" to convert to text
file
tootsu...@.gmail.com wrote:
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much|||I have the same issue, and found that the driver for dbf files is not
included in the sql2005 install. I had to go to msdn.microsoft.com to get
the foxpro driver files and installed it. I can now at least locate the dbf
and attempt the import, but it is only importing the first 20 rows of 16,000
set. Interested to see what other answers you get becasue I could get only
one person even attempting to help me, and she got me as far as this.
<tootsuite@.gmail.com> wrote in message
news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...
> Hi,
> I am wondering if anyone knows of a reliable tool for importing Foxpro
> (dbase) (dbf) files to SQL Server?
> I have inherted this task from a former employee. I have been using an
> Access database with an ODBC link to SQL Server for the import task -
> and it worked fine until last week. For some reason, it just quit
> importing one of the dbase files.
> So, I tried use "db workbench" to convert the dbase file to a text file
> first, then tried to import it into Access. It apparently "worked", but
> the data got totally corrupted in the process.
> Now, I am back at square one - and I need a tool I can use to either
> 1.) import the dbase file directly to SQL Server, or 2.) a reliable
> tool for converting the dbase file to a text file, csv, or xls file for
> importing into SQL Server.
> Any ideas/suggestions greatly appreciated!
> Thanks much
>|||Update - I just discovered the *easiest* way to do this! You can open
DBF files with Microsoft Excel - then just save as an xls - then import
- viola - works!
Don't know why I didn't discover this earlier. No need for any special
tools, or dts.
JC HARRIS wrote:[vbcol=seagreen]
> I have the same issue, and found that the driver for dbf files is not
> included in the sql2005 install. I had to go to msdn.microsoft.com to get
> the foxpro driver files and installed it. I can now at least locate the db
f
> and attempt the import, but it is only importing the first 20 rows of 16,0
00
> set. Interested to see what other answers you get becasue I could get only
> one person even attempting to help me, and she got me as far as this.
>
> <tootsuite@.gmail.com> wrote in message
> news:1158866292.228863.307680@.h48g2000cwc.googlegroups.com...|||Hi!
Yes, older format DBFs can be opened with Excel but you will lose the
content of any Memo fields. As an alternative you can download and install
the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
Import Wizard or set up a linked server.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
> Update - I just discovered the *easiest* way to do this! You can open
> DBF files with Microsoft Excel - then just save as an xls - then import
> - viola - works!
[vbcol=seagreen]|||What Cindy says is correct. I could not use the excel method because of the
dbf size (overflows the excel program). I followed Cindy's instrcution on
another newsgroup and it worked great.
"Cindy Winegarden" <cindy@.cindywinegarden.com> wrote in message
news:OwD1qPl3GHA.4924@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>
>
>|||Hi Cindy,
Thanks for the information. I don't know if I have any memo fields -
how can I tell?
Would the column just show up as blank in Excel?
Thanks!
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>|||Hi Cindy,
Yes, I see what you mean - the memo fields are blank in Excel.
I went to the link you listed below, but I don't know which file to
download? They all seem like service packs. Is this the right page?
Help.
THANKS
Cindy Winegarden wrote:[vbcol=seagreen]
> Hi!
> Yes, older format DBFs can be opened with Excel but you will lose the
> content of any Memo fields. As an alternative you can download and install
> the FoxPro and Visual FoxPro OLE DB data provider from
> msdn.microsoft.com/vfoxpro/downloads/updates and then use the SQL Server
> Import Wizard or set up a linked server.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@.cindywinegarden.com
>
> <tootsuite@.gmail.com> wrote in message
> news:1158874680.304855.200110@.h48g2000cwc.googlegroups.com...
>|||"JC HARRIS" <harris1113@.fake.com> wrote in message
news:%230YkfWm3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> What Cindy says is correct. I could not use the excel method because of
the
> dbf size (overflows the excel program). I followed Cindy's instrcution on
> another newsgroup and it worked great.
If you have it available, you might the Office 2007 Beta version of Excel.
It increases the number of records immensely.
Jonathan|||Hi!
http://msdn.microsoft.com/vfoxpro/downloads/updates/ , second item. It
points to
http://www.microsoft.com/downloads/...&displaylang=en .
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<tootsuite@.gmail.com> wrote in message
news:1158946224.807292.64180@.m7g2000cwm.googlegroups.com...
> Hi Cindy,
> Yes, I see what you mean - the memo fields are blank in Excel.
> I went to the link you listed below, but I don't know which file to
> download? They all seem like service packs. Is this the right page?
> Help.
> THANKS
>
> Cindy Winegarden wrote:
>

Import datetime

I am having a problem importing data from a csv file. I am trying to import into a table with numeric and datetime columns, but it errors out everytime. Is there a way to accomplish time? I am not very familiar with Microsoft SQL, so hopefully this can be accomplished.

I am not sure how to approach this. Any help is greatly appreciated. Thanks in advance.What are you using? DTS, BCP, BULK INSERT?

What errors are you getting?|||Trying just normal import. Right click database -> All Tasks -> Import Data.
Going from csv file into already established table. I have attached the error box.|||Import it to a new table...let dts create the table for you, then check out the datatypes...sounds like your mappings are off...

I usually always create a staging table for imports...that way I can audit/sanitize the input files.

If tere's something wrong you can stop it there, BEFORE you hose the good data|||You get bad data ?!?!

Oh the shame! Your users don't always provide you with nice, clean, carefully QA'ed data in their files? Especially CSV files ?!?

Ok, so I'll climb down off of my soap box now...

As Brett observed, it is a lot safer to just bring the data wholesale into a staging table, so that you can inspect it carefully, and if it is found wanting you can dump it without a second thought. I generally presume that data is absolutely worthless until after I prove otherwise. More often than not I'm wrong, but not a whole lot more often!

-PatP|||Actually, the data in question is the original data from the table. Someone decided to screw with constraints to allow some new data to be imported and ended up with duplication. Due to some other constraint, the duplicates could not be deleted (I didn't see the error). So the data should be OK.
The import utility won't take a field from the csv file (that was exported from that table) and import it back into the numeric or datetime field in the table.|||Whenever I do a raw import from a CSV file, I get the columns processed in ascending order. Based on the error message that you posted, you appear to be using a transformation that processes the columns in descending order.

Are you using a custom transformation? If not, which Enterprise Mangler / SQL Service pack are you using?

You also processed 91 rows Ok, and 92 went "toes skyward" on you. I'd investigate the CSV file, looking hard at row 92.

-PatP|||Thank you all for your help. Too much frustration made me rush through things. Come to find out, after importing the csv file into a new table (all columns vchar), the users inputed commas in some of the descriptions (one of the columns). This of course forced additional columns to be created and unmatched datatypes within certain columns.
Being the only DBA here for MSSQL, Oracle, Sybase, and MySQL, I have no one to work through these things with locally.
Thanks, to the forums and your responses.|||You are NOT alone

"mind what you learn here...save you it will..."

Oh, and "trust no one Dr. Jones..."