SQL SERVER 2005 - Export tables en CSV

Version imprimable

20/12/2010, 16h58
ash_rmy

SQL SERVER 2005 - Export tables en CSV

Bonjour tout le monde,

J'ai une cinquantaine de tables à exporter de SQL vers CSV. Comment faire pour automatiser l'export SVP.

Merci.

ash_rmy.
20/12/2010, 17h11
mikedavem

Bonsoir,

Quelle version de SQL Server ?

++

C'est du SQL SERVER 2005, mais pour plus de d'info sur l'édition. Donne nous le résultat de cette commande
Code:

1 2 3 4 5 6 7 SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion ,SERVERPROPERTY('ProductLevel') AS ProductLevel ,SERVERPROPERTY('Edition') AS Edition ,SERVERPROPERTY('EngineEdition') AS EngineEdition

21/12/2010, 04h57
SQLpro

utilisez bcp.exe ou SSIS.

Pour bcp vous pouvez le faire sure toutes vos tables via du SQL dynamique appelant la procédure xp_cmdshell.

A +

Bonjour,

Vous pouvez faire cela à l'aide de l'utilitaire BCP et d'une table contenant le nom des tables à exporter :

Code:

1
2
3
4
5
CREATE TABLE bcp_tables_to_export_in_CSV_file
(
	table_name sysname CONSTRAINT PKbcp_tables_to_export_in_CSV_file PRIMARY KEY
	, exported bit NOT NULL CONSTRAINT DFbcp_tables_to_export_in_CSV_file DEFAULT 0
)

Ajouter le nom des tables comme suit pour chacune d'entre-elles :

Code:

INSERT bcp_tables_to_export_in_CSV_file (table_name) VALUES ('maTable')

Ceci fonctionne pour le cas où toutes les tables sont dans le même schéma, par défaut dbo.

Utilisez ensuite la procédure stockée suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
ALTER PROCEDURE proc_export_tables_to_CSV_file
	@_export_file nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON
 
	-- Checkiing directory existence
	DECLARE	@tb_directory_exists TABLE
	(
		file_exists bit
		, file_is_a_directory bit
		, parent_directory_exists bit
	)
 
	DECLARE	@directory nvarchar(255)
	SELECT	@directory = LEFT(@_export_file, LEN(@_export_file)-  CHARINDEX('\', REVERSE(@_export_file)))
 
	INSERT	@tb_directory_exists
	EXEC	xp_fileexist @directory
 
	IF EXISTS
	(
		SELECT	*
		FROM	@tb_directory_exists
		WHERE	file_is_a_directory = 0
	)
	BEGIN
		RAISERROR('The "%s" directory does not exist. Create it before executing this procedure.', 16, 1, @directory)
		RETURN
	END
 
	-- Proceeding export
	UPDATE	dbo.bcp_tables_to_export_in_CSV_file
	SET	exported = 0
 
	DECLARE @bcp varchar(1024)
 
	WHILE EXISTS
	(
		SELECT	*
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
	)
	BEGIN
		SELECT	TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name
			+ ' out "' + @_export_file + '" -c -T -t, –S' + @@SERVERNAME
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
 
		PRINT	@bcp
 
		EXEC	xp_cmdshell @bcp
 
		UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
		SET	exported = 1
	END
END

@++ ;)

21/12/2010, 12h29
zinzineti

@ elsuket
Très bonne idée :ccool:

Sur une instance par défaut ça marche nickel, mais sur une instance nommée, il y a un petit souci sur la ligne

Code:

' out "' + @_export_file + '" -c -T -t, –S' + @@SERVERNAME

Que j'ai remplacé par

Code:

' out "' + @_export_file + '" -c -T -t, -S ' +'"'+ @@SERVERNAME +'"'

ça me donne des idées pour faire de cette manière là des imports.

Thanks :ccool:
21/12/2010, 13h02
mikedavem

Pour SSIS aussi (ben oui faut bien donner toutes les solutions :mouarf:) il suffit de faire un script qui liste l'ensemble de vos tables et integrer cela dans un foreach loop qui exportera les donnees de vos tables une a une.

++

Encore un correctif,

Description du bug
------------------
Lorsqu'il y a plus d'une ligne dans la table bcp_tables_to_export_in_CSV_file il y a une boucle infinie sur :

Code:

1
2
UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
SET	exported = 1

Correctif
------------------

Code:

1
2
3
UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
SET	exported = 1
WHERE	exported = 0

Procédure stockée après correction
--------------------------------

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
ALTER PROCEDURE proc_export_tables_to_CSV_file
	@_export_file nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON
 
	-- Checkiing directory existence
	DECLARE	@tb_directory_exists TABLE
	(
		file_exists bit
		, file_is_a_directory bit
		, parent_directory_exists bit
	)
 
	DECLARE	@directory nvarchar(255)
	SELECT	@directory = LEFT(@_export_file, LEN(@_export_file)-  CHARINDEX('\', REVERSE(@_export_file)))
 
	INSERT	@tb_directory_exists
	EXEC	xp_fileexist @directory
 
	IF EXISTS
	(
		SELECT	*
		FROM	@tb_directory_exists
		WHERE	file_is_a_directory = 0
	)
	BEGIN
		RAISERROR('The "%s" directory does NOT exist. CREATE it before executing this procedure.', 16, 1, @directory)
		RETURN
	END
 
	-- Proceeding export
	UPDATE	dbo.bcp_tables_to_export_in_CSV_file
	SET	exported = 0
 
	DECLARE @bcp varchar(1024)
 
	WHILE EXISTS
	(
		SELECT	*
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
	)
	BEGIN
		SELECT	TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name
			+ ' out "' + @_export_file + '" -c -T -t, -S ' +'"'+ @@SERVERNAME +'"'
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
 
		PRINT	@bcp
 
		EXEC	xp_cmdshell @bcp
 
		UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
		SET	exported = 1
		WHERE exported = 0
	END
END

3ème BUG : Description
---------------

L'ajout (APPEND) dans le fichier de sortie ne s'effectue pas.
le dernier export de la dernière table écrase les données existant dans le fichier.
Résultat : le fichier contient uniquement les données de la dernière table exportée

Correction
----------------------

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
ALTER PROCEDURE proc_export_tables_to_CSV_file
	@_export_file nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON
 
	-- Checkiing directory existence
	DECLARE	@tb_directory_exists TABLE
	(
		file_exists bit
		, file_is_a_directory bit
		, parent_directory_exists bit
	)
 
	DECLARE	@directory nvarchar(255)
	SELECT	@directory = LEFT(@_export_file, LEN(@_export_file)-  CHARINDEX('\', REVERSE(@_export_file)))
 
	INSERT	@tb_directory_exists
	EXEC	xp_fileexist @directory
 
	IF EXISTS
	(
		SELECT	*
		FROM	@tb_directory_exists
		WHERE	file_is_a_directory = 0
	)
	BEGIN
		RAISERROR('The "%s" directory does NOT exist. CREATE it before executing this procedure.', 16, 1, @directory)
		RETURN
	END
 
	-- Proceeding export
	UPDATE	dbo.bcp_tables_to_export_in_CSV_file
	SET	exported = 0
 
	DECLARE @bcp varchar(1024)
	DECLARE @_export_file_temp varchar(1024)
	DECLARE @append varchar(1024)
	SET @_export_file_temp = 'c:\temp\file_temp.csv'
 
	WHILE EXISTS
	(
		SELECT	*
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
	)
	BEGIN
 
		SELECT	TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + @_export_file_temp  +'" -c -T -t, -S ' +'"'+ @@SERVERNAME +'" ' 
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
 
		PRINT	@bcp
 
		EXEC	xp_cmdshell @bcp
 
		-- Ajout dans le fichier de sortie (Ajouté par zinzineti)
		SET @append = 'type ' + @_export_file_temp + ' >> ' +  @_export_file 
		EXEC master..xp_cmdshell @append
		-- MAJ table des tables (Modifiée par zinzineti)
		UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
		SET	exported = 1
		WHERE exported = 0
	END
END

Merci pour lé débuggage Zinzineti ;)

On peut aussi penser à exporter avec un fichier par table, ce qui fait que l'on peut se passer de l'append :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
ALTER PROCEDURE proc_export_tables_to_CSV_file
	@_directory_path nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON
 
	-- Checking directory existence
	DECLARE	@tb_directory_exists TABLE
	(
		file_exists bit
		, file_is_a_directory bit
		, parent_directory_exists bit
	)
 
	INSERT	@tb_directory_exists
	EXEC	xp_fileexist @_directory_path
 
	IF EXISTS
	(
		SELECT	*
		FROM	@tb_directory_exists
		WHERE	file_is_a_directory = 0
	)
	BEGIN
		RAISERROR('The "%s" directory does not exist. Create it before executing this procedure.', 16, 1, @_directory_path)
		RETURN
	END
 
	-- Proceeding export
	UPDATE	dbo.bcp_tables_to_export_in_CSV_file
	SET	exported = 0
 
	DECLARE @bcp varchar(1024)
		, @export_file_temp varchar(1024)
		, @append varchar(1024)
		, @table_name sysname
 
	SET	@_export_file_temp = @directory + '\file_temp' + @file_extension
 
	WHILE EXISTS
	(
		SELECT	*
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
	)
	BEGIN
 
		SELECT	TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + table_name  + '.csv" -c -T -t, -S ' + '"' + @@SERVERNAME + '"' 
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
 
		PRINT	@bcp
 
		EXEC	xp_cmdshell @bcp
 
		-- MAJ table des tables (Modifiée par zinzineti)
		UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
		SET	exported = 1
		WHERE	exported = 0
	END
END

Et donc d'écrire une procédure stockée de chargement des données à partir des fichiers, à l'aide de BULK INSERT, puisqu'il est fort probable que les tables n'aient pas la même structure.

@++ ;)

Citation:

Envoyé par elsuket

On peut aussi penser à exporter avec un fichier par table, ce qui fait que l'on peut se passer de l'append :

Et donc d'écrire une procédure stockée de chargement des données à partir des fichiers, à l'aide de BULK INSERT, puisqu'il est fort probable que les tables n'aient pas la même structure.

@++ ;)

Oui c'est ce que je pense aussi, vu que la demande initiale de ash_rmy
est :

Citation:

ash_rmy
J'ai une cinquantaine de tables à exporter de SQL vers CSV. Comment faire pour automatiser l'export SVP.

J'ai relevé des coquilles dans le script que tu proposes pour cette opération :

1.) les paramètres suivants sont inutiles :
--> @directory
--> @export_file_temp
--> @_export_file_temp (celui-ci n'était pas déclaré !)
--> @append

2.) la ligne suivante est inutile :

--> SET @_export_file_temp = @_directory_path + '\file_temp' + @file_extension

3.) J'ai remplacé la ligne suivante

Code:

SELECT TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + table_name + '.csv" -c -T -t, -S ' + '"' + @@SERVERNAME + '"'

par

Code:

SELECT TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + @_directory_path + table_name + '.csv" -c -T -t, -S ' + '"' + @@SERVERNAME + '"'

Et finalement
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 /* EXEC proc_export_tables_to_CSV_file 'E:\' */ ALTER PROCEDURE proc_export_tables_to_CSV_file @_directory_path nvarchar(255) AS BEGIN SET NOCOUNT ON -- Checking directory existence DECLARE @tb_directory_exists TABLE ( file_exists bit , file_is_a_directory bit , parent_directory_exists bit ) INSERT @tb_directory_exists EXEC xp_fileexist @_directory_path IF EXISTS ( SELECT * FROM @tb_directory_exists WHERE file_is_a_directory = 0 ) BEGIN RAISERROR('The "%s" directory does not exist. Create it before executing this procedure.', 16, 1, @_directory_path) RETURN END -- Proceeding export UPDATE dbo.bcp_tables_to_export_in_CSV_file SET exported = 0 DECLARE @bcp varchar(1024) -- , @export_file_temp varchar(1024) (modifié par zinzineti) -- , @append varchar(1024) (modifié par zinzineti) , @table_name sysname --SET @_export_file_temp = @_directory_path + '\file_temp' + @file_extension (modifié par zinzineti) WHILE EXISTS ( SELECT * FROM dbo.bcp_tables_to_export_in_CSV_file WHERE exported = 0 ) BEGIN --(modifié par zinzineti) SELECT TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + @_directory_path + table_name + '.csv" -c -T -t, -S ' + '"' + @@SERVERNAME + '"' FROM dbo.bcp_tables_to_export_in_CSV_file WHERE exported = 0 PRINT @bcp EXEC xp_cmdshell @bcp -- MAJ table des tables (Modifiée par zinzineti) UPDATE TOP (1) dbo.bcp_tables_to_export_in_CSV_file SET exported = 1 WHERE exported = 0 END END

26/12/2010, 11h12
elsuket

Décidément les vacances me sont bien nécessaires !
Encore merci et Joyeux Noël à toi ;)

@++ ;)

Citation:
Envoyé par zinzineti
C'est du SQL SERVER 2005, mais pour plus de d'info sur l'édition. Donne nous le résultat de cette commande
Code:

1 2 3 4 5 6 7 SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion ,SERVERPROPERTY('ProductLevel') AS ProductLevel ,SERVERPROPERTY('Edition') AS Edition ,SERVERPROPERTY('EngineEdition') AS EngineEdition
Bonjour tout le monde,

merci beaucoup pour votre aide.

Déjà en exécutant la requête ci-dessous, voici ce que j'ai :

ProductVersion = 9.00.3073.00
ProductLevel = SP2
Edition = Enterprise Edition (64-bit)
EngineEdition = 3

Quand j'exécute la procédure proc_export_tables_to_CSV_file, il m'affiche en erreur :

Code:

Nom d'objet 'proc_export_tables_to_CSV_file' non valide.

merci pour votre aide.

ash_rmy.

Citation:

Envoyé par ash_rmy

Quand j'exécute la procédure proc_export_tables_to_CSV_file, il m'affiche en erreur :

Code:

Nom d'objet 'proc_export_tables_to_CSV_file' non valide.

merci pour votre aide.

ash_rmy.

Il faut d'abord créer la procédure avant de l'exécuter. remplace le "ALTER" par "CREATE". Voir ci-dessous

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
/*
EXEC proc_export_tables_to_CSV_file 'E:\'
*/
CREATE PROCEDURE proc_export_tables_to_CSV_file
	@_directory_path nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON
 
	-- Checking directory existence
	DECLARE	@tb_directory_exists TABLE
	(
		file_exists bit
		, file_is_a_directory bit
		, parent_directory_exists bit
	)
 
	INSERT	@tb_directory_exists
	EXEC	xp_fileexist @_directory_path
 
	IF EXISTS
	(
		SELECT	*
		FROM	@tb_directory_exists
		WHERE	file_is_a_directory = 0
	)
	BEGIN
		RAISERROR('The "%s" directory does not exist. Create it before executing this procedure.', 16, 1, @_directory_path)
		RETURN
	END
 
	-- Proceeding export
	UPDATE	dbo.bcp_tables_to_export_in_CSV_file
	SET	exported = 0
 
	DECLARE @bcp varchar(1024)
	--	, @export_file_temp varchar(1024) (modifié par zinzineti)
	--	, @append varchar(1024)           (modifié par zinzineti)
		, @table_name sysname
 
	--SET	@_export_file_temp = @_directory_path + '\file_temp' + @file_extension   (modifié par zinzineti)
 
 
	WHILE EXISTS
	(
		SELECT	*
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
	)
	BEGIN
	    --(modifié par zinzineti)
		SELECT	TOP (1) @bcp = 'BCP ' + DB_NAME() + '.dbo.' + table_name + ' out "' + @_directory_path + table_name  + '.csv" -c -T -t, -S ' + '"' + @@SERVERNAME + '"' 
		FROM	dbo.bcp_tables_to_export_in_CSV_file
		WHERE	exported = 0
 
		PRINT	@bcp
 
		EXEC	xp_cmdshell @bcp
 
		-- MAJ table des tables (Modifiée par zinzineti)
		UPDATE	TOP (1) dbo.bcp_tables_to_export_in_CSV_file
		SET	exported = 1
		WHERE	exported = 0
	END
END

28/12/2010, 09h46
ash_rmy

merci beaucoup pour votre aide. ça marche :)

ash_rmy.