SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Import CSV File - Help

    Hi Guys,

    I have over 25 million records to import to a table. How ever the data is set out as the following:

    WA2 8PR, Unit 3, Winstanly Ind Estate, Long Lane, Warrington, Cheshire,
    SK11 8EQ, 1, Westbond Street,, Macclesfield, Cheshire
    WN7 2HX 1, Wensleydale Road,, Leigh, Lancashire
    I'm currently uploading the 1.88GB csv file to the server and going to be using the LOAD DATA query to do it, how ever what do i put to as Columns enclosed with and Columns escaped with? Also do i put Lines terminated with as /n ?

    Thanks
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,815
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You don't have your columns enclosed in anything and from the sample provided there is no way to tell what character you have used to identify commas in the actual data.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Columns are separated by commas, is there a way of be importing the CSV with just the commas?

    If not is there any kind of program i can use that will convert the CSV file so i can easily import the CSV file to MySQL?

    Any help would be great.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  4. #4
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any kind of program i can use that will do what it needs to do to make the the data import to a MySQL Table?

    If theres anything you can think of that will do what i need it to do that would be great.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,815
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    An example to get you started. The following has columns enclosed in "" with both address lines in the one column. Because the columns are wrapped in quotes the comma in the data doesn't need to be escaped.

    "WA2 8PR","Unit 3","Winstanly Ind Estate, Long Lane", "Warrington"," Cheshire"
    "SK11 8EQ","1","Westbond Street", "Macclesfield", "Cheshire"
    "WN7 2HX","1","Wensleydale Road", "Leigh","Lancashire"


    This is the format that Excel expects CSV files to be in so that it can load the data correctly. The alternative if you don't want to wrap all the column content would be to escape any commas in the data itself - otherwise where the column is supposed to contain a comma the content would be jumbled.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Lines terminated by \n. Optionally enclose with ". Separated by ,. As felgall has brought up, you may run into a problem since it doesn't seem you have enclosures. Double check after import.


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
  •