not working as expected 2

[TipGiver]

Hi,
i'm new to SQL and generally just started 'dealing' with databases.
All i need is to insert data from the disk to a table.

The Text File:

30434 menios
30567 dimitris
30666 skatas
30123 testing


SQL syntax:

mysql> load data local infile 'c:\\sub.txt' into table m;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from m;
+-------+-----------+
| am | name |
+-------+-----------+
|434 | menios
|30567 | dimitris
|666 | skatas
| 30123 | testing |
+-------+-----------+
4 rows in set (0.00 sec)

Why is that happening?
- my PC: MySQL last version (5. ..), win32
- It works fine under linux!

'Describe' of m table
mysql> describe m
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| am | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Tnx

 

[KarveR]

what exactly isnt working ? seems like its all there innit ?

*unless its possibly looking odd when displayed, you may need ot add a bit to the load data command.

load data infile 'c:\\sub.txt' fields terminated by '\t' lines terminated by '\n' into table m;

*note I'm guessing at tab seperated here (\t).
also do you have huge amounts of whitespace in the file ?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TipGiver]

The text file has 30434 and the data base 434. The 30 infront of it is missing.

I have tried using as delimiter the ; and , but only the TAB works. The space between numbers and strings is a single TAB.

The lines are delimited by one ENTER. Except from the end; meaning that before i save the I-beam cursor is next to "testing".


I'll try yours,
Tnx

 

[TipGiver]

?? Although the missing '30', take a look at this:

mysql> select * from m;
+-------+-----------+
| am | name |
+-------+-----------+
|434 | menios
|30567 | dimitris
|666 | skatas
| 30123 | testing |
+-------+-----------+
4 rows in set (0.00 sec)

mysql> select * from m where am=30434
-> ;
+-------+---------+
| am | name |
+-------+---------+
|30434 | menios
+-------+---------+
1 row in set (0.02 sec)

 

[KarveR]

theres something in your text file making a mess of the import, new lines before data perhaps ?

If you get a decent text editor and show hidden characters, newlines and spaces you should be able to figure out whats breaking it.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TipGiver]

The text file hass only these:

number TAB string ENTER
number TAB string ENTER
number TAB string ENTER
number TAB string

 

[hoinz]

Hi,

I think that the two select s given on 2 Nov 05 17:07 leave two possible explanations:

1) There are some hidden, nonprintable characters in your table. They may or may not be caused by erroneous characters in your text file, as KarveR suggested.

2) The settings of your PC or of your query tool are a bit strange. Reboot time?

hex() function might help to find out whether the first guess is correct:
select am, hex(name) from m;

hope this helps

 

[TipGiver]

hi hoinz,

I have 30434 (am) with 'menios' (name).

In the table (stored in) the hex(name)
gives: 6D656E696F730D

WHEREAS

the: select hex('menios');
gives: 6D656E696F73


So there ARE some hidden chars! Should i make some changes from the administrative tools, Data Sources, ODBC connector?


Thank you + KarveR

 

[hoinz]

yes,
0D is such a hidden character, known as carriage return.
Probably it is in your text file, what you called ENTER.
It seems you have to re-think your load procedure.
I am not an expert for this, but one possible source of problems comes to my mind:
A mixup of Windows and Unix/Linux formats perhaps?

regards

 

[KarveR]

how are you creating the file? can you specify unique delimiters at creation time, i.e number | name ;

this way you can convince mysql to ignore the hidden chars and use only those suplied - granted they'll make your data look ugly when selected though, but this can be fixed after the import using
update table set fieldname = TRIM(fieldname);

more fun and games from the been there seen it argued with it lots and lots and finally won team.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TipGiver]

hoinz you confused me!

OD is at the end of every name but the last. It is not shown. Everything is ok till now. The problem is that when i do: select * from m; it shows:

+-------+-----------+
| am | name |
+-------+-----------+
|434 | menios
|30567 | dimitris
|666 | skatas
| 30123 | testing |
+-------+-----------+

If i do: select * from m where am=30434;
it shows the 1st line.

The problem is at displaying the data. The data are saved corectly to the database table m, by the txt file.

 

[KarveR]

something30434 is stored to the db, its however you opened teh stream to write the file that is giving you jip.

if you manually recreate the file using notepad, do you get the same issue?

i.e delete from m;

create using notepad a file carefully.

load notepad_file into .....

select * from m;

what do you get now?



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[hoinz]

What is confusing here?
Imho opinion your data are not saved correctly.
I am quite sure that you have to get rid of those 0D;
when you display a 0D, it will move the cursor to the beginning of the line (that's why it is called carriage return), and some of your data that already has been displayed, will be overwritten, you will not see it anymore.
The reason why it does not happen in all lines must be the lenght of the names: skatas and menios are shorter than the others.

The best way to get rid of it should be during load.
But TRIM(), as suggested by KarveR, should also work.
just try this:
select am, TRIM(name) from m;

regards

 

[TipGiver]

The output result now is:

mysql> select am,TRIM(name) from m;
+-------+------------+
| am | TRIM(name) |
+-------+------------+
|34 | menios
|0567 | dimitris
|66 | skatas
| 30123 | testing |
+-------+------------+
4 rows in set (0.00 sec)

 

[hoinz]

ok, it seems we misunderstood what TRIM() does, sorry.
But I stll think you should get rid of the 0D, before or during load.

 

[TipGiver]

STRANGE:

I created a new txt file and a new table:

text file: It maybe looking not good here.
menios 1 e 1985-11-20
dimitris 2 e 1973-11-11
testing 3 a 2000-01-01
testing2 4 b 2005-02-02


MySQL command line:
mysql> load data local infile 'c:\\sub1.txt' into table test;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;
+----------+------+----------+------------+
| name | am | semester | bod |
+----------+------+----------+------------+
| menios | 1 | e | 1985-11-20 |
| dimitris | 2 | e | 1973-11-11 |
| testing | 3 | a | 2000-01-01 |
| testing2 | 4 | b | 2005-02-02 |
+----------+------+----------+------------+
4 rows in set (0.00 sec)


This i can think of now is that in windows i should not start with numerical data in txt files (that will be imported in the database).

 

[KarveR]

its to do with how you open the file to write, I've seen similar things under linux but they're hard to explain and harder to cure.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[danomac]

This likely is because the text files are encoded differently in linux than windows. If you don't specifically specify Windows encoding, it assumes linux encoding (which is just "\n".)

Windows uses "\r\n" for a new line in text files, so if you did something like:

mysql> load data local infile 'c:\\sub.txt' into table m lines terminated by '\r\n' STARTING BY '';

it will probably import correctly.

 

[KarveR]

its not going wrong at the newlines, its going wrong at the very start of the first data.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[hoinz]

Hi,

I disagree with KarveR here;
I think the problem is at the end of the lines, with this hex 0D, or \r, or ENTER, or whatever you call it.
In my posting from 3 Nov 05 9:47 I tried to explain why the problem seems to be at the beginning of the lines.
I have seen similar things before when Windows and Unix formats had been mixed up.

And did anybody notice in TipGiver's postings that the ouput of the selects does not show a '|' at the end of the data lines, except for the line with 'testing'?
Guess why!

regards