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

Posted on 2011-05-11
Last Modified: 2012-05-11
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:

"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:

"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"
Question by:armgon
    LVL 39

    Expert Comment

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

    Author Comment

    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

    LVL 39

    Expert Comment

    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

    Accepted Solution

    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.

    Author Closing Comment

    used tab delimited in place.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    sql calculate reminders 11 52
    SQL Date from a string 4 41
    Copy only dates 3 60
    Order by but want it in specific order 2 14
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now