SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hung up on 112,249

    When I try to import my csv file in MySQL-Front, it appears to get hung up on every integer - or perhaps it's just every integer in the first integer field.

    When it cites an error, it only lists the numerals after the comma. For example, it says 249 for this sample from my csv file:

    "112,249","al","Calhoun","county",

    I inserted 112,249 direcly with phpMyAdmin, and it did just the opposite - it only displays 112. I though perhaps it would automatically insert the comma if I just inserted the six numerals, but it doesn't.

    Could someone tell me all the table structure values you would use, assuming this field is not an index or key?

    I prsently have...

    int(10) | | Yes | NULL

    I deleted the length, but it inserted 11 by default.

    Any tips?

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes... get rid of the comma. Why do you need it there anyway?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or alternatively in a very nasty botch job type effort put the field as text...
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by geosite
    I prsently have...

    int(10) | | Yes | NULL

    I deleted the length, but it inserted 11 by default.

    Any tips?
    yes, ignore the numbers in parentheses, that's just phpmyadmin trying to be smart

    an integer is an integer is an integer, it always occupies 4 bytes and it always hold numbers between -2147483648 to 2147483647 (except when it thinks the numbers are unsigned, then it's 0 to 4294967295)

    and an integer has no commas, so you have to remove those when inserting or loading data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, ignore the numbers in parentheses, that's just phpmyadmin trying to be smart

    an integer is an integer is an integer, it always occupies 4 bytes and it always hold numbers between -2147483648 to 2147483647 (except when it thinks the numbers are unsigned, then it's 0 to 4294967295)

    and an integer has no commas, so you have to remove those when inserting or loading data
    OK, I think I understand. You're saying leave the "length" column empty, and just ignore any value phpMyAdmin inserts (like (11)), right? And I should remove the commas before I insert the data. And if I do my homework, I assume I'll find a way to make commas appear for display purposes when people view my table.

    One more trivial question. I can easily remove the commas by changing my numeric columns from "numeral" format to "text" format on my spreadsheet. However, I'd like to retain the numeral format, as I use my spreadsheet to manipulate and study the data.

    Though I can easily change from numeral format to text format, then change it back to numeral format after I SAVE AS csv, it gets a little harder when I have many columns to deal with. It isn't a big deal, but I just wondered if you might know of some trick that eliminates the commas when you hit the SAVE AS key.

    Thanks!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why not leave the commas in your so-called numeric cells, but instead of saving it as a comma-separated file, save it as a tab-delimited file, then use search/replace to remove all remaining commas, then use search/replace to change the tabs to commas...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it's Microsoft's sub-Excel spreadsheet, whatever it's called. When I bought my computer a couple years ago, I remember being given the option of purchasing an office suite with Excel or the cheaper version with the other spreadsheet. I didn't even know what a spreadsheet was at the time, so I chose the cheaper version.

    I'm still not aware of all the differences, but I have a hunch Excel has a few features that could be useful. I also have OpenOffice.org's spreadsheet.

    Quote Originally Posted by r937
    why not leave the commas in your so-called numeric cells, but instead of saving it as a comma-separated file, save it as a tab-delimited file, then use search/replace to remove all remaining commas, then use search/replace to change the tabs to commas...
    Ah, that sounds like just my style! Thanks.

  8. #8
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you talking about Excel?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •