December 9, 2019 at 2:30 pm
Hi All
I am trying to export some data from a SQL server database to a CSV file. As I need to do this for around hundreds of records, I am using a cursor to loop through and creating dynamic file names to write individual files. This seems to be working ok. Some of the columns in my data contains large texts (around 2000 characters), so prefer those columns on the CSV file are formatted to have bigger column widths, word wrapped and vertically aligned to the top. BCP utility has a switch called format, and i am wondering if this can be used to format the the columns on the CSV file? Please advise. If so can I have a sample format file please.
Thanks
Anton
December 9, 2019 at 5:14 pm
format is for creating a format file, not for specifying the format (Arguments). If you want to specify that the file should be in a CSV, then you want to provide "," for the -t switch (field terminator). The default row terminator for bcp is already a line feed (\n).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2019 at 6:09 pm
from the description you are then opening that CSV file with Excel and you wish them to automatically have a particular format applied.
This is not possible with CSV files - you need to do that formatting on Excel yourself - optionally you can create a macro that opens the file and applies the format but otherwise its not possible.
Also note that BCP does not create true CSV files - you will need to take care yourself of any comma (or character equal to whatever delimiter you choose) that is embedded on the text of your fields as well as any record terminator. And if any of these occur within your field contents you need to escape them - field itself written with Double Quotes around it, and internal double quotes escaped with another double quote
December 9, 2019 at 7:08 pm
Thanks for your advice, I have been applying that switch and my export to csv works fine, exported csv file opens fine in excel. only issue I have is that I want the csv file formatted to have certain properties.
December 9, 2019 at 7:14 pm
Thanks Frederico. Yes - you are right, I want the file to be formatted to have certain properties . I am creating around 1000files so can’t do this individually. I thought I can format it through BCP. As you are pointing out this is not possible, I will look into see if I can do this using a macro.
December 10, 2019 at 8:56 am
Thanks Frederico. Yes - you are right, I want the file to be formatted to have certain properties . I am creating around 1000files so can’t do this individually. I thought I can format it through BCP. As you are pointing out this is not possible, I will look into see if I can do this using a macro.
If you want the values to have a certain format then you either need to export to something like an xlsx and set the display property, or export the values in that format (which could result in data loss). With the latter option, that'll likely mean converting all the values to a varchar in your SELECT statement and using style codes to get the right format you are after.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 10, 2019 at 9:54 am
--
-- Create a text for a CSV file. (Not a file).
-- Strings with double qoutes, semicolons or CRLF get qouted and the double qoutes are 'escaped' with an double qoute. :-)
-- Special characters are handled correctly.
-- The 'generated' CSV text is kept fairly minimal.
-- The script itself is kept fairly minimal.
-- The CSV (actualy semi colon seperated) is a 'correct' CSV, but not all applications (like Excel),
-- can handle multiple lines or special characters correctly
--
-- 'T' is the name of the table to be processed. (Replace the T with the actual table name).
--
-- Ben Brugman
--
DECLARE @T CHAR(99)= 'T',@L VARCHAR(MAX) = '',@S CHAR(999)='+CASE WHEN[Q1]LIKE''%[;"
]%''THEN'';"''+REPLACE(CONVERT(VARCHAR(MAX),[Q1]),''"'',''""'')+''"''ELSE'';''+COALESCE(CONVERT(VARCHAR(MAX),[Q1],121),'''')END'
SELECT @L=@L+REPLACE(@S,'Q1',Column_name) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@T
EXEC('SELECT'+@L+' FROM '+@T)
Handling of BBCodes did cause the above code to be mangled 🙁 Bummer).
Now I hope the code displays correctly.
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply