Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Exporting using Import and Export Data Wizard from SQL DB to csv file

I need to export into a file using import and export wizard (either 32 or 64 bit).

The source is SQL Native CLient 10.0.

I need it to be comma delimited, with ( " ) as the text qualifier.

The export has to be like the sample below, where the fields that are numeric only are separated by a comma and not surrounded by ( " ).

The Wizard is not giving me an option of configuring the field mapping types. But it is defaulting those that are numeric fields to "Double Precision Float (DT_R8)"

Any suggestions/solutions are greatly appreaciated

I need the export to be formatted as follow:

"custid","shipperid","lineref","invtid","Descr","QtyOrd","UnitDesc","QtyShip","QtyBO","SlsPrice","DiscPct","TotInvc","TotMerch"
"DOV1000        ","S0254901       ","00001","ZEBZFP03                      ","FUSE 3amp  Pkg25                                            ",0,"EA    ",1,0,9.7159999999999993,0,9.7200000000000006,9.7200000000000006
"DOV1000        ","S0254901       ","00003","EPC10038                      ","Coupling 3/8                                                ",0,"EA    ",6,0,0.57999999999999996,0,3.48,3.48
"DOV1000        ","S0254901       ","00005","EPC10034                      ","Coupling 3/4                                                ",0,"EA    ",6,0,1.6699999999999999,0,10.02,10.02
"HEA4000        ","S0254891       ","00001","WPL8201799                    ","START DEVICE                                                ",0,"EA    ",4,0,27.789999999999999,6,104.48999999999999,104.48999999999999


The Wizard is exporting the following format:

"CustID","ShipperID","LineRef","InvtID","Descr","QtyOrd","UnitDesc","QtyShip","QtyBO","SlsPrice","DiscPct","TotInvc","TotMerch","sNoteText"
"TIF1500        ","S0261666       ","00001","G/HGCWP1000MWW                ","GE 3.2 Cu. Ft. Super Capacity Washer White                  ","1","EA    ","1","0","272.33999999999997","6","256","256","APT.# 1735"
"TIF1500        ","S0261666       ","00002","G/HGTDX200EMWW                ","GE 6.0 cu. ft. capacity DuraDrumâ„¢electric dryer WHITE       ","1","EA    ","1","0","236.16999999999999","6","222","222","APT.# 1735"
"TIF1500        ","S0261666       ","00003","GEHWX9X2                      ","Dryer Cord 4   30A                                          ","1","EA    ","1","0","8.5099999999999998","6","8","8","APT.# 1735"
"TIF1500        ","S0261666       ","00004","BZB010242                     ","4"X8' FLEX FOIL DUCTING                                     ","1","EA    ","1","0","7.4500000000000002","6","7","7","APT.# 1735"
"TIF1500        ","S0261666       ","00005","G/HGSD2100RWW                 ","GE Dishwasher 24" EStar White                               ","1","EA    ","1","0","168.08500000000001","6","158","158","APT.# 1612
"
"WEL1000        ","S0261678       ","00001","G/HGCWP1000MWW                ","GE 3.2 Cu. Ft. Super Capacity Washer White                  ","1","EA    ","1","0","272.33999999999997","6","256","256","APT.# 15-104"
"WEL1000        ","S0261678       ","00002","G/HGTDX200GMWW                ","GE 6.0 cu. ft. capacity DuraDrum gas dryer White            ","1","EA    ","1","0","270.21199999999999","6","254","254","APT.# 15-104"
"PAR6514        ","S0261691       ","00001","G/HJBS07MCC                   ","GE 30" Range Bisque                                         ","1","EA    ","1","0","319","0","319","319","APT.# 243"
"PAR6514        ","S0261691       ","00002","GEHWX9X6                      ","Range Cord 4 ft 3-Wire (40amp 125/250v 8/2,10/1 SRDT)       ","1","EA    ","1","0","8.0030000000000001","0","8","8","APT.# 243"
"PAR6514        ","S0261691       ","00003","G/HJBS07MCC                   ","GE 30" Range Bisque                                         ","1","EA    ","1","0","319","0","319","319","APT.# 178"
"PAR6514        ","S0261691       ","00004","GEHWX9X6                      ","Range Cord 4 ft 3-Wire (40amp 125/250v 8/2,10/1 SRDT)       ","1","EA    ","1","0","8.0030000000000001","0","8","8","APT.# 178"
0
armgon
Asked:
armgon
  • 3
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
Here is how I do something like you need and please replcae your fields in the statement below - should work for you as well:

set nocount on;
declare @filename nvarchar(100)
declare @sqlcmd nvarchar(4000)

--Change GETDATE()-1 below to match the appropriate run date if executed before midnight

set @filename = '"C:\FILE_EXPORT\FILE' + replace(convert(varchar(10),GETDATE()-1,101),'/','') + '.txt"'
set @sqlcmd = 'exec master..xp_cmdshell ''sqlcmd -E -Q"set nocount on; select ''''""''''+ltrim(rtrim(CAST(accountid as varchar(11))))+''''"",""''''+CAST(field1 as char(20))+''''"",""''''+CAST(field2 as char(100))+''''"",""''''+ltrim(rtrim(CAST(field3 as char(8))))+''''"",""''''+CAST(field4 as char(50))+''''"",""''''+CAST(field5 as char(50))+''''"",""''''+CAST(field6 as char(50))+''''"",""''''+CAST(class as char(50))+''''"",""''''+CAST(hosting as char(50))+''''"",""''''+CAST(sku as char(50))+''''"",""''''+CAST(myPlan as char(50))+''''"",""''''+CAST(mydate as char(10))+''''""'''' from sql_server_name.DBname.[dbo].table_name" -h-1 -k -W -o' + @filename + ''', no_output'
--print (@sqlcmd);
exec (@sqlcmd);
0
 
armgonAuthor Commented:
I would like to generate these files with the Export Wizard if all possible.

I used to run this with SQL2000 would create a .csv file that would put double quotes when quotes were found within the text of a specific field. Now SQL2008 will not do it out of the box. I am sure I just have a configuration that I am not hitting correctly.

Also, SQL2008 wants to put quotes around all fields, where prior SQL2000 would only put quotes around those with TEXT values.

See below for what I mean(,"30"" Gas Range w/ electronic ignition White                  ",):

Before SQL2000:
"HAM8000        ","S0205918       ","00001","G/HJGBS07DEMWW                ","30"" Gas Range w/ electronic ignition White                  ",1,"EA    ",1,0,353.19,6,332,332

Today SQL2008 (,"30" Gas Range Bisque w/ electronic ignition Bisque          ",):
"REE1500        ","S0261675       ","00001","G/HJGBS07DEMCC                ","30" Gas Range Bisque w/ electronic ignition Bisque          ","1","EA    ","1","0","275.53100000000001","6","259","259","APT.# 30-102

0
 
lcohanDatabase AnalystCommented:
If you realy want to use that wizzard I sugggest you try to use text not CSV export and change the settings as in the screen shot bellow to suit your needs. export settings
0
 
armgonAuthor Commented:
I tried the above and it would not output with double quotes when a quote is part of the characters in the field.

I appreciate everyones input. But I made a change on the import side on a different server and have accomodated for the problem.
0
 
armgonAuthor Commented:
used tab delimited in place.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now