[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Import CSV file into SQL table

Posted on 2008-02-12
8
Medium Priority
?
2,092 Views
Last Modified: 2010-04-21
I am developing a package to import a flat CSV file into a SQL table.  It does work, but I am unhappy with the performance of over an hour to import an average of 50,000 records.  I first tried the Data Flow Task to map the columns and define the output colun datatypes/lengths to match the SQL destination columns.  Some output columns are to be ignored and are selected as such in the transformation.  I tried initially with the CSV source file location on my local drive, being imported over to the server.  With the poor performance, I then tried the CSV source file location on the same server as the destination, but performance was still poor.
So I then tried a Bulk Insert Task, which I'm not very familiar with.  This does not seem to work since the source columns are larger than the destinationon and/or I have some source columns that need to be ignored.  

Any ideas how I can improve performance of this import?  
0
Comment
Question by:VDanner
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 20877157
I worked w/several very similar projects -- unfortunately, I never got the runtime I needed out of DTS when importing the csv.  I got MUCH better runtimes using bulk insert.

You need only to use a format file.  see BOL:  'Using Format Files'
This will tell you exactly how to workaround the table and file variances.  Let me know if you need an example.
0
 

Author Comment

by:VDanner
ID: 20877181
Since I am so new to Bulk Insert, I would very much appreciate an example.  
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 20877201
sure, gimme one sec
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 17

Expert Comment

by:dbaSQL
ID: 20877318
this is a genericised version of a proc i use to load files.  i actually am loading a LOT more than this, but, i pulled it all out except for one, just to keep things simple:

CREATE PROC dbo.usp_loadFiles
AS
SET NOCOUNT ON

BULK INSERT database.dbo.table
 FROM '\\servername\directoryname\fileA.csv'
WITH(FORMATFILE = 'E:\MSSQL\formatfile.fmt',FIELDTERMINATOR = ',',ROWTERMINATOR = '\r',FIRSTROW=2,TABLOCK,ROWS_PER_BATCH = 100000)

SET NOCOUNT OFF
GO

You will see, I have the format file in there,k as well as the field and row terminators, FIRSTROW and ROWS_PER_BATCH.  

FIRSTROW is necessary because the files have headers (column names).  i need to skip that row before starting to load the data

here is a format file:

8.0
6
1      SQLCHAR      0      50      ","      2      field1      SQL_Latin1_General_CP1_CI_AS
2      SQLCHAR      0      1      ","      3      field2      SQL_Latin1_General_CP1_CI_AS
3      SQLCHAR      0      1      ","      4      field3      SQL_Latin1_General_CP1_CI_AS
4      SQLCHAR      0      4      ","      5      field4      SQL_Latin1_General_CP1_CI_AS
5      SQLCHAR      0      4      ","      6      field5      SQL_Latin1_General_CP1_CI_AS
6      SQLCHAR      0      4      "\n"      7      field6      SQL_Latin1_General_CP1_CI_AS

(i've just cut/pasted... hopefully it prints out ok)

do you have BOL, and if so, you really need to read 'using format files'.  it is exactly what you need to know - and it explains the format file in detail

0
 

Author Comment

by:VDanner
ID: 20877349
Thank you for this example.  I will try out this morning and if all goes well close this question.  I'm not sure what you mean by BOL?  I am working in Business Intelligence Development Studio (BIDS) and have now opened their Help engine, searching on format files.  Between your example and their narratives this should resolve my problem.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 20877428
BOL =  Books Online
http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

basically, it's one of your sql bibles.  or at least it is one of my sql bibles
0
 

Author Closing Comment

by:VDanner
ID: 31430253
Thank you.  Regarding BOL (duh...should have guess that).  I am downloading that version now and feel confident this and your example will do the trick.  
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 20877569
Excellent.  Good luck!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

612 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