How to Import data with leading zero

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • benchpolo
    New Member
    • Sep 2007
    • 142
    #1

    How to Import data with leading zero

    I have an excel spreadsheet (.csv) format of list of zipcodes that I am trying to import to my custom table zipcodes.. I having issue in importing a zip code with leading zero. Can someone assist me on this? I'm using MSSQL server 2005 import/export wizard.
    Thanks.
    Tags: None
    • Jerry Winston
      Recognized Expert New Member
      • Jun 2008
      • 145
      #2
      Make sure you're storing the zip codes in a string type field. Of course, there are no leading zeroes with numeric type fields, therefore I tend to think the Import/Export Wizard analyzed the data in the csv file and auto typed the zip field as a numeric type in the destination table.

      Although truncating the leading zeroes is not the desired result, consider the storage, indexing, and querying implications of a CHAR(5) field versus an INT. The conversion truncates the leading zeroes, but does not loose any data that cannot be easily recovered through proper formatting.

        Comment

        • benchpolo
          New Member
          • Sep 2007
          • 142
          #3
          But I need to see the leading zeroes in the table. For example, if I custom format the zip code as 08111 it should be 08111 also in SQL table.

            Comment

            • ck9663
              Recognized Expert Specialist
              • Jun 2007
              • 2878
              #4
              What "issue" are you having?

              ~~ CK

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32634
                  #5
                  I think you'll need to export the data as a string. This means to include quotes around it. Otherwise, data with only numeric characters in it is quite correctly interpreted as numeric. That's how it works.

                  I'm not certain that overriding that in SQL is impossible, but I strongly suggest that you work more naturally with your data where possible (IE. Store string data as strings rather than relying on any auto-conversions).

                    Comment

                    • benchpolo
                      New Member
                      • Sep 2007
                      • 142
                      #6
                      When I place the data onto an excel spreadsheet, the data looks lils 09999, but after importing to SQL 2005 tables the leading zero is ignored.

                        Comment

                        • NeoPa
                          Recognized Expert Moderator MVP
                          • Oct 2006
                          • 32634
                          #7
                          It's not a good idea to use Excel to manipulate CSV files I'm afraid. It's very difficult to get it to stop making unfounded assumptions about your data. Numeric data which is enclosed in quotes is nevertheless treated as numeric data, even though the quotes indicate it should be treated as textual.

                            Comment

                            • ck9663
                              Recognized Expert Specialist
                              • Jun 2007
                              • 2878
                              #8
                              True, Neo...

                              Bench, save your file as .CSV/.TXT and import that instead.

                              Good Luck!!!

                              ~~ CK

                                Comment

                                Previous template Next
                                Working...