Converting ACCESS and EXCEL data to SQL

Hi,

I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.
How may this be done?
I am able to create a Data Component with the ACCESS mdb file.

Likewise, how may I convert EXCEL data to SQL2005 table?
Thanks.

David

Access should have an upsizing wizard look for it, excel try the link and code below. Hope this helps.

http://www.sqlis.com/

/* 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

|||

Thank you very much for the reply!
Moving along, I tried do an "upsize" from ACCESS to SQL, but I was not able to create a DSN for the SQL server. (Could not specify the "server"; I have tried using "local" and it didn't work.)
I am using SQL 2005 express - not the full version.
Can anyone tell me if this is due to limitation of the express version?
i.e. is it not possible to create a DSN to SQL 2005 Express ?

David

|||

You don't need DSN see if you can upsize it into SQL Server 2000. Then you can backup and restore it. Try the link below download and install the SQL Server 2005 Express Manager it, there must be a way to upsize. BTW Microsoft bought a migration tool company I forgot all about it you can download from the company site and test drive or use the link below to sign up for a Microsoft beta program. Hope this helps.

http://www.microsoft.com/sql/migration/default.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

sqlsql

Convert sql2005 database to sql200

Can somebody tell me, it is possible to convert a database developed in sql2005 back to sql2000?

I am desperatly trying to do so, but...

please help...

Create a blank database in 2000 use 2000 DTS to get your tables and objects from 2005. Hope this helps.

|||

Here is a solution, please follow these steps.

1. Create a blank database in SQL Server 2000.

2. In SQL 2005 Management Console Studio , from Database -> tasks -> Generate Script -> select Database ->Script for Server Version -> Change it from SQL Server 2005 to SQL Server 2000

3. Do the process as per wizard.

4. We can run that Script into SQL Query Analyzer 2000 for that selected database where you want to move it.

You should be able to get whole database table creation and whole relationship which was made into SQL Server 2005.

If you have data, use bcp utilities to export and import.

Please check this thread for detail.

Convert Date Format of 00-XXX-00 to NULL

Let me start by saying that I'm brand new to SQL Server 2005 and SSIS.
I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"

The value of 00-XXX-00 means that there is no purchase date.

I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.

The table Data Type is datetime.

If I use the import wizard using the example above I get this error message:

- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

If I remove the 00-XXX-00 values and import something like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"

The import is successfull and the dates look correct in a querry.

SELECT *
FROM date

date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000

(3 row(s) affected)

Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?

Thank you,

Ryan

Ryan,

The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.

In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.

You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

Hope that helps!

-Jamie

|||Could you please break down what that expression does or point me to a reference for the expression language?
|||

Don't mean to butt in here, but what Jamie has listed here:

Jamie Thomson wrote:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

-Jamie

uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.

So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.

The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.

Hope this helps.

Mark

http://spaces.msn.com/mgarnerbi

|||

There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.

-Jamie