Friday, August 12, 2011

How to use SQL Server 2008 R2 Import and Export Wizard to import a CSV file into SQL Azure

The SQL Server 2008 R2 Import and Export Wizard will allow you to import a CSV ‘flat’ file directly into SQL Azure. The process is relatively simple, but it’s not obvious. The following steps will help guide you through the process.

The SQL Server Import and Export Wizard is available on the SQL Server 2008 R2 media, so you must obtain your organization’s copy of SQL Server 2008 R2 software.

1. Run the wizard from the Windows Start menu, Start à Programs à SQL Server 2008 R2 à Import and Export Data.

2. Select Flat File Source as the data source, browse for your CSV file, and then select the settings appropriate for your file. For example, the fields in my file are surrounded by double-quotes if they contain commas, so I select double-quotes as my text qualifier. Also, the column names are found in the first row of my file. Click Next.

3. Verify that your data looks as you expected. Click on Advanced on the left.

4. Remember that the wizard is looking at a flat file, so it doesn’t make many assumptions about the data contained therein. It’s up to you to go through each column in this screen and adjust data types and sizes. The following table shows a mapping of data types between the wizard and SQL Server. Click Next after you’ve made your changes.

Data Type

SQL Server

(SQLOLEDB; SQLNCLI10)

DT_BOOL

bit

DT_BYTES

binary, varbinary, timestamp

DT_CY

smallmoney, money

DT_DATE

DT_DBDATE

date

DT_DBTIME

DT_DBTIME2

time(p)

DT_DBTIMESTAMP

datetime, smalldatetime

DT_DBTIMESTAMP2

datetime2

DT_DBTIMESTAMPOFFSET

datetimeoffset(p)

DT_DECIMAL

DT_FILETIME

DT_GUID

uniqueidentifier

DT_I1

DT_I2

smallint

DT_I4

int

DT_I8

bigint

DT_NUMERIC

decimal, numeric

DT_R4

real

DT_R8

float

DT_STR

char, varchar

DT_UI1

tinyint

DT_UI2

DT_UI4

DT_UI8

DT_WSTR

nchar, nvarchar, sql_variant, xml

DT_IMAGE

image

DT_NTEXT

ntext

DT_TEXT

text

Table 1 - Data type mappings (source: http://msdn.microsoft.com/en-us/library/ms141036.aspx)

5. Here’s the part that’s not obvious. You must select .Net Framework Data Provider for SqlServer as the destination. Then, enter the following information.

· Data Source – This is the fully qualified DNS name (FQDN) of your SQL Azure server.

· Initial Catalog – This must be an existing database on the SQL Azure server.

· User ID

· Password

6. Click Edit Mappings.

7. Uncheck the Nullable box for your primary key column. Did I mention that a SQL Azure table must have a primary key? Notice that there isn’t a way to specify the primary key in this dialog. We’ll have to add that to the SQL statement manually. Click the Edit SQL button.

8. Add the “Primary Key” option to the declaration of your primary key column, and click OK.

Note: If you’re using a clustered primary key, please see SQL Server Books Online for the syntax.

9. Review the data type mappings. Note that I receive warnings about the default DT_STR types, so I ignore them.

10. Keep the default setting, Run immediately, click Next, and then click Finish on the next screen.

11. Finally, review the status of the import, and enjoy using your data in SQL Azure.

17 comments:

  1. I used this excellent tutorial a while back to learn the import/export wizard. Thanks. Any chance you have the same knowledge with importing flat data stored in azure storage to sql azure table? Would speed things up, I would think.

    ReplyDelete
    Replies
    1. Glad it helped you! Sorry, I haven't done that. Let me know if you figure it out.

      Delete
  2. Great! Thought I was going to have to go down the 'sync' route :)

    ReplyDelete
  3. Thank you. It took me awhile to figure out the double quotes text qualifier only needed one double quote added. the picture is a little fuzzy, so I was typing in two and wondering why it wasn't working. Great blog - thank you for all the details and pictures.

    ReplyDelete
  4. Start a home business if you have it in your mind. The options and opportunities are huge if you are ready to work seriously in this field. The idea of starting an export import business is a brilliant idea.International Freight Forwarders

    ReplyDelete
  5. It is somewhat fantastic, and yet check out the advice at this treat. ppc management

    ReplyDelete
  6. aşk kitapları
    youtube abone satın al
    takipçi satın al
    takipçi satın al
    takipçi satın al
    takipcialdim.com/tiktok-takipci-satin-al/
    instagram beğeni satın al
    beğeni satın al
    btcturk
    tiktok izlenme satın al
    sms onay
    youtube izlenme satın al
    tiktok jeton hilesi
    tiktok beğeni satın al
    takipçi satın al
    uc satın al
    sms onay
    sms onay
    tiktok takipçi satın al
    tiktok beğeni satın al
    twitter takipçi satın al
    trend topic satın al
    youtube abone satın al
    instagram beğeni satın al
    tiktok beğeni satın al
    twitter takipçi satın al
    trend topic satın al
    youtube abone satın al
    takipcialdim.com/instagram-begeni-satin-al/
    perde modelleri
    instagram takipçi satın al
    instagram takipçi satın al
    takipçi satın al
    instagram takipçi satın al

    ReplyDelete
  7. I advise you to make video about it and use youtube to post it. You should buy youtube subscribers to promote your channel faster

    ReplyDelete
  8. Hmm , very informative article! Thanks to author for it! Don't you think it's worth make a video on this article and post it on Instagram? By the way, you can always use the services of https://viplikes.net/ to quickly increase the number of your followers.

    ReplyDelete
  9. Congratulations on your article, it was very helpful and successful. 1f8ab388f626f1c451ad3f9037216054
    website kurma
    sms onay
    website kurma

    ReplyDelete
  10. Thank you for your explanation, very good content. d1fbe43a244566c2b8ee22f861a36fd7
    altın dedektörü

    ReplyDelete