Excel import problem

[QatQat]

Hi There,

I am trying to import a large excel spreadsheet into a mysql 5 table. All fields import correctly but only one continues to give "null" data after import.

It is a column set to Text on excel that contains data with the following format:

06/123456

Two digits for the year and a number up to six digits.

The relevant column in my mysql table is a varchar 10.

I suspect that the "/" creates problems.
Must I replace all "/" in my excel with "//" before importing?


QatQat


Life is what happens when you are making other plans.

 

[QatQat]

Tried that but no luck!

Any suggestion?

QaTQat

Life is what happens when you are making other plans.

 

[TonyGroves]

There should be no problem with / characters. What format is the input file in? MySQL only recognises delimiter-separated text files.

 

[QatQat]

I tried to export excel document to tab separated text file and import it but the above column does not get imported correctly.
I tried ODBC import feature on SQLYog and same problem.

WHat else can i try?

QaTQat

Life is what happens when you are making other plans.

 

[TonyGroves]

Maybe you could post the offending portion of the file so we can see what the data looks like.

 

[QatQat]

Hi Tonygroves,

the whole file gets imported correctly, with the exception of one column.
the field in object is called filenumber and contains either records in the format

100861


straight six digits or

06/xxxxxx

two digits for the year, a "/" and six digits.

In this case I created a column varchar 10 just in case there are longer data in the filenumber column.

Every record with the filenumber similar to the one without "/" sign is imported correctly.

Every record where the filenumber column includes a "/" is imported with a NULL in the filenumber column.

I think that my problem may be in the excel column which I have converted in all possible ways (text, generic, custom) with no luck.
If I export the excel file to text, the column seems fine and contains all values, whether only 6 digits or with th e "/" sign; when imported using load data infile, all null values will appear in the above mentioned field.

So there are no offending lines; I do not get any error when importing into mysql.


QatQat

Life is what happens when you are making other plans.