Solved

Transfer of data from SQL to excel and vice versa

Posted on 2004-08-28
7
7,992 Views
Last Modified: 2010-05-18
Hi Guys,

Hope you all are doing fine I have got some difficulty in doing something which I am doing the first time. I have a program writeen in Vb.net in which backend is SQL. The scenario is like that from this program I have to take some data and update the data on another computer and both of these computers cannot be connected to one another. So I thought it is best to export the data into excel and again import the data from excel to SQL. But I am not able to do that I got some code from the net but it is not working.

What I want to do is that I want to create a small application which will use SQL only to export the data ? I am using below mentioned code but it is giving some error.


CREATE PROCEDURE export_data AS

Insert into #temp select Reqdate from Requests where Reqdate = '2004/08/26'

insert into OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Student.xls; HDR=No', [StudentData])
select ReqDate from #temp
go


This gives an error that either the table StudentData does not exist or you do not have the permission to write on that.

Anybody can help me out.
0
Comment
Question by:faisal_n_k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11920796
Exporting to an Excel spreadsheet requires you to have the excel spreadsheet already existing, plus to have a 'table' with the correct column headers etc in the spreadsheet.
You will need to create the spreadsheet student.xls and create a named area in a sheet called StudentData, and this must have a header called Reqdate in order for this kind of export to work.

However, if all you want to do is to transfer the data, you would be best off using BCP to unload / load the data to a text file - it will be much faster, and the file will be created for you.
 
to unload the data use something like this :
exec master..xp_cmdshell 'bcp "select Reqdate from Requests where Reqdate = ''2004/08/26'' queryout "c:\test.txt" -c -q -U"user" -P"password"'

then to load back in again on the other side -
exec master..xp_cmdshell 'bcp "database.dbo.table" in "c:\test.txt" -c -q -U"user" -P"password"'
0
 

Author Comment

by:faisal_n_k
ID: 11920961
Thanks Bill for the reply but  when I am running the code that you have given in the query analyzer it is giving me output like this

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"]
NULL


Secondly what I want to ask is that do i need to make a file called test.txt earlier then running the code and can you please make me understand the code what is -c, -p, what username and passw ord does it requires

Thanks in advance
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11921084
The message you get is because there is some error in the format of the string you are using - can you paste the exact command? you ran?
just notriced, in the version I gave you there should be 1 more " -
exec master..xp_cmdshell 'bcp "select Reqdate from Requests where Reqdate = ''2004/08/26''" queryout "c:\test.txt" -c -q -U"user" -P"password"'
that is, the whole SQL statement should be enclosed in double quotes.
any part of the SQL that would normally have a single quote (') needs to be replaced by two single quotes ('') NOT a double quote (")
If you are still getting an error, can you paste your exact command you use and I can check it.

the format for bcp to create the file is :
exec master..xp_cmdshell 'bcp "this is where you put your SQL statement"  queryout "this is where you specify the file" -c -q -U"user" -P"password"'

the -c option means write the output as a character text file, using tabs to delimit fields, and \n to seperate rows
the -q otpion is really not necessary, but it lets you quote the name of the database - it is only required if you have any spaces etc in the name of the database etc.
the -U option is to specify a user. It is this user that the extract will happen as. This can be any user who has the rights to execute the SQL you want. If you are concerned about security, you could create a specific user which only has permissions to select from the tables you want, and no other rights. (and of course on the destination the user will need to be able to insert)
the -P option is to specify the password that this user uses to logon to SQLServer.

No, you don't need to create the text file - it will be automatically created - one of the reasons, apart from speed, to use this method rather than the Excel route...



0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:faisal_n_k
ID: 11921187
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Requests'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL


I am getting this error while running this command
0
 

Author Comment

by:faisal_n_k
ID: 11921217
When I give the username and password that I am using to run this program it gives some other error and when I give my username and password it gves the same error

The command that I am using is

exec master..xp_cmdshell 'bcp "select ReqDate from Requests where Reqdate = ''2004/08/26''" queryout "c:\test.txt" -c -q"IT_prog" -U"sa" -P"password"'

bcp:  unknown option I
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"]
NULL
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 100 total points
ID: 11921401
what is IT_prog - is this the name of the database? it should'nt go after the -q, the -q is simply to tell the bcp that you are using quoted names etc. it doesn't take any parameters. The error Invalid object name 'Requests' is prbably because the default database for the user (sa) is not the database that this table is in, so  you need to qualify the table name with the database - (assuming IT_prog is the name of your database, and dbo is the owner of the Requests table, then try this form -

exec master..xp_cmdshell 'bcp "select ReqDate from IT_prog.dbo.Requests where Reqdate = ''2004/08/26''" queryout "c:\test.txt" -c -q -U"sa" -P"password"'
0
 

Expert Comment

by:michael090
ID: 12186601
hi guys,

  just wanna ask how to insert excel file to sql database using asp.net
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question