SQL SERVER 2005 - Export tables en CSV

**ash_rmy** · 20/12/2010, 16h58

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.

**mikedavem** · 20/12/2010, 17h11

Bonsoir,

Quelle version de SQL Server ?

++

**zinzineti** · 20/12/2010, 22h34

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**SQLpro** · 21/12/2010, 04h57

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 +

**elsuket** · 21/12/2010, 05h24

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

@++

**zinzineti** · 21/12/2010, 12h29

@ elsuket
Très bonne idée

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Que j'ai remplacé par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Thanks

**mikedavem** · 21/12/2010, 13h02

Pour SSIS aussi (ben oui faut bien donner toutes les solutions

) 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.

++

**zinzineti** · 21/12/2010, 15h34

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**zinzineti** · 21/12/2010, 17h30

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**elsuket** · 23/12/2010, 08h55

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

@++

**zinzineti** · 23/12/2010, 11h17

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 :

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Et finalement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**elsuket** · 26/12/2010, 11h12

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

@++

**ash_rmy** · 26/12/2010, 16h35

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

Nom d'objet 'proc_export_tables_to_CSV_file' non valide.

merci pour votre aide.

ash_rmy.

**zinzineti** · 27/12/2010, 14h41

Envoyé par ash_rmy

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**ash_rmy** · 28/12/2010, 09h46

merci beaucoup pour votre aide. ça marche

ash_rmy.

SQL SERVER 2005 - Export tables en CSV

MS SQL Server

Discussions similaires

Partager

Partager