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.
| Author |
Topic |
|
golden rule
Starting Member
4 Posts |
 Posted - 2008-05-06 : 00:36:10
|
| Hi all,I have a file with the following zip code data...ZIPCODE||LATITUDE||LONGITUDE||STATE||CITY||COUNTYCHAR(5)||DECIMAL(8,6)||DECIMAL(8,6)||CHAR(2)||VARCHAR(64)||VARCHAR(30)00501||40.817967||-73.045257||NY||HOLTSVILLE||SUFFOLK00544||40.8152||-73.0455||NY||HOLTSVILLE||SUFFOLK00601||18.1788||-66.7516||PR||ADJUNTAS||ADJUNTAS00602||18.381389||-67.188611||PR||AGUADA||AGUADA00603||18.4554||-67.1308||PR||AGUADILLA||AGUADILLA00604||18.4812||-67.1467||PR||AGUADILLA||AGUADILLA00605||18.429444||-67.154444||PR||AGUADILLA||AGUADILLA00606||18.182778||-66.980278||PR||MARICAO||MARICAO00610||18.284722||-67.14||PR||ANASCO||ANASCOI have replaced the double-pipes with commas to enable a simple copy/paste when I use the "INSERT INTO" commands.However, when I use this method, I would have to insert a single quote before and after each state, city, and county to get the row inserted without error. Obviously I'm not going to do that for 7,000 rows of data...and input 21,000 single quotes.When I try importing the file using phpMyAdmin, it takes absolutely forever...to the point where I cancel every time because I'm pretty sure nothing is happening.Is there any resolve to this? How do I get this data into my ZIPCODES Table? I've spent hours trying to manipulate my commands, the data, and anything else I can think of. I'm sure there is a solution, I'm just limited in my knowledge to figure it out. Hope all is well and thanks in advance for any help or response! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-05-06 : 08:11:41
|
Can you read it into Excel first?You wouldn't need to get rid of the || then either as Excel would do this for you. You could then run something like the following:select *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="<path>\<filename>";Extended properties=Excel 8.0')...[<worksheetname>$] a |
 |
|
|
|
|
|
|
|
Zip Code database import issues...