Export Excel records to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Export Excel records to SQL

Author

Topic

slmgt
Starting Member

37 Posts

Posted - 2010-09-09 : 15:49:25

This is my first time posting on this site and to be frank, it is because I messed up my database.

I ran this query in an attempt to update all records with blank entries (not NULL) with NULL:

"UPDATE    dbo.Mailing_Contacts
SET              Contact_Zip = REPLACE(Contact_Zip, '', NULL)"

But it replaced EVERY records Zip code with NULL. I have a DEV database, but it does not have 100% updated data - I know I should have used DEV to test run a query, but what's done is done. I have requested our officially backed up database be restored into our Deployed DB using Contact_ID to ensure Contact_Zip is properly updated. Until then I am wondering if it would be possible to use my Excel sheet [containing the Zip codes which were backed up prior to the mistake] to update the records myself. I do not believe my skills are sufficient to figure this out. Here are queries I have attempted to run and why they didn't work:

SELECT     Contact_Zip
INTO            dbo.Mailing_Contacts
FROM         OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\testing.xls;Extended Properties=Excel 8.0' )...[Sheet1$] AS derivedtbl_1

Error:
.Net SqlClient Data Provider
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

INSERT INTO dbo.Mailing_Contacts
SELECT     Contact_Zip
FROM         OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\testing.xls;HDR=YES', 'SELECT Contact_Zip FROM [Sheet1]') AS derivedtbl_1

Error:
.Net SqlClient Data Provider
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

I do not have access to the root server housing the database and those that do are also the ones who have access to our backed up database, so I guess that may be Plan B (if restore doesn't work).

Any ways for me to do this without dealing with the other department?

Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 16:39:05

Do you have bcp? Can you create a staging table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-10 : 00:05:24

quote:
Originally posted by tkizer

Do you have bcp? Can you create a staging table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Unfortunately I am not familiar with either of those. Could you elaborate further and/or offer other suggestions?

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 04:53:43

Bcp is a command line executable. Search for bcp.exe on your computer.

By staging table, I mean are you able to run a CREATE TABLE statement?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-10 : 16:38:24

I can create tables as well as have access to bcp.exe.

I have already created a new table called "Mailing_TEST" which has two columns, "Contact_ID" (int) and Contact_ZIP (varchar(50)) which mirrors the Mailing_Contacts Table. I also found bcp.exe in C:\Program Files\Microsoft SQL Server\90\Tools\Binn - what are my next steps to combine the data I have in Excel with SQL?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 16:44:03

Import the data like this (we can work on the merge next):

Save your file as a csv file.

bcp YourDatabase.dbo.YourStagingTable in c:\YourFile.csv -Sserver1\instance1 -T -c -t, -r\r\n

-T means Windows authentication
Substitute it with -Uuser1 -Ppassword1 if you intend to use SQL authentication.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 16:45:32

Then the merge:

UPDATE t
SET Contact_ZIP = csv.Contact_ZIP
FROM YourActualTable t
JOIN YourStagingTable csv
ON t.Contact_ID = csv.Contact_ID

Please ensure you test this first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-10 : 18:04:43

I appreciate the responses, but I get errors when running the code (unknown option 1). I need to make sure my syntax is exact.

We will pretend my Server IP is 101.123.101.123, DNS is sql.testsite.com, my database name is Database_SQL, staging table is Mailing_TEST, and location to excel file is C:\testing.xls. User and password are Admin_SQL and SQL_Password respectively.

The two columns I wish to reference are Contact_ID and Contact_Zip - in the excel sheet they can be named or I will leave them unnamed in columns A and B respectively.

This is what I think it should look like:

bcp Database_SQL.dbo.Mailing_TEST in c:\testing.csv -101.123.101.123\Database_SQL -Admin_SQL -SQL_Password -t, -r\r\n

Based on this interpretation:

bcp DatabaseName?.dbo.Mailing_TEST in c:\testing.csv -Sserver1\instance1(what does this portion mean?) -User1 -Password1 -t, -r\r\n

Have a specific recommendation?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 18:11:09

So your Excel file contains more than those two columns? If so, then you'll need to create the staging table to match the excel file.

servername\instancename is whatever you use to connect to the server. If you have the default instance installed, then it's just servername.

You also need -S -U -P as those are the switch/parameter names.

Probably like this:
bcp Database_SQL.dbo.Mailing_TEST in c:\testing.csv -S101.123.101.123 -UAdmin_SQL -PSQL_Password -t, -r\r\n -c

Make sure you run this in a cmd window (Start..Run..cmd) as bcp is a command line executable.

If you are uncomfortable with any of this, run bcp /? to see its parameters. -t, means use comma as field terminator. -r\r\n means use CRLF for row terminator. -c means the file is in character format (as opposed to native format which is -n).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-10 : 18:43:57

This is the error I get:
An error occurred while processing the command line.

My testing.csv file resembles this:


Contact_ID,Contact_Zip
171,10002
173,10031
174,10012
175,15525

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 23:17:03

I think we may need to skip the header. Add -F2 to your bcp command to indicate to start at row 2.

You may also need to convert your Excel file to csv (save as in Excel).

Just to be sure, could you type in bcp.exe from a command window to ensure it's coming up. And bcp /? as well.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-13 : 15:29:01

I am still getting the same error, with and without the -F2. I removed the headers entirely and tried again without the -F2 and it still returns the error.

Any other way or is there any way to troubleshoot the cause of this error?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 15:46:13

What does bcp /? show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-13 : 16:03:42

C:\Documents and Settings\slmgt>bcp /?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:06:16

Good, that means that it can at least find it.

Did you try saving your excel file as csv and then using that with bcp?

Alternatively, you can use the import/export wizard in SSMS to import you file into a table using a GUI (wizard).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-13 : 16:10:32

Yes the file is saved as a CSV through the Excel Save As... menu (Excel 2007, but file originally .xls then converted to .csv)

How could I use SMSS to import data from Excel using a GUI?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:14:30

Right click on the database, go to Tasks, then Import Data... Then follow the wizard.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-13 : 16:43:53

I would like to share a very helpful tip I learned:[url=http://www.mssqltips.com/tip.asp?tip=1430]How to copy & paste data from Excel sheets to SQL Server... Using Copy & Paste[/url]

That saved me a bunch of time and headaches.

Now I would like to ask how I can merge the two tables (Mailing_test) and Mailing_Contacts together when they have different schemas.

Mailing_Test schema: Contact_ID & Contact_Zip
Mailing_Contact schema: Contact_ID, Contact_Email, Contact_Street_Address, Contact_City, Contact_State, Contact_Notes, Contact_Exclude, Contact_Zip

I now have all the data from my Excel sheet in Mailing_test including Contact_id and Contact_Zip and I need to merge that table's data with the Contact_ID and Contact_Zip fields [only] of the Mailing_contacts table. How can this be done WITHOUT any loss of data?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:46:36

See my update statement above.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slmgt
Starting Member

37 Posts

Posted - 2010-09-14 : 00:02:10

I'll work on that tomorrow, because I need to make sure it works. I have already created another testing table to import my data into, then if that works, I'll import it into the live table. I just need to figure out how to customize that statement to work for my situation, making sure the zip codes line up with the IDs.

Thanks for the help!

slmgt
Starting Member

37 Posts

Posted - 2010-09-14 : 15:36:42

UPDATE Mailing_MergeTest
SET Contact_ZIP = csv.Contact_ZIP
FROM MAILING_TEST
JOIN Mailing_MergeTest csv
ON Mailing_MergeTest.Contact_ID = csv.Contact_ID

Where Mailing_MergeTest is my new staging table [to hold the data].

I made the MergeTest table just so I could test importing the data from TEST into the real Mailing_Contacts table. My ultimate goal is getting the TEST data (Contact_ID and Zip) into the Contacts table (which has the same schema as the MergeTest table).

I am sorry for all the questions - this is a learning experience for me. Thank you.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources