July 8, 2004 at 8:26 am
Hi,
I don't know well dmo. I have done this vb script to export data from sqlserver's table to csv file. The script do well the job but i have trouble with the date format in my output file(csv).
How can I easily manage the format of the exported data?
Also : bulkcopy is it the best solution to export data?
The amount of data in the tables is huge.
Thanks for your replies.
Best regards
julien
The script :
Dim oServer
Dim oDatabase
Dim oBCP
Dim nRows
dim table
dim oTable
dim sResults
on error resume next
if WScript.Arguments(0) = "" then
WScript.Echo "Vous n'avez pas spécifié de base de données à laquelle se connecter."
WScript.Quit
end if
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
Set oBCP = CreateObject("SQLDMO.BulkCopy")
oServer.EnableBcp = True
oServer.Connect "." ', "sa"
Set oDatabase = oServer.Databases(Wscript.Arguments(0))
msgbox oDatabase.SpaceAvailableInMB
For Each oDatabase In oServer.Databases
For Each oTable In oDatabase.Tables
if oTable.systemobject = false then
sResults = sResults & oTable.Name & Chr(13) & Chr(10)
end if
Next
msgbox sResults
Next
oBCP.ColumnDelimiter = vbComma
oBCP.DataFileType = SQLDMODataFile_CommaDelimitedChar
oBCP.ImportRowsPerBatch = 1000
oBCP.MaximumErrorsBeforeAbort = 1
BCP.RowDelimiter = vbCrLf
oBCP.ServerBCPDataFileType = SQLDMOBCPDataFile_Char
oBCP.UseExistingconnection = True
if wscript.Arguments(1) = "" then
for each table in oDatabase.tables
' pas une table systeme
if table.systemobject = false then
oBCP.DataFilePath = table.name & ".csv"
nRows = table.ExportData(oBCP)
wscript.Echo nRows & " lignes exportées à partir de " & table.name
end if
next
else
oBCP.DataFilePath = wscript.Arguments(1) & ".csv"
nRows = oDatabase.Tables(wscript.arguments(1)).ExportData(oBCP)
wscript.Echo nRows & " lignes exportées à partir de " & wscript.arguments(1)
end if
July 8, 2004 at 9:17 am
I looked in BOL and couldn't find anything where you could use DMO to reformat your datetime data. Maybe using a format file would work, but I didn't see anything in BOL which would indicate you could transform datetime data.
Bulkcopy is fine to export data, though I am not sure how efficient SQL-DMO is compared to command-line bcp or DTS.
If you want more control over the data you are exporting you can use the DTS Import/Export Wizard from EM. It will take a lot more work to set up, but you can use activeX scripts to transform each column you are copying. In this way you can convert your datetime fields into a format suitable for your csv files.
DTS is fine to use for large files, but for best performance schedule the DTS as a job on the server. Running it on your PC will be slower than on the server.
July 8, 2004 at 9:24 am
Just thought of something. Why don't you set up views for each table, use the views to convert your datetime data into char data and use the ExportData method on the view instead of the table.
Again, a lot of work depending on the amount of tables in your database.
Anyone got any better options?
July 8, 2004 at 10:56 am
Thanks a lot Wayne
The last solution with views is not bad (but i have about 22 tables). Why not?
But I think the first one is cleaner with DTS.
A last answer, when you say :
"I looked in BOL" what's BOL?I know I am newbie.
If someone know a solution with bcp to format data, i am interresting.
While waiting, I will seek information on DTS
Best regards
Julien
July 9, 2004 at 1:33 am
July 9, 2004 at 2:19 am
ok thanx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply