Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2

    Imported CSV messing up formatting


    Hello all,

    I'm importing a pretty sizeable CSV file but when trying to search for a particular entry it returns nothing.

    Below is an excerpt of the information I'm importing:

    [CODE="CSV extract"]
    4921000000000000,0376,2598.16,nn
    4921000000000001,3851,267.66,nn
    4921000000000002,7508,4165.87,nn
    4921000000000003,2204,1296.48,nn
    4921000000000004,7184,6648.07,nn
    4921000000000005,2913,3614.73,nn
    4921000000000006,0644,4913.35,nn
    4921000000000007,0528,4259.35,nn
    4921000000000008,3683,3764.92,nn
    [/CODE]

    And below is the SQL command I'm using to import said CSV file:

    [CODE="SQL command"]
    load data local infile '/mnt/hgfs/VMware shared folder/customerEntries.csv' into table customerRecords
    fields terminated by ','
    lines terminated by 'nn'
    (CardNumber,PIN,Balance)
    [/CODE]

    But when I SELECT * FROM customerRecords; the formatting of the imported data makes it impossible to search any data. I have included a screenshot of outputted data below:



    Can anyone offer me a solution as to why this is happening. In the screenshot I actually added some data manually, the account number "4921000000000001" is all formatted correctly as I added it but the couple above it were imported via the CSV.

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    To add further information as well, I am getting truncated errors when importing.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    you've forgotten the real end of line terminator \r for linux or \r\n for windows.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    you've forgotten the real end of line terminator \r for linux or \r\n for windows.
    So I would need to replace the "nn" at the end of each line with "/r" for this to work successfully?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    Originally Posted by Mastah^
    So I would need to replace the "nn" at the end of each line with "/r" for this to work successfully?
    No, there is already a /r at the end of each line, otherwise it would be one continuous line. Don't know where the "nn"s come from but you don't need them
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    No, there is already a /r at the end of each line, otherwise it would be one continuous line. Don't know where the "nn"s come from but you don't need them
    It comes from a Python script I've made to create the initial CSV file, see below:

    [CODE="Python script"]
    import csv
    import random
    import decimal

    cardNumbers = []
    PIN = lambda: random.randint(0000,9999)
    Balance = lambda: random.uniform(0000,9999)

    cNStart =4921000000000000
    cNMax =4921999999999999
    terminator = "nn"
    c = csv.writer(open("customerEntries.csv","wb"))

    while cNStart != cNMax:
    #print cNStart, "%04d" % PIN(), "%.2f" % Balance()
    c.writerow([cNStart, "%04d" % PIN(), "%.2f" % Balance(), terminator])
    cardNumbers.append(cNStart)
    cNStart = cNStart + 1
    [/CODE]

    I don't know why I made the script to output "nn", I think I was trying to replicate a "\n" but it wasn't outputting at all.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    ok, well anyway just change the

    lines terminated by 'nn'

    to

    lines terminated by 'nn\r'

    if you are using linux

    or

    lines terminated by 'nn\r\n'

    if you are using windows, and it should work
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    ok, well anyway just change the

    lines terminated by 'nn'

    to

    lines terminated by 'nn\r'

    if you are using linux

    or

    lines terminated by 'nn\r\n'

    if you are using windows, and it should work
    Okay, so I am still having issues. I have amended my CSV creation script so there's nothing at the end of each line now, see below:

    [code="CSV file"]
    4921000000000000,3763,8263.09
    4921000000000001,1171,7853.31
    4921000000000002,1392,8585.47
    4921000000000003,0163,9018.42
    4921000000000004,7002,8869.69
    4921000000000005,5921,6340.37
    4921000000000006,1190,8092.93
    4921000000000007,6654,7583.59
    4921000000000008,3932,4367.70
    4921000000000009,4394,9878.22
    4921000000000010,8802,7032.94
    4921000000000011,9688,580.04
    4921000000000012,0784,7538.27
    4921000000000013,1839,8372.65
    4921000000000014,8530,5276.42[/code]

    And I am still getting loads of errors when I try and import the data:

  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    Do they now show correctly when you do

    select * from customerrecords;
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    Do they now show correctly when you do

    select * from customerrecords;
    Same output which is frustrating as I will be integrating the database into an IVR menu where user input will be the exact length of the Card Number.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    If you still have the same formatting problems, then you still haven't got the

    line terminated by

    right yet

    Is this linux or windows, and what is the load command you are now running?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    If you still have the same formatting problems, then you still haven't got the

    line terminated by

    right yet

    Is this linux or windows, and what is the load command you are now running?
    It's Linux I am running MySQL on and the command I am typing is as follows:

    [CODE="Command"]
    load data local infile '/mnt/hgfs/VMware shared folder/customerEntriesv2.csv' into table customerRecords fields terminated by ',' lines terminated by '\r' (CardNumber,PIN,Balance);[/code]
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    looks ok, maybe you need to look at the csv file in a hex editor to see if python is doining anything weird to the end of the lines.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    41
    Rep Power
    2
    Originally Posted by TonyF123
    looks ok, maybe you need to look at the csv file in a hex editor to see if python is doining anything weird to the end of the lines.
    I have done as you have suggested and this is a partial output of what the hex editor found from my .csv file:



    I've highlighted a dot that occurs before every 4 of each line. The hex editor shows it as a 0A. I personally have never used a hex editor, would this "0A" be significant in any way at all?
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    you have 2 'dots' 0D 0A

    so use \r\n as the terminator

    Comments on this post

    • SimonJM agrees : Rep by proxy :)
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo