Import Excel Spreadsheet to Database Tables

Question

Import Excel Spreadsheet to Database Tables

Viewing 15 posts - 16 through 30 (of 69 total)

You must be logged in to reply to this topic. Login to reply

gorthog Ten Centuries Points: 1084 More actions · Answer 1

This may be a little off-topic, but is there a way to convert dBase files to xml for import into SQL Server 2008 (64-bit) tables?

jim@tailwindcyclists.com SSC Journeyman Points: 90 More actions · Answer 2

Here is the Openrowset/Opendatasource Test Script you can use after the above installation.

/*

Ref: http://www.connectionstrings.com/excel-2007

If you want to read the column headers into the result set (using HDR=NO even though

there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider

the scenario that one Excel file might work fine cause that file's data causes the

driver to guess one data type while another file, containing other data, causes the

driver to guess another data type. This can cause your app to crash.

*/

SELECT *

FROM OPENROWSET(

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 XML;HDR=YES;IMEX=1Database=c:\install\AgeHeartRate.xls;',

'SELECT * FROM [Sheet1$]');

Select * from opendatasource(

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;IMEX=1;Database=c:\install\AgeHeartRate.xls;')...[Sheet1$]

peterhe SSChampion Points: 11469 More actions · Answer 3

Denis,

First, in Your scenario, the file needs to be in the C:\ drive of the SQL Server box;

Second, check the file security:

If you log on SQL Server by a SQL Server login, the SQL Server service (process) account is used to access the file.

If you log on SQL Server by Windows Authentication, you can read only those files that can be accessed by your windows account, regardless of the security settings of the SQL Server service account.

peterhe SSChampion Points: 11469 More actions · Answer 4

sckemp1,

Did you save your spreadsheet as xml in Excel?

Atradius SSC Eights! Points: 829 More actions · Answer 5

Gordon,

what program creates your dBase files?

Newer version of dBase, and their clones such as Visual Foxpro, have support for converting tables to XML files.

Otherwise it is quite elementary, although not so fast, to skip through the table and adding XML tags around the values.

peterhe SSChampion Points: 11469 More actions · Answer 6

Gordon,

To import dbfile to SQL Server, a better way is to use SSIS. 64 bit sql server has both 32 bit and 64 bit SSIS command line. You can use the 32 bit command line (DTExec), which will use the 32bit jet provider, to import data to 64 bit SQL Server

sckemp1 Grasshopper Points: 16 More actions · Answer 7

Oh, I'm a moron. I thought that the stored proc did the conversion directly from xls. My bad.

Thanks for pointing that out. ( sorry!! )

Denis W. Repke Mr or Mrs. 500 Points: 536 More actions · Answer 8

Denis W. Repke

Mr or Mrs. 500

Points: 536

February 17, 2010 at 10:36 am

#1120263

Peter,

That makes sense. Thank you.

Denis

Denis W. Repke

changc-708876 Newbie Points: 5 More actions · Answer 9

It's not working for me when I use UNC path. give me this error:

Msg 50000, Level 16, State 1, Procedure uspImportExcelSheet, Line 497

Cannot bulk load because the file "\\Projects\FTP-Download\BatchData\CWR\Warrants_2010-02-16.xml" could not be opened. Operating system error code 5(Access is denied.).

I ran this script against a development sql server from my local management studio. the file is locate at network drive.

can anyone help?

gorthog Ten Centuries Points: 1084 More actions · Answer 10

The program that creates the dBase file is a canned student information system that utilizes dBase for data storage. The system does not provide any tools for working with the dBase files. In an effort to migrate clients to a SQL Server based solution, I have used ad hoc openrowset queries to load the dBase files into SQL Server tables. This solution works well, except where a 64-bit version of SQL Sever is installed.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

greg.staley Valued Member Points: 67 More actions · Answer 12

This is a FANTASTIC article that makes a tedious process EASY.

The only reason I can think of why someone would not rate this article as a 5 star is because somehow they just dont get it...or else they are a genius!! 🙂

JGay 2041 SSC Enthusiast Points: 142 More actions · Answer 13

Peter,

I need help dissecting your ImportExcel.sql. Is this the appropriate forum or should I e-mail you directly?

peterhe SSChampion Points: 11469 More actions · Answer 14

Greg staley:

Thanks for your kind words.

changc-708876:

As the error message said, it is the security setting issue. The account you used (the Windows account if you used trust authentication or the SQL Server service account if you used SQL login) to login to SQL Server does not have the permission to access the shared file in the remote machine.

peterhe SSChampion Points: 11469 More actions · Answer 15

peterhe

SSChampion

Points: 11469

February 17, 2010 at 1:55 pm

#1120448

JGay 2041,

You can post it here.