Excel/SQL Server question 2

[chris123321]

In an excel sheet, there is a zip code column. The data type of that column in excel is General. The zip code can have letters as it contains international zip codes.

After importing the data from excel into a sql server table, the zip codes that don't have any letters have a value of NULL. The zip code data type in sql server is nvarchar(15). For example, a zip code in Excel that is 10001A will import properly into the table, but a zip code as 7015 will import as NULL into the table.

What I've tried:

1) Create a new column as text in Excel, copy the values from the original column, and then import the data into the sql table. Still got NULL values for 7015.

What has worked is adding a single quote to 7015 in Excel and then importing into sql. This works, but the problem is that I'm not sure how to programatically do this for all the rows. Any suggestion?

Also adding zzzz to 7015 and then importing into sql works as well. But how can I do this programatically?

Any other suggestions?

 

[SkipVought]

Hi,

ZIP codes are Identifiers, and as such, ought to be TEXT.

However, in Excel, you cannot just FORMAT the column as TEXT. Format does not change the data. It only changes how your data might be displayed if it is a NUMBER.

The NUMBERS must be converted to TEXT, by prefixing with a single quote, for instance or adding a trailing space.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[chris123321]

Thanks for the reply, Skip. That worked.

How would I be able to add a trailing space programmatically to the Zip column as there are thousands of rows?

Thanks.

 

[SkipVought]

dim r as range
for each r in range(Z1:Z999) 'or whatever range your ZIPS
  with r
    if isnumeric(.value) then _
      .value = cstr(.value & " ")
  end with
next

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[ESquared]

No need to add a trailing space or prefix with a single quote. And no need for code. You can either modify your import to make it a real text column, or you can use formulas to fix the column afterward.

- If pasting, change the column to text before you paste.
- If the data is in the cell (not as NULL) without leading zeroes, use a formula to fix it in the next column over. Copy the entire formula column, paste special, values. Change column to text. Do a text-to-columns on that column and tell it text again, and it's truly text now. Text-to-columns (on the data menu) is also a way to get Excel to reinterpret things that as numbers things are displaying as text but shouldn't, including formulas (which must be a number format such as General, at least something besides text, in order to be calculated).

There is always a way to tell Excel that a column should be interpreted as text. It can be a real hunt to find the setting or syntax to get the job done, but it CAN be done. Sometimes an alternate import/export method is required.

Anyway, how exactly are you doing the import? I can't give you a specific solution until I know exactly what you're doing.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[chris123321]

Thanks for the help, Skip.
Can you recommend a book that teaches VBA?

 

[chris123321]

Just read your post E-Squared.

I'm doing the import thru enterprise manager. Is that what you wanted to know?

What does text to column do? I did exactly what you recommended till text-to-columns. Will try it again and let you know the results.

Thanks.

 

[SkipVought]

ESquared, Text to Columns... ==> [highlight]*[/highlight]

Why didn't I think of THAT?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[ESquared]

Whether you're using "Copy table(s) and view(s) from the source database" or "Use a query to specify the data to transfer", there is a place in the DTS Import/Export wizard where you can click a button with three dots on it that is in the column "Transform." Hit that button and make sure it's a text data type that is coming across. It works for me... try it and see.

Now, there is some registry setting or something that you can set that tells the Excel database driver to scan ALL the rows in an import to decide its data type instead of just a sample of 100 (or whatever it is). If what I suggested above doesn't do the trick, you might try to find that setting.

Another way to deal with the problem is to add an order by clause to that make sure that the rows which are having problems are at the top of the export so when the top X rows are sampled to see the appropriate data type, the weird ones are not missed.

Let us know what happens.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]