[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Comma Delimited Text

Posted on 2004-10-27
10
Medium Priority
?
2,512 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
Comment
Question by:davidlars99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12427728
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
ID: 12427759
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
ID: 12427809
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 13

Author Comment

by:davidlars99
ID: 12427913
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
ID: 12427935
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
ID: 12427943
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
ID: 12427956
SashP, your version is very cool, but I don't think I'll be using it...  :)
0
 
LVL 13

Author Comment

by:davidlars99
ID: 12427969
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:
Brendt Hess earned 2000 total points
ID: 12428435
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
ID: 12428565
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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