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

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

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

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

The file contains fields separated by tabs and not commas
What does the (BN, BC) bit do?
Shouldn't the direction of slash be :
Try something like :
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

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

From what I can see is that the lines are terminated with "\r\n".

Andreas

Hi Andreas,

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

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

-Sashi

EDIT : not needed

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);

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

tats it then....that was the problem with my code...

Thanks again

-Sashi