• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2515
  • Last Modified:

Comma Delimited Text

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
davidlars99
Asked:
davidlars99
1 Solution
 
Brendt HessSenior DBACommented:
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
 
finalpantsCommented:
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
 
SashPCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
davidlars99Author Commented:
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
 
davidlars99Author Commented:
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
 
davidlars99Author Commented:
I'll try DTS later, but at this point I'd rather use [Bulk Insert] and [Import Data]
0
 
davidlars99Author Commented:
SashP, your version is very cool, but I don't think I'll be using it...  :)
0
 
davidlars99Author Commented:
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
 
Brendt HessSenior DBACommented:
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
 
davidlars99Author Commented:
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now