Import CSV files using DTS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Import CSV files using DTS package

Author

Topic

JonnyMckenna
Starting Member

3 Posts

Posted - 2004-10-14 : 11:50:07

Hi

I am having a problem importing data using a CSV file using a DTS package. Typically when I import the data from a csv it work successfuly, but one particular CSV file contains columns which contains comma's as part of the data. When SQL server imports directly from the CSV file additional columns are created knocking the rest of the row of track.
ROW 1
"THOMAS J","Person","4 street","town","city","country",000,000,000,9999
ROW 2
"person2","person2","59 street hall,","Mount Saint Annes,",000,000,000,0000

ROW 1 creates ten columns which is what is expected however ROW2 creates 12 rows.
Not all of the cells in the CSV cells have double quotes as text qualifiers so an error is thrown when double quotes are used (invalid delimiter). The file is too far large to adjust manually (replace all)and the data cannot be adjusted prior to import. The file cannot be adjusted to for example Excel Document. Is there a possible work around for this problem.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 12:49:26

how large is the file (how many rows?)

you *could* import it all into a single column... but then you would have to parse it back out in SQL... blech!

How is the file generated? COuld you get it with a different delimiter? like '|' or something?

Corey

JonnyMckenna
Starting Member

3 Posts

Posted - 2004-10-14 : 13:13:07

I tried changing the Delimiter and it would work fine however the File is generated externally from my company and the delimiter cannot be changed otherwise several other applications would be impacted, Due to the nature of the content data of the file it cannot be changed prior to execution

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 13:33:59

i suppose you could parse it from one column, but it could prove difficult...

Corey

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-15 : 14:12:10

I would request a file with a different delimiter and if it is an issue for other applications then I woul remove all commas from the file and then change the new delimiter to comma. but why do other applications need the file to have the comma why they don't use the information of the file from the DB once it is successfully loaded?

You can develope an alternate process that will be triggered if the normal import fails in which you upload the file in one column as Corey sugested and then eliminate all commas that are within a field (between cuotes) and then split the column in columns to populate the table it is intended to populate.

EDIT:

Declare @painString as nvarchar(500)
Declare @pos1 as int, @pos2 as int

Set @painString ='"person2","person2","59 street hall,","Mount Saint Annes,",000,000,000,0000'
Set @pos1 =0
Set @pos2 =0

Set @pos1 =isNull(CHARINDEX ( '"', @painString , @pos2+1) ,1)
While @pos1>0
Begin
    Set @pos2 =CHARINDEX ('"',  @painString, @pos1+1)
    Set @painString =
        Left (@painString, @pos1) +
        Replace ( substring (@painString ,@pos1+1,@pos2-@pos1), ',' , ' ') +
        right (@painString, len(@painString) - @pos2)
    Set @pos1 =CHARINDEX ( '"', @painString , @pos2+1)
end

print @painString

Result

"person2","person2","59 street hall ","Mount Saint Annes ",000,000,000,0000

you can also add an Identity column to scroll through the records

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

JonnyMckenna
Starting Member

3 Posts

Posted - 2004-10-15 : 16:55:39

Thanks for the suggestions guys. I used some vb code to treat the file the same way as an excel file. For some reason Excel is able to correctly identify all the delimiters correctly in the csv file... why excel is able to do this and SQL server not I don't know but it works perfectly. This also allows me to extract the specific data i need...

strOpen = mvarDirectoryPath & "\File.csv"

Set oEmpWorkbook = Workbooks.Open(strOpen, , True)

'Select Columns A-J and Rows from start to first column data Ends
Set oEmpRng = Range("A1:J1", Range("A1").End(xlDown))

iTotRows = oEmpRng.Rows.Count
For iRow = 1 To iTotRows
strForeName = SQLText(oEmpRng.Cells(iRow, 4))
strSurName = SQLText(oEmpRng.Cells(iRow, 5))
strAddr1 = SQLText(oEmpRng.Cells(iRow, 6))
strAddr2 = SQLText(oEmpRng.Cells(iRow, 7))
strAddr3 = SQLText(oEmpRng.Cells(iRow, 8))
strAddr4 = SQLText(oEmpRng.Cells(iRow, 9))
strPostCode = SQLText(oEmpRng.Cells(iRow, 10))
Next

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-15 : 18:07:47

thinking about it...

in you dts did you specify that text fields where between cuotes?

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-16 : 01:15:09

You have a quote delimitted strings and it sounds like you haven't defined them as such.
I never use dts for text file manipulation as it's simpler and faster to use bcp.
Here's a format file to import quote delimitted strings
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 01:31:18

DTS does correctly handle a quoted-delimited text file. Correct formatting for this is comma delimited, text columns that [may] contain a comma are surrounded by double-quotes, and any double-quotes in such text columns are doubled up. (You may wish to check that your provider is indeed doing that last step, as it is often overlooked and will break the import - if not now then at some future point!)

What choices are you using when importing the CSV file into SQL?

You need to choose:

Data source = Text File
Delimited
Type = ANSI (presumably)
Row delimiter = {CR}{LF} (presumably)
Text qualifier = DoubleQuote {"}
Skip rows = 0 (unless there are rows to be ignored at the top of the file)
[ ] First row has column headings (Tick this if it is true)

On the next screen choose the delimiter - "Comma"

you will see a representation of your data, in columns. Check that the column breaks appear correctly - using your two sample rows from your first message they do appear to

Then choose the target database, and then the target table - or use a new name to create a new table.

Note that Excel is likely to break your file. For example, if you have a column with a telephone number Excel will import it as a number and drop the leading zero (unless you explicitly tell it that the column is "Text")

Kristen

jimjmc
Starting Member

1 Post

Posted - 2004-11-08 : 17:24:41

I've experienced the same problem with delimiters inside text qualifiers. Another interesting/troubling aspect of this issue is that the file preview (in the Text File Properties form) shows the text file parsed correctly with the delimiter inside the text, but the actual insert happens differently.

As a workaround I used the Data Transformation Task with a Copy Column transformation for each column (not 1 for all columns together). Not as efficient as Bulk Insert, but it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources