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 string into date

i got a string field from excel and want to import to SQL server
the date field is "28-Nov-05", How can i convert it into date field '
thanks a lotHi,
what about : SELECT CONVERT(Datetime,'24-Nov-2005')
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Good old Books Online:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
Take a good look at the style table for datetime/smalldatetime types. If
your exact style is not listed, try one that is similar.
ML
http://milambda.blogspot.com/

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva