Solved

Comma Delimited Text

Posted on 2004-10-27
2,510 Views
Last Modified: 2012-06-27
Hi,

I have following data in .CVS file (US ZipCode Database)
   
07009       S      Cedar Grove      D      New Jersey      NJ      973      40.854334999999999      -74.231713999999997
07009      S     Overbrook          N     New Jersey       NJ      973        40.854334999999999      -74.231713999999997

Table is ready in database with proper values (char(5) for zipcode) and when I try to import it with (.txt, .cvs) driver from EM I get following values in ZipCode column, all without zero

7009
7009

also any value that has zero as the first character



thank you
 dave
0
Question by:davidlars99
    10 Comments
     
    LVL 32

    Expert Comment

    by:bhess1
    If a CSV table is not Quote/Comma delimited, then any field consisting only of numbers is assumed to be numeric by default.  This is what appears to be happening here.

    You would do better to use DTS or Bulk Insert to import the data
    0
     

    Expert Comment

    by:finalpants
    Make sure when you use that DTS to set your precision (and scale) for your column you're importing the data into.

    If you don't know know about DTS stop everything you're doing right now and go do some research on it.  It could possibly be the greatest thing ever invented.

    --FP
    0
     
    LVL 8

    Expert Comment

    by:SashP
    If you want to add the zero back in after the fact use.

    Update [Your Table Name]
    SET [ZipCode column name] = RIGHT( '00000' + [ZipCode column name], 5)


    Cheers Sash
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    this is .CSV file

    00501,U,Holtsville,D,New York,NY,631,40.813078,-73.046388
    00544,U,Holtsville,D,New York,NY,631,40.813223,-73.049288
    00601,S,Adjuntas,D,Puerto Rico,PR,787,18.165950,-66.723627
    00601,S,Jard de Adjuntas,N,Puerto Rico,PR,787,18.165950,-66.723627
    00601,S,Urb San Joaquin,N,Puerto Rico,PR,787,18.165950,-66.723627
    00602,S,Aguada,D,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Alts de Aguada,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Comunidad Las Flores,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Ext Los Robles,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Parc Palmar Novoa,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Urb Brisas del Mar,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Urb Isabel la Catolica,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Urb Montemar,N,Puerto Rico,PR,787,18.383005,-67.186553
    00602,S,Urb San Cristobal,N,Puerto Rico,PR,787,18.383005,-67.186553
    00603,S,Aguadilla,D,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Bda Caban,N,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Bda Esteves,N,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Bda Nueva,N,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Bo Borinquen,N,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Comunidad Borinquen,N,Puerto Rico,PR,787,18.433236,-67.151954
    00603,S,Comunidad Corrales,N,Puerto Rico,PR,787,18.433236,-67.151954
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    I tryed:

    Bulk insert dbo.ZipCode from 'c:\ZipCodes\premium.csv'
    with (
          Fieldterminator=',',
          Rowterminator='\n'
    )

    and it gives me error:

    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 3092, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 5323, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 5964, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 8757, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 10852, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 16726, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 17018, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 18002, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 18485, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 18543, column 3 (City).
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 20765, column 3 (City).
    Server: Msg 4865, Level 16, State 1, Line 1
    Could not bulk insert because the maximum number of errors (10) was exceeded.
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:  The provider did not give any information about the error.].
    The statement has been terminated.


    looks like something's wrong with these columns, but I checked them one by one and didn't see anything suspisious..
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    I'll try DTS later, but at this point I'd rather use [Bulk Insert] and [Import Data]
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    SashP, your version is very cool, but I don't think I'll be using it...  :)
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    this is the by the way:


    CREATE TABLE [dbo].[ZIPCode] (
          [ZIPCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [ZIPCodeType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [City] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [CityType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [State] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [StateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [AreaCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [Latitude] [float] NULL ,
          [Longitude] [float] NULL
    ) ON [PRIMARY]
    GO
    0
     
    LVL 32

    Accepted Solution

    by:
    Your errors indicate that the City value in those rows is more than 28 characters long.  Thus, the truncation error.  Try altering the table to have City varchar(50), and see if your import works then.
    0
     
    LVL 13

    Author Comment

    by:davidlars99
    strange thing is that, I had .mdb file which I imported in the same table [city varchar(28)] and it worked... is not this frantic..? I would never guess :)

    thanks bhess1
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    877 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

    11 Experts available now in Live!

    Get 1:1 Help Now