Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Unanswered: Some letters are missing in the uploaded database??

    Hi all,

    I am trying to upload a CSV file using the LOAD DATA INFILE command. The data is getting uploaded, but when I see the data, in the First column
    1) Some of the rows have their first letter missing
    2)Some of the rows have 2 or more than two letters missing
    3) only one row has the full name without missing any thing

    Can anyone please tell me as to what the reason could be??

    Thanks,
    Sashi

  2. #2
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    17
    Without knowing anything about your file and table I only can post some hints :


    • check that you give the correct charset to load data. Characters tend to disappear for example if you load a file using utf-8 which actually is encoded in a 8-Bit Characterset like iso-8859-1.
    • check line and field terminators and escape characters.



    Andreas

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It might help if you gave us a little more info ie
    • which line went in perfectly - was it the 1st line?
    • the full command you are using to load the data
    • some info on the file, assuming you're on unix run the following command to show us what's actually in your file. The head just passes the first line to the command od which shows all the funny characters that are present :
      head -1 your_file.csv | od -c

  4. #4
    Join Date
    Feb 2009
    Posts
    12
    Hi,

    Sorry about that. I should have given you more data. This is the complete command I am using.

    LOAD DATA INFILE 'C:/Users/Sashikanth/desktop/buildings.csv' INTO TABLE sashi4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n' (BN, BC);

    When I upload the data in the form of .txt file, with a comma between the first column and second column, the 3rd row from the bottom goes in correctly. This particular row has a building code which is not entirely numeric. It has an alphabet in the code. I do not know if that is causing some issue. I have attached the .txt file to this post.

    Please do let me know if you need additional data

    -Sashi
    Attached Files Attached Files

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The file contains fields separated by tabs and not commas
    What does the (BN, BC) bit do?
    Shouldn't the direction of slash be :
    Code:
    LINES TERMINATED BY '\n'
    Try something like :
    Code:
    LOAD DATA INFILE 'C:/Users/Sashikanth/desktop/buildings.csv' 
    INTO TABLE sashi4 
    FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
    You might find the LINES TERMINATED BY '\n' is optional as I think it defaults to this. Sorry can't be more exact but I don't use this command normally so I'm winging it

  6. #6
    Join Date
    Feb 2009
    Posts
    12
    The (BN, BC) tells that, the columns in the .csv file have to be uploaded into the table with the column names BN and BC.

    With regard to the '/n'....I have actually used '\n' but has mistyped as '/n' in the query box...sorry about the confusion...

    I have used the command given by you. Now it is giving me a new error.
    DUPLICATE ENTRY ' ' FOR KEY 1

  7. #7
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    17
    From what I can see is that the lines are terminated with "\r\n".

    Andreas

  8. #8
    Join Date
    Feb 2009
    Posts
    12
    Hi Andreas,

    Worked perfectly...Thank you so much...

    err...would you mind telling me what difference did '\r\n' make??

    -Sashi

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    EDIT : not needed
    Last edited by mike_bike_kite; 02-06-09 at 16:02.

  10. #10
    Join Date
    Feb 2009
    Posts
    12
    This is the code I have used to create the table

    CREATE TABLE Buildings ( BN varchar (35) NOT NULL, BC varchar (50) NOT NULL Primary Key);

  11. #11
    Join Date
    Aug 2002
    Location
    Germany
    Posts
    17
    Your're welcome :-)

    \n is just the newline (think of an old typewriter turning the paper feed up one line)

    \r is carriage return (moves the printing head back to left start position)

    Well, back from mechanics, windows files more often have the both control characters for a line feed, which is 2 bytes, whereas unix traditionally uses the newline only for line termination - 1 byte.

    The the one extra byte messed up the file structure.

    Andreas

  12. #12
    Join Date
    Feb 2009
    Posts
    12
    tats it then....that was the problem with my code...

    Thanks again

    -Sashi

Posting Permissions

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