Saving file with 15 digit number without converting last digits to 0

[ellak123]

Hi there,

I have an app that generates .csv files with 15 digit numbers that I need to upload to a portal. The .csv is fine when opened in excel, the number displays as e.g. 8.1122E+14 instead of 811223344556677 which is not ideal but fine.

However sometimes I need to combine files rather than uploading 5 different files so I copy all of the numbers into 1 file. They copy in fine and I save. Then after the save the numbers change to 811223000000000. This happens even if I don't actually edit the number so I must assume it is a problem when the file is saved.

I have tried using an ' in front of the number but the portal picks this up and does not allow non numerical entries.

I could edit the .csv in notepad however it is not always me editting the files and other people will not be able to facilitate this.

Is there anyway to solve this problem?

 

[Joe4]

One trick is to change the extension of the file from "CSV" to "TXT". Then should Excel shouldn't make its automated assumption that those are numbers, and you should be able to control the format of each field, and set it to text. Then it will appear as the long string instead of a number in scientific location. So you should then be able to combine and save the files, and then you can change the extension back to "CSV".

Alternatively, you could just select your range of values to fix (one column at a time, if multiple), and run this little macro on them:

Sub FixFormat()

    Selection.NumberFormat = "@"

    Selection.TextToColumns Destination:=Selection(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

End Sub

That should fix them that when you re-save the CSV file, they should be how you need them.

 

[ellak123]

One trick is to change the extension of the file from "CSV" to "TXT". Then should Excel shouldn't make its automated assumption that those are numbers, and you should be able to control the format of each field, and set it to text. Then it will appear as the long string instead of a number in scientific location. So you should then be able to combine and save the files, and then you can change the extension back to "CSV".

Alternatively, you could just select your range of values to fix (one column at a time, if multiple), and run this little macro on them:

Sub FixFormat()

    Selection.NumberFormat = "@"

    Selection.TextToColumns Destination:=Selection(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

End Sub

That should fix them that when you re-save the CSV file, they should be how you need them.

Yeah that works alright, similar idea to editting in notepad. I was wondering if it's possible to do this within the file so that others not used to manipulating files could just go in and change the numbers or paste in more

 

[Joe4]

The issue is that when you open CSV files directly in Excel, it performs automated conversions on the data, based on what it thinks the data should be. It treats columns where entries are all numbers, as numbers, and not strings. The only way that I know of to change that behavior is to not open the CSV directly in Excel. If you do, you need to fix it "after the fact", like I show with the code I provided.

So, between the two of us, we have provided 3 different options:
1. Change the extension of the file before opening in Excel so Excel doesn't perform its automated conversions that it does for Excel files.
2. Fix it after the fact, either manually, or with a macro like I provided.
3. Edit the file in a Text Editor program instead of Excel. Note that there are some robust 3rd party Text Editors (like NotePad++ and others) that have expanded features beyond NotePad and WordPad that might make editing and combining these files much easier in those programs than Excel.

IMHO, I love Excel, but I think that one of the biggest things that Excel/Microsoft gets wrong is that they set up Excel to be the default program to open CSV files. I think this is a big mistake, since Excel performs automated conversions on the data that you are experiencing. If you want to truly view the contents of the CSV file, as they truly exist, you need to view it in something other than Excel. For that reason, one of the first things I do when I get a new computer is to change the default program to open CSV files on my computer from Excel to NotePad.

 

[ellak123]

Yes, I have been looking everywhere to find a way to do this but consensus seems to be this is just a limitation of Excel. I will probably make use of your macro to help the process along.

I appreciate you interacting with the question anyway, Thanks Joe4.

 

[Joe4]

You are welcome.

Yes, I don't think there is any way to override that default Excel behavior.