• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

Create a CSV file from a SQL Server database

I am a bookseller and maintain my inventory in SQL Server. I use a third party service to price my books which requires CSV format. I currently use Excel to do this with the Data -> Import External Data function. For reasons too long to go into here I have to also do some manipulation of the data once I import it into the Excel/CSV file. I do this with a macro which I will paste here. The problem is the database is about to exceed the maximum number of rows allowed in Excel - its 58000 rows now and Excel max around 65000. Can somebody help me with a general query that would a) do the work my macro is doing now and b) create the CSV without having to go through Excel. Here are some examples of what the macro does:

Columns("B:B").Select
    Selection.Replace What:="BrandNew", Replacement:="New", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Like New", Replacement:="Used - Like New", LookAt _
        :=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="LikeNew", Replacement:="Used - Like New", LookAt _
        :=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Very Good", Replacement:="Used - Very Good", LookAt _
        :=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="VeryGood", Replacement:="Used - Very Good", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Selection.Replace What:="Good", Replacement:="Used - Good", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Acceptable", Replacement:="Used - Acceptable", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
 
0
bpfsr
Asked:
bpfsr
  • 18
  • 17
1 Solution
 
HoggZillaCommented:
BCP or SSIS can create the .csv file.
We can build a view that does everything your macro is doing.
0
 
HoggZillaCommented:
From the SQL Server database, you have a column that contains a varchar value for condition.

SELECT CASE ConditionColumn
  WHEN  'BrandNew' THEN 'New'
  WHEN  'Like New' THEN 'Used - Like New'
 ...
  ELSE ConditionColumn
END Condition

Open in new window

0
 
bpfsrAuthor Commented:
no, actually I don't. here is the query I'm using. I'll also attach a sample ISBN file (please note the attached is an Excel file because I cannot upload a CSV file to EE, but obviously it can be converted and saved as CSV), but when you use this query it creates a table that has one column. The select query at the end below returns 3 columns, but they are not stored in the table. What I want is to write the results a new CSV file.

DECLARE  @ISBN  TABLE(
      [ISBN] [nvarchar](50) NOT NULL,
      [Condition] [nvarchar](50) NULL,
      [SKU]  AS (ltrim(rtrim([ISBN]))+ltrim(rtrim([CONDITION]))) PERSISTED
)  
DECLARE @TABLECONDITION TABLE (
      [Condition] [nvarchar](50)
)
 
INSERT INTO @TABLECONDITION
SELECT  'New'  
UNION ALL
SELECT 'Used Like New'  
UNION ALL
SELECT 'Used Very Good'  
UNION ALL
SELECT 'Used Good'  
UNION ALL
SELECT  'Used Acceptable'  
 
------------------------------------
INSERT INTO @ISBN
SELECT ISBNIMPORT.ISBN, T.* FROM ISBNIMPORT
CROSS JOIN @TABLECONDITION  T
-----------------------------------
SELECT * FROM @ISBN
ORDER BY 1


ISBN-HDC-Test-20.xls
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
bpfsrAuthor Commented:
Sorry, I forgot the top of the query...

CREATE TABLE ISBNIMPORT
(ISBN VARCHAR(200))
 
BULK INSERT  ISBNIMPORT
FROM 'C:\Documents and SettingsMy Documents\ISBN HDC Test 20.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
0
 
HoggZillaCommented:
I will build this today if you still need it?
0
 
bpfsrAuthor Commented:
I do, thank you.
0
 
HoggZillaCommented:
OK, I was working on this today but I got a little confused. Plese confirm if I right. You can copy all of this text into your response and answer or comment on each of my points.
1. You have an inventory of books.
2. You keep records of your inventory in a SQL Server table which includes the folloiwng columns: ISBN, Condition and SKU.
3. Little fuzzy here .. You send a list of all books, ISBN and Condition, to a third party for pricing. They require a .csv format file. So, from SQL Server Mgmt Studio you select Tasks, Export Data, and then create an export into a .csv file.
4. Currently you have to open the .csv file, after you create it, and use Excel to change the Condition from one text to another.
So I don't know how the Bulk Insert of ISBN numbers back into your database fits into my understanding of your process.
If you just need to change the text in the Condition column before you write to a .csv file we can do that with a view or conditional select stmt.
If you need to import the Condition column and change the values on Import, we can do that also - but I am just not sure I understand what I need to do.
Very sorry for the confusion on my part ... I can definatelly help, just need to be sure of the need. Thanks!!!
0
 
bpfsrAuthor Commented:
Thanks for the help, hope this clears things up.

1. You have an inventory of books.
Correct.

2. You keep records of your inventory in a SQL Server table which includes the folloiwng columns: ISBN, Condition and SKU.
Partially correct, I START with a temporary table - NewBooks - with those three columns. I have all three set up as varchar.

3. Little fuzzy here .. You send a list of all books, ISBN and Condition, to a third party for pricing. They require a .csv format file. So, from SQL Server Mgmt Studio you select Tasks, Export Data, and then create an export into a .csv file.
I do send the books to a third party for pricing and it does have to be in CSV format. However, the way I do that now is I open an Excel file and do Data->Import External Data. The problem there is my inventory is growing beyond the Excel max of 64,000+ records. So this is the first place I want to use SQL Server to create the CSV file without having to go through Excel. I did not know there was a Export Data option available in SQL Server. I have Mngmt Studio Express, Would that make a difference why I don't see it?
4. Currently you have to open the .csv file, after you create it, and use Excel to change the Condition from one text to another.

So I don't know how the Bulk Insert of ISBN numbers back into your database fits into my understanding of your process.
The third party program gives me addition data I can use to enhance the book database. Once I have that data - in the form of a CSV file - I first create the following table:
CREATE TABLE tmp_DBase_Editor
(
      sku nvarchar (max) not null,
      title nvarchar (max) null,
      price nvarchar(max) not null,
      condition nvarchar (max) not null,
      author nvarchar (max) null,
      sellers int null,
      usedsellers int null,
      isbn varchar (13) not null,
      availability nvarchar (max) null,
      comments money null
)
Then import the data with
 
insert tmp_DBase_Editor ( sku, title, price, condition, author, sellers, usedsellers, isbn, availability, comments)
 
SELECT dbo.ustr(1,line),dbo.ustr(2,line),convert(money,replace(dbo.ustr(3,line),',','')),dbo.ustr(4,line),dbo.ustr(5,line),
       dbo.ustr(6,line),dbo.ustr(7,line),dbo.ustr(8,line),dbo.ustr(9,line),convert(money,replace(dbo.ustr(10,line),',',''))
 
FROM OPENROWSET(BULK 'G:\dbase_editor.csv' ,
FORMATFILE = 'C:\Documents and Settings\My Documents\single_line.txt',FIRSTROW = 2, MAXERRORS=10) AS A
--inserts the data from tmp_DBase_Editor --Run alone

I then run a series of queries that combines the data into a view. I use the view to compare to and edit the permanent file. I then delete the temp. files


If you just need to change the text in the Condition column before you write to a .csv file we can do that with a view or conditional select stmt.

If you need to import the Condition column and change the values on Import, we can do that also - but I am just not sure I understand what I need to do.

Very sorry for the confusion on my part ... I can definatelly help, just need to be sure of the need. Thanks

So basically the main task I am trying to accomplish is to figure out how to write to a CSV file direct from SQL Server. I did some research on my own and there seems to be a consensus on using the BCP utility, although I have not figured out how that works. If there is something as simple as an Export Data function in SQL Server Management Studio Express that would be even better. Thanks again for the help and for staying on this.
0
 
HoggZillaCommented:
Thanks for clearing up my fogginess. I get it now.
>>So basically the main task I am trying to accomplish is to figure out how to write to a CSV file direct from SQL Server. I did some research on my own and there seems to be a consensus on using the BCP utility, although I have not figured out how that works. If there is something as simple as an Export Data function in SQL Server Management Studio Express that would be even better. Thanks again for the help and for staying on this.
Unfortunately, the Express Edition does not ship with theExport functionality, nor SSIS. We are going to have to use BCP.
0
 
HoggZillaCommented:
What table in your database has the data you need to export to a .csv file? What are the column names in your database and the required formatting of columns in the .csv file?
I do not use the Express Edition. Can you execute the xp_cmdshell stored procedure?
0
 
bpfsrAuthor Commented:
It would be the NewBooks table. The columns would be ISBN, Condition and SKY and all three are varchar (max). Sorry, not familiar with xp_cmdshell stored procedure.
0
 
bpfsrAuthor Commented:
Is there a way to download the full Management Studio version? I have looked for it on MSDN and can't find it...
0
 
HoggZillaCommented:
You can download a 180 day trial of the Enterprise version of SQL Server.
http://www.microsoft.com/sqlserver/2008/en/us/try-it.aspx
In SSMS, try this script. I want to see if you can run the xp_cmdshell stored procedure. That will make our BCP statement easier.
DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd
 
0
 
HoggZillaCommented:
SELECT
ISBN,
 CASE Condition
  WHEN  'BrandNew' THEN 'New'
  WHEN  'Like New' THEN 'Used - Like New'
  WHEN  'LikeNew'  THEN 'Used - Like New'
  WHEN  'Very Good' THEN 'Used - Very Good'
  WHEN  'VeryGood' THEN 'Used - Very Good'
  WHEN  'Good' THEN 'Used - Good'
  WHEN  'Acceptable' THEN 'Used - Acceptable'
  ELSE Condition
END Condition,
SKY
FROM NewBooks
0
 
bpfsrAuthor Commented:
Got this:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
0
 
bpfsrAuthor Commented:
Okay, I turned it on.
0
 
HoggZillaCommented:
Does the SELECT above return what you expect?
0
 
bpfsrAuthor Commented:
is that supposed to create the CSV file? (that is what I am most concerned with)
0
 
HoggZillaCommented:
Try this. I do not have access to xp_cmdshell in my environment so can't verify.
DECLARE @bcpCommand varchar(2000)
SET @bcpCommand = 'bcp " SELECT ISBN, CASE Condition WHEN  'BrandNew' THEN 'New' WHEN  'Like New' THEN 'Used - Like New' WHEN  'LikeNew'  THEN 'Used - Like New' WHEN  'Very Good' THEN 'Used - Very Good' WHEN  'VeryGood' THEN 'Used - Very Good' WHEN  'Good' THEN 'Used - Good' WHEN  'Acceptable'  THEN 'Used - Acceptable'  ELSE Condition END Condition, SKY FROM NewBooks" queryout c:\temp\output.csv -c -CACP -t, -T'
EXEC master..xp_cmdshell @bcpCommand

Open in new window

0
 
bpfsrAuthor Commented:
HZ - I think this is my fault. I'm not worried about the conversions, I had already sorted that out. All I really need now is the ability to convert the table to the database. I have attached a sample of the CSV file which I created using the Excel Import Data utility. The data in there is exactly as it would be in the NewBooks table. I tried running the snippet above by taking all the conversions out and got the following error message:

DECLARE @bcpCommand varchar(2000)
SET @bcpCommand = SKY FROM isbnexport" queryout c:\temp\output.csv -c -CACP -t, -T'
EXEC master..xp_cmdshell @bcpCommand

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ' queryout c:\temp\output.csv -c -CACP -t, -T'
EXEC master..xp_cmdshell @bcpCommand
'.
0
 
HoggZillaCommented:
I see this in your message:
SET @bcpCommand = SKY FROM isbnexport" queryout c:\temp\output.csv -c -CACP -t, -T'
Should be this above:
 SET @bcpCommand = 'bcp " SELECT ISBN, CASE Condition WHEN  'BrandNew' THEN 'New' WHEN  'Like New' THEN 'Used - Like New' WHEN  'LikeNew'  THEN 'Used - Like New' WHEN  'Very Good' THEN 'Used - Very Good' WHEN  'VeryGood' THEN 'Used - Very Good' WHEN  'Good' THEN 'Used - Good' WHEN  'Acceptable'  THEN 'Used - Acceptable'  ELSE Condition END Condition, SKY FROM NewBooks" queryout c:\temp\output.csv -c -CACP -t, -T'

0
 
bpfsrAuthor Commented:
Sorry, what would it be if I left all the  - CASE Condition WHEN  'BrandNew' THEN 'New' - conversions out?
0
 
HoggZillaCommented:
DECLARE @bcpCommand varchar(2000)
SET @bcpCommand = 'bcp " SELECT ISBN, Condition, SKY FROM NewBooks" queryout c:\temp\output.csv -c -CACP -t, -T'
EXEC master..xp_cmdshell @bcpCommand
0
 
bpfsrAuthor Commented:
We are making progress! The BCP utility is working but still not writing the CSV file; I am getting the following error message:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
NULL
0
 
HoggZillaCommented:
You should do a couple things. Make sure your SQL Server allows remote connections.
http://support.microsoft.com/kb/914277 
And add this to your SQL. Put the Server.Schema.tableName for NewBooks. So it might be [YourServer].[dbo].NewBooks.
0
 
bpfsrAuthor Commented:
Hello, I changed it to:
DECLARE @bcpCommand varchar(2000)
SET @bcpCommand = 'bcp " SELECT ISBN, Condition, SKU, SKY FROM DELL\SQLExpress.dbo.NewBooks" queryout g:\temp\output.csv -c -CACP -t, -T'
EXEC master..xp_cmdshell @bcpCommand

I changed the drive to 'g' because I want to write it to a flash drive. DELL\SQLExpress is server name, should I be including the database name as well? I still get the same error:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
NULL

I do have the access set to local and remote as shown in the screenshot below.

BCP-example.bmp
0
 
HoggZillaCommented:
You might be ok with just using
DatabaseName.Schema.TableName
 
0
 
bpfsrAuthor Commented:
very frustrating, getting the same error message...
0
 
bpfsrAuthor Commented:
I know its close because at least now there is an error message whereas before yesterday we didn't even get that far. I am searching the internet for any posts on that error message...
0
 
HoggZillaCommented:
Sometimes it could be so helpful to be there, sorry. Let me ask a few questions:
Are you entering this command in SQL Server Management Studio?
You verified earlier you could use the xp_cmdshell, right?
I apologize that I cannot test BCP at my office, it looks like BCP is not talking to your SQL Server. Hmmm. Can you look at this documentation and verify you can get BCP to work? Maybe try the Command Line instead of xp_cmdshell to verify. BCP is a utility and when you call it from T-SQL it probably needs some help finding the queryout database.
http://msdn.microsoft.com/en-us/library/aa174646.aspx 
0
 
bpfsrAuthor Commented:
I do appreciate the help and persistence! Yes, I am running it from Mngmt Studio and I was able to verify I can use the xp_cmdshell. I'll check the documentation and get back to you. Thanks again...
0
 
HoggZillaCommented:
'bcp " SELECT ISBN, Condition, SKY FROM [YourDatabaseName].dboNewBooks" queryout c:\temp\output.csv -S [servername] -U [user] -P [password] -c'  
I think if you configure the bcp according to the instruction provided at the link above, it will look something like this.
0
 
bpfsrAuthor Commented:
I don't know if we are making progress or taking one step forward and two back! I did some research and made some changes. I adjusted the query (I tried so many versions I can't even list them all here. Got various errors that made me think it was a formating problem, like 'incorrect column SKY", etc. This is the first that didn't have a problem with the actual query) so it now reads:

DECLARE @bcpCommand varchar(2000)
SET @bcpCommand = 'bcp " SELECT ISBN, Condition, SKU FROM [MyDatabase.MDF].dbo.NewBooks" queryout g:\temp\output.txt -c -CACP -t, -T -S'+ 'dell\sqlexpress'
EXEC master..xp_cmdshell @bcpCommand

Only now the error is:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

Think that's progress but still not working. Any suggestions, of course, apprecaited.

SQLState = HY000, NativeError = 0


0
 
bpfsrAuthor Commented:
HZ:
I tracked this error message down in MSDN and found an instance where the poster was getting the exact same error and using SQL Server Express, as I am. His query looked almost the same as ours. He did not get a solution posted but at the end of the string he says "I got this working. It was having a problem with the format file not the "host" data file". Do you have any ideas what he means by that?
0
 
HoggZillaCommented:
I will test this at home tonight.  I don't think it is the BCP utitlity because I understand it is the same utility in the enterprise edition as in express.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 18
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now