How to avoid automatically change data type in .CSV file

Question

I have created an application in java language and I have used MySQL as a DBMS. I have a button in this application which export my data to .CSV file.

The problem is that when I open .CSV file, it automatically change the data. For example, 00001 will becomes 1.

How can I avoid automatically change data type in excel?

`Import` (`Data` ► `Get External Data` ► `From Text`) instead of `Open`; and pre-format the column as text — Ron Rosenfeld, Aug 17 '16 at 11:39
Yeah, that Import Data -> From Text Menu is your friend. That has nothing to do with java or sql. — Matze, Aug 17 '16 at 11:49
thank you, if we import data as text we can specify data type — hoger qassim, Aug 17 '16 at 18:04
Welcome to Stack Overflow! Can you please have a little bit more detailed information in the content with your effort to solve the problem? — Enamul Hassan, Aug 19 '16 at 16:53

score 1 · Accepted Answer · answered Aug 17 '16 at 11:40

If you open the *.csv directly in Excel, then all columns will be read as type general (means there is an automated guessing for the value type).

If you import the file as text into the current sheet you can specify the value delimiter and also the type of each column.

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

May be this is related. In that case the solution would be to save value as

"=""00001"""

This will be interpretered as text by excel.

edited May 23 '17 at 10:28

Community

1
1

answered Aug 17 '16 at 11:44

PKey

3,715
1
14
39

score 0 · Answer 3 · answered Aug 17 '16 at 11:40

0

Have you tried prefixing it with a single quote?

Example: '00001

It's just a guess based on how that value would need to be typed into Excel for it to treat it as a string and not to convert the value into a numeric 1.

answered Aug 17 '16 at 11:40

aresti

1
2

score 0 · Answer 4 · answered Aug 17 '16 at 11:46

Another way that might work: when creating the csv, what value are you writing? Are you writing 0001 (so, as number); or "0001"?

You see, when this column is seen as number ... then it might be reasonable that you get 1 for 00001.

So, tried saving that the values for that column as "00001"?

How to avoid automatically change data type in .CSV file

4 Answers4