problem importing csv delimited text file into a sql server 2005 table

Hot Topic

What to do with a measure of varying data types?
Importing non-hidden rows from Excel?
2 minutes after one datetime vs. another datetime
Crystal report conversion
Create File Option In File Connection Manager
SQL Browser service won't start
Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.
SOS!! Proc written in C# throwing errors !!
SSIS - Handling different types of record in same flat file source
Change an Access Database to SQL Server

SQL Server

navigation on subtotal
Problem with managed code simple example "Target string size is too small to represent the XML instance"
looping through XML with xquery
Database Mirroring hangs on ALTER DATABASE SET PARTNER
June CTP Sql agent problem
NOT IN + if structure
SQL Server 2005 System Table (View?) Map
100% Stacked Column chart
Use opendatasource
Custom Code in SSAS

problem importing csv delimited text file into a sql server 2005 table

I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:\mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='\n',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"




Answer this question

problem importing csv delimited text file into a sql server 2005 table

  • Ryan Tessier

    I think it is getting confused with the quotes because they seem to be reserved for the program or something. I get an error.

    do you know the bcp command I need to get the xml format I will try that as well.

    Sarah



  • Frank Cassata

    Try this:

    3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 100 ",""" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 100 """," 5 Address1 SQL_Latin1_General_CP1_CI_AS



  • aymenos07

    Hi

     

    You can do this by using a formatfile. You can define each field in a formatfile and specify a differend field terminator for each field.

     

    For example:

    < xml version="1.0" >

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <RECORD>

    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""," MAX_LENGTH="30"/>

    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="""\n" MAX_LENGTH="30"/>

    [....]

    </RECORD> <ROW>

    <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNVARCHAR"/>

    <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>

     === Edited by Hatzi74 @ 25 Apr 2006 2:49 PM UTC===
    Edit2: You could also use the Data Import Wizzard... Select "Flatfile" as source, and then match the fielddelimiters for every field the way you need them to be...

    For example... After a Text -> Number you would need --",-- as a delimiter if you go from text -> text you will need --","-- as a delimiter and when coming from number -> text you need --,"--

    Hope this makes sense. Also the row delimiter could be adjusted to match --"\n-- if the last field is a text field. But this will leave the field "dirty" (It will include a tailing ")

     



  • Active_Matrix

    Thank you for your quick reply. I did try a non xml format file using the bcp command and it came out like the info below. However, It worked on importing but I still had the same problem with the commas as the delimiter. I wanted to add the quotes as a delimiter but I could not figure out how to do that in this type of file.

    I will try a xml format this time but I am not sure by your instructions how to tell it that the file has quotes for the text qualifiers. I think you are saying I need a "," as a delimiter

    Thank you so much for your time and effort. I am going to try it.

    Most sincere,

    Sarah

    my format file below (.fmt type)

    9.0
    18
    1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 100 "," 4 BadAddress SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 100 "," 5 Address1 SQL_Latin1_General_CP1_CI_AS
    6 SQLCHAR 0 100 "," 6 Address2 SQL_Latin1_General_CP1_CI_AS
    7 SQLCHAR 0 100 "," 7 Address3 SQL_Latin1_General_CP1_CI_AS
    8 SQLCHAR 0 100 "," 8 City SQL_Latin1_General_CP1_CI_AS
    9 SQLCHAR 0 100 "," 9 State SQL_Latin1_General_CP1_CI_AS
    10 SQLCHAR 0 100 "," 10 Zip SQL_Latin1_General_CP1_CI_AS
    11 SQLCHAR 0 100 "," 11 Foreign SQL_Latin1_General_CP1_CI_AS
    12 SQLCHAR 0 100 "," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
    13 SQLCHAR 0 100 "," 13 Dpbc SQL_Latin1_General_CP1_CI_AS
    14 SQLCHAR 0 100 "," 14 County SQL_Latin1_General_CP1_CI_AS
    15 SQLCHAR 0 100 "," 15 CountyNo SQL_Latin1_General_CP1_CI_AS
    16 SQLCHAR 0 100 "," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
    17 SQLCHAR 0 100 "," 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
    18 SQLCHAR 0 100 "\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS



  • Fredvanherz

    I am trying that, but it does not seem to be working.

    Sarah



  • papadi

    Hi

    I just wanted to thank you for your reply and that you somewhat directed me into the right direction.

    I just wanted to let you know that I finally figured it out. I had to strip the " quotes out of the ascii file and replace them with | pipes and then I created the fmt (format file) and imported the data. Here is a copy of the fmt file just for your own information. I had to tell it exactly where the pipes started and ended for each field delimter. I tried setting up the format file to look for the quotes but it had a problem with that because " " were being used in the format file.

    Anyways I got it and now I am having a problem accessing the A:\ drive, using the bulk insert command. sql server thinks I am accessing the actual servers A:\ and not my local drive. wow what a nightmare just to read a simple ascii file into a table. Unfortunatley the operator has to import data from floppy diskettes. I will repost this problem.

    Anyways, thank you very much for your imput.

    Most sincere,

    Sarah Diane Reid

    Sample Format File below:

    9.0
    18
    1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 100 ",|" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 100 "|,|" 5 Address1 SQL_Latin1_General_CP1_CI_AS
    6 SQLCHAR 0 100 "|,|" 6 Address2 SQL_Latin1_General_CP1_CI_AS
    7 SQLCHAR 0 100 "|,|" 7 Address3 SQL_Latin1_General_CP1_CI_AS
    8 SQLCHAR 0 100 "|,|" 8 City SQL_Latin1_General_CP1_CI_AS
    9 SQLCHAR 0 100 "|,|" 9 State SQL_Latin1_General_CP1_CI_AS
    10 SQLCHAR 0 100 "|,|" 10 Zip SQL_Latin1_General_CP1_CI_AS
    11 SQLCHAR 0 100 "|,|" 11 Foreign SQL_Latin1_General_CP1_CI_AS
    12 SQLCHAR 0 100 "|," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
    13 SQLCHAR 0 100 ",|" 13 Dpbc SQL_Latin1_General_CP1_CI_AS
    14 SQLCHAR 0 100 "|," 14 County SQL_Latin1_General_CP1_CI_AS
    15 SQLCHAR 0 100 ",|" 15 CountyNo SQL_Latin1_General_CP1_CI_AS
    16 SQLCHAR 0 100 "|," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
    17 SQLCHAR 0 100 ",|" 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
    18 SQLCHAR 0 100 "|\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS



  • problem importing csv delimited text file into a sql server 2005 table
Answer this question