Importing from dBase

I'm trying to import a dBase database to SQL Server, but it contains Swedish characters (,,), and these are replaced with other characters.

I've tried importing from dBase 5, III, and IV, without luck. I don't know how to solve this problem. Is there any way to replace characters using T-SQL, or do I need to change some settings?

I hope someone can help me with this...Have you tried using Swedish collation on sql server?
It will also depend on how you are doing the import. It may be losing the data on the extract from dbase or on the import into sql server.|||Thanks for your reply!

I tried changing the collation using this:

alter database TEST COLLATE Finnish_Swedish_CS_AS

It didn't work. I got an error message saying:

"Incorrect syntax near 'Finnish_Swedish_CS_AS'."

I've used the syntax described in Books Online.

*confused*|||I just learned that collation only can be changed in Sql Server 2k, so I'll solve it in another way. Thanks anyway.

Importing Excel 2007 and/ or DBF files into SQl server 2005

Greetings,

I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).

I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.

I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.

Any hints on what I should do ? (maybe I'm doing something awfully wrong)

Thank you for taking the time to answer my question,
Val

If you have SP2 installed, you should be able to load data from Excel 2007 format using the I/E wizard. Do not use the Excel connection, but use the new OLE DB provider for Office 2007. You will need to set extended properties to "Excel 12.0".

Let me know if you need more assistance.

Thanks.

|||

Try by looking at this : http://msdn2.microsoft.com/en-us/library/aa337084.aspx and also you'll need to configure your connection manually to connect. Set up a Jet OLEDB Connection - point to your folder containing the DBase files. Click the "All" button and change the "Extended Properties" to "DBASE IV".

Importing DBase tables into MSDE using ADO

What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
in VB6
I am using ...
Set cn1 = New ADODB.Connection
cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
my SQL server name and Catalog tramcars exists)
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
I get error message 'Invalid Object Name dbase
IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
---
Using...
cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
works fine and imports DB table to Access 2000
Regards
steve
Try:
select * from OPENROWSET('MSDASQL',
'Driver={Microsoft dBase Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',
'select * from base')
or better yet, create a linked server to dbase:
EXEC sp_addlinkedserver
'DBF',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\tramcars\Bakery\',
NULL,
'dBase IV'
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'DBF',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword = NULL
select * from openquery(DBF,'select * from base')x
-oj
http://www.rac4sql.net
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
> What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
> in VB6
> I am using ...
> Set cn1 = New ADODB.Connection
> cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
> my SQL server name and Catalog tramcars exists)
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> I get error message 'Invalid Object Name dbase
> IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
> Using...
> cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> works fine and imports DB table to Access 2000
> ----
> Regards
> steve
>
|||oj
Thanks
Worked a treat
Steve
"oj" <nospam_ojngo@.home.com> wrote in message
news:uTBcBx$NEHA.3016@.tk2msftngp13.phx.gbl...
> Try:
> select * from OPENROWSET('MSDASQL',
> 'Driver={Microsoft dBase
Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',[vbcol=seagreen]
> 'select * from base')
> or better yet, create a linked server to dbase:
> EXEC sp_addlinkedserver
> 'DBF',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'c:\tramcars\Bakery\',
> NULL,
> 'dBase IV'
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'DBF',
> @.useself = false,
> @.locallogin = NULL,
> @.rmtuser = NULL,
> @.rmtpassword = NULL
> select * from openquery(DBF,'select * from base')x
> --
> -oj
> http://www.rac4sql.net
>
> "steve" <sfrancis@.bigpond.net.au> wrote in message
> news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
ADO[vbcol=seagreen]
Security=SSPI;Persist[vbcol=seagreen]
PM is
>

Importing dBase files with the SSIS Import/Export Wizard

I saw this post by dterrie in the Wishlist thread and I just wanted to second it:

"How about bringing back a simple dBase import. The SSIS guys are clearly FAR out of touch with reality if they think people who handle data no longer need to work with dbf files. I've seen alot of dumb stuff in my day, bit this is just sheer brilliance. I just love the advice of first importing into Access and then importing the Access table. Gee, why didn't I think of such a convenient solution. I could have had a V-8."

I've been struggling with this the last couple days and finally decided to import the dBase III file into Access and then import that into SQL Server 2005. Imagine my surprise when I discovered this was the current recommended method.

That's just ridiculous. Can someone tell me why they would reduce some of the functionality of SQL Server from 2000 to 2005? This was a very easy process in SQL Server 2000...

Philip,

Could you record your request here:

http://lab.msdn.microsoft.com/productfeedback/default.aspx

That way a request will be passed directly to our bug system. It will increase a chance to address it sooner, and you will be informed about the progress.

Thanks.

|||

Thanks Bob! That's a great idea.

I know you guys have been catching some flack over the anemic ODBC support.

Here's hoping there is a service pack for it soon!