This is a brilliant way to export data! Thanks a lot for sharing this info and the script. I have tested and it is working fine to download a single table though I am running into trouble when trying to do it for 2 tables. Am I missing something? It returns me an error: Unable to open BCP host data-file I have tried several options but it looks like I can't figure this out. I know I can create n-bat file one for each table but I'd really love to have all in one place, easy to control Please let me know if you can advice on this
Hello Aqil, so while exporting to txt file or csv file if we go to advanced tab in the connection manager, we can name each and every column and enable the option "Column names in the first data row". This can also solve the problem right rather than this script?
Although I never tried exporting the data using bcp to shared path, but in this post they are saying about providing read\write access to some user www.sqlservercentral.com/forums/topic/exporting-a-file-to-a-network-or-local-path-using-bcp
Hi aqil,your teaching is awesome, I have a one question if we have a flat file having 105 records,we have to split that into 11 sheets. Every sheet has 10 records and last sheet has 5 records how can we do that?
Can you share your email id ? I have written some code for you in C# which can export data from CSV file into an excel sheet and as discussed it will export data into 11 sheets. Every sheet will contain 10 records and 11th sheet will contain 5 records.
Do you know how yo do it but to an Excel file? I mean not a CSV, I need a .xls file, and not using the export tool wizard nor SSIS. I did it in the past but I don't remember well, I think it was with bcp or OpenRowSet, ¿do you know those solutions? Thank you
Hello Bro, Assume there are 1 lakh records in sql table . I want to split the data into 10 csv files with 10k rows/file using BCP Command. how we can do this .can you explain with brief
I have not done it before may be you can take a look at this link and see the last solution suggested here stackoverflow.com/questions/17632236/exporting-sql-server-table-to-multiple-part-files
I am trying using this. but some where it is going wrong DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME) INSERT INTO @TestData20 (IntValCol, DateCol) VALUES (1,'09/05/2020'), (2,'09/05/2020'), (3,'09/06/2020'), (4,'09/06/2020'), (5,'09/07/2020'), (6,'09/07/2020'), (7,'09/08/2020'), (8,'09/08/2020'), (9,'09/09/2020'), (10,'09/09/2020'),(11,'09/10/2020'), (12,'09/10/2020')
-- Declaring Variables DECLARE @MinDate DATETIME, @MaxDate DATETIME,@FileName VARCHAR(30), @FilePath VARCHAR(100), @BCPCommand VARCHAR(4000) --Assigning Values To Variables SELECT @MinDate = MIN(DateCol) FROM @TestData20 SELECT @MaxDate = MAX(DateCol) FROM @TestData20 --Creating the loop WHILE @MinDate
@@muraliuppara9276 Hi, as I already told you I have not done this before, thus I am 100 % not sure how we can do this. Also at the moment I am out of station, thus won't be able to test it. Can you try to put this question on stackoverflow, they give very genuine and fast answers. Just make an account on the site and put your question there.
Hi sir , My data is not coming from a single table it is using 3 table and join to get data ...now I am able to export data in Excel but how can I add header to the file
You can watch this video, how you can export the data from SQL Server to Excel file. You would need to create a spreadsheet in the excel file. th-cam.com/video/88XH96gSA0U/w-d-xo.html
This is a brilliant way to export data! Thanks a lot for sharing this info and the script. I have tested and it is working fine to download a single table though I am running into trouble when trying to do it for 2 tables. Am I missing something?
It returns me an error: Unable to open BCP host data-file
I have tried several options but it looks like I can't figure this out.
I know I can create n-bat file one for each table but I'd really love to have all in one place, easy to control
Please let me know if you can advice on this
I tried, but the the step did not generate any output from job scheduler
Hello Aqil, so while exporting to txt file or csv file if we go to advanced tab in the connection manager, we can name each and every column and enable the option "Column names in the first data row". This can also solve the problem right rather than this script?
Correct. Yes it will save the problem if you are using SSIS data flow task to export the data to csv file with header information.
How to export data to shared path? When I am doing this I am getting error like: Unable to open bcp host data- file.
Although I never tried exporting the data using bcp to shared path, but in this post they are saying about providing read\write access to some user
www.sqlservercentral.com/forums/topic/exporting-a-file-to-a-network-or-local-path-using-bcp
Hi aqil,your teaching is awesome, I have a one question if we have a flat file having 105 records,we have to split that into 11 sheets. Every sheet has 10 records and last sheet has 5 records how can we do that?
Can you share your email id ? I have written some code for you in C# which can export data from CSV file into an excel sheet and as discussed it will export data into 11 sheets. Every sheet will contain 10 records and 11th sheet will contain 5 records.
@@learnssis im sending my mail but it is auto deleted..
@@logohackz Email me at aqil33@gmail.com
@@learnssis hi aqil can we achieve this problem with for loop?
@@logohackz I have sent you the code in C#.
Good video , Thank you help me a lot.
You are most welcome.
Do you know how yo do it but to an Excel file? I mean not a CSV, I need a .xls file, and not using the export tool wizard nor SSIS. I did it in the past but I don't remember well, I think it was with bcp or OpenRowSet, ¿do you know those solutions? Thank you
Sorry don't know if we can do it using bcp or openrowset. I normally do it using SSIS or C#.
@@learnssis thank you so much for your quick answer
Thank you so much!
You are most welcome.
AND IS THERE A SCRIPT TO DO IT DIRECTLY IN A PROCEDURE? I AM STRUGGLING WITH IT AND I CAN'T FIND ANY SOLUTION.
I don't think it is possible with SP, that was one option with bcp, in other options you can use a programming language like C# or you can use SSIS.
Hello Bro,
Assume there are 1 lakh records in sql table . I want to split the data into 10 csv files with 10k rows/file using BCP Command. how we can do this .can you explain with brief
I have not done it before may be you can take a look at this link and see the last solution suggested here
stackoverflow.com/questions/17632236/exporting-sql-server-table-to-multiple-part-files
@@learnssis I have tried but it is not doing anything can you help me please
please can you provide some assistance
I am trying using this. but some where it is going wrong
DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME)
INSERT INTO @TestData20 (IntValCol, DateCol) VALUES
(1,'09/05/2020'), (2,'09/05/2020'), (3,'09/06/2020'), (4,'09/06/2020'),
(5,'09/07/2020'), (6,'09/07/2020'), (7,'09/08/2020'), (8,'09/08/2020'),
(9,'09/09/2020'), (10,'09/09/2020'),(11,'09/10/2020'), (12,'09/10/2020')
-- Declaring Variables
DECLARE @MinDate DATETIME, @MaxDate DATETIME,@FileName VARCHAR(30), @FilePath VARCHAR(100), @BCPCommand VARCHAR(4000)
--Assigning Values To Variables
SELECT @MinDate = MIN(DateCol) FROM @TestData20
SELECT @MaxDate = MAX(DateCol) FROM @TestData20
--Creating the loop
WHILE @MinDate
@@muraliuppara9276 Hi, as I already told you I have not done this before, thus I am 100 % not sure how we can do this. Also at the moment I am out of station, thus won't be able to test it. Can you try to put this question on stackoverflow, they give very genuine and fast answers. Just make an account on the site and put your question there.
Hi sir ,
My data is not coming from a single table it is using 3 table and join to get data ...now I am able to export data in Excel but how can I add header to the file
You can watch this video, how you can export the data from SQL Server to Excel file. You would need to create a spreadsheet in the excel file.
th-cam.com/video/88XH96gSA0U/w-d-xo.html
@@learnssis ok ..thankyou i will see and implement same
Thankyou...my issue resolved because of ur video 😊
@@vaibhavsrivastava8285 Glad to know this.
I want a method to export all tables in db to csv at once please help me to achieve that 😔
Take a look at this method.
th-cam.com/video/I7em3qfJ0N4/w-d-xo.html
@@learnssis is there no method directly from the SQL studio instead of coding i have restrictions to use the code there that's why
@@kishore6399 Sorry I am not aware about other method for all tables.
Sir Hindi me bhi video bnayye
Bhai English me video banane ke liye hi time nikalna mushkil hota he, Hindi me video banane ke liye fir aur time chahiye hoga.