007007970 export to excel it change into 7007970

lihai

My tbody has some data begin with 0,like 007007970,00113, 01204.I make the table export to excel.In the excel the data change into 7007970,113,1204.How to slove it?
The table:
007007970 00113 01204
The EXCEL:
7007970 113 1204

allan

Looking in the raw exported file, is the full number there? If so, its an Excel formatter that is hidden the leading zeros. If not, please link to a test case.

Allan

lihai

hello allan.When I use the tabletools'copy to copy the table,it's the full number.So in the raw exported file,that is the full number there.I agree with that its an Excel formatter.But how to slove this problem?

allan

Change the formatter in Excel? Its not something that can be controlled from the file since its just a CSV file.

Allan

lihai

Change the formatter in Excel?I don't think that is a good idea.After change the formatter that the zeros also can't back again.I think that is a bug in the tabletools.I hope it will be worked out someday.

allan

If the data is in the file, how can it be a bug in TableTools? It just outputs a CSV file.

In Excel, select the cells and change the cell format to text and you should see the full text.

Allan

lihai

Because the tabletoos can't set the cell format.I hope it can also change the csv cell format when the table outputs.The zeros is missing in Excel.When I change the cell format,the zeros can't back again,the zeros also is missing!

allan

Can you please link me to a test case so I can see the outputted file and the source data.

Allan

lihai

I just work in my localhost,don't have a link.You can change some table's data in the \DataTables-1.9.4\extras\TableTools\defaults.html.Like:
[code]


Rendering engine
Browser
Platform(s)
Engine version
CSS grade


[/code]
into
[code]


00101
0201
00000001
Engine version
CSS grade


[/code]
Then use the tabletools to export to excel.That you will see what I say.

allan

The exported file looks like this:

[code]
00101,0201,00000001,Engine version,CSS grade
[/code]

without exporting an xls file, TableTools can't do anything else. Yes, exporting an xls file would be good, but it is not trivial.

Allan

jp_noronha

hi

forgive my intrusion but don't blame tabletools, if you search you will find the answer. The problem lies within excel, when importing text files it uses the first row to check what type a field can be, assuming to be a number if it's possible to convert.

Ammar

hey allan,

try this:
1 - Open your exported .csv file in notepad or any desired text editor.
2 - Copy the entire content.
3 - Open Excel Sheet and change the format of entire column A to "text".
4 - Paste your content and then use "Text to Column" option in excel.
5 - Choose Delimited option, provide comma as separator.
6 - In the next screen change the format of your desired fields as per you requirements and you are done.

hope this would help.

jshuusko

One of the easiest solutions for this problem can be found from here.

http://excel.tips.net/T002752_Pasting_Leading_Zeroes.html

In short what I do is that I paste the data to excel, change the erroneous columns' format to text and paste again and leading zeros will be there.

Another option would be to open the file form Excel's File Open Dialog. It should then open the Text Import Wizard where text columns could be marked as text instead of general. I think sometimes I've had to change the file format to .txt to prevent some versions of Excel to automatically open the .csv file.

-jari