Excel to .txt file - number formatting issues

%3CLINGO-SUB%20id%3D%22lingo-sub-1569636%22%20slang%3D%22en-US%22%3EExcel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569636%22%20slang%3D%22en-US%22%3E%3CP%3EHoping%20someone%20can%20help%20here%20-%20I'm%20at%20my%20wits%20end.%3C%2FP%3E%3CP%3EI%20have%20data%20from%20an%20excel%20file%20that%20I%20have%20saved%20as%20a%20.txt%20file%20that%20I'm%20importing%20into%20MYOB.%26nbsp%3B%20This%20data%20includes%20references%20to%20account%20codes%20-%205-0105%2C%204-5100%20%26amp%3B%201-1410.%26nbsp%3B%20When%20I%20view%20the%20data%20in%20the%20text%20file%20these%20values%20are%20correct.%26nbsp%3B%20After%20the%20file%20is%20imported%20the%20error%20log%20shows%20that%20the%20values%20have%20been%20changed%20to%201488%2C%201314%20%26amp%3B%201585.%3C%2FP%3E%3CP%3EI've%20attempted%20the%20following%20solutions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B-%20changing%20format%20of%20the%20whole%20sheet%20from%20General%2C%20to%20Number%20and%20to%20Text%20(some%20of%20the%20test%20sheets%20have%20preserved%20the%20format%20of%205-0105%20and%201-1410%20account%20numbers%20but%20the%204-5100%20is%20always%20changed)%3C%2FP%3E%3CP%3E-%20using%20text%20import%20wizard%20to%20bring%20in%20text%20data%20to%20new%20Excel%20sheet%2C%20selecting%20delimiters%2C%20changing%20the%20account%20column%20formatting%20to%20both%20Text%20and%20General%3C%2FP%3E%3CP%3E-%20rewriting%20data%20in%20individual%20cells%20and%20then%20tabbing%20out%20(rather%20than%20enter)%3C%2FP%3E%3CP%3E-%20saving%20as%20.csv%20file%20-%20all%20of%20the%20same%20issues%3C%2FP%3E%3CP%3E-%20MYOB%20support%20tried%20similar%20solutions%20without%20success.%3C%2FP%3E%3CP%3ENO%20JOY.%26nbsp%3B%20HOURS%20LOST.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20someone%20can%20assist!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1569636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570056%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570056%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750877%22%20target%3D%22_blank%22%3E%40bree_anastasi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20share%20a%20sample%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572089%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572089%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20just%20added%20-%20thanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572091%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750877%22%20target%3D%22_blank%22%3E%40bree_anastasi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572219%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572219%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750877%22%20target%3D%22_blank%22%3E%40bree_anastasi%3C%2FA%3E%3CBR%20%2F%3Eit%20seems%20this%20is%20not%20an%20Excel%20issue.%3CBR%20%2F%3EThere%20is%20not%20problem%20with%20Excel%20exporting%20the%20data.%3CBR%20%2F%3EFrom%20the%20error%20log%2C%20I%20understood%20some%20values%20are%20supposed%20to%20be%20numbers%20and%20your%20program%20is%20not%20accepting%20them%20because%20they%20are%20text.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20ask%20you%20to%20export%20data%20from%20the%20program%20then%20show%20it%20in%20excel%20(upload%20it%20here%20pls)%2C%20maybe%20it%20gives%20us%20a%20clue%20what%20is%20going%20on.%3CBR%20%2F%3Eregards%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572401%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20uploaded%20a%20list%20as%20exported%20out%20of%20MYOB%20into%20Text%20Document%20and%20the%20contents%20copied%20and%20pasted%20into%20the%20excel%20spreadsheet.%26nbsp%3B%20Hope%20you%20can%20see%20something%20I%20can't!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572440%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572440%22%20slang%3D%22en-US%22%3EI%20do%20not%20know%20how%20MYOB%20works%2C%20what%20does%20the%205%20and%20the%200105%20refer%20to.%3CBR%20%2F%3EI%20am%20sorry%20I%20do%20not%20know%20what%20is%20the%20problem%2C%20I%20leave%20it%20to%20somebody%20else%20to%20answer%2C.%20have%20a%20nice%20day%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572955%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20to%20.txt%20file%20-%20number%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572955%22%20slang%3D%22de-DE%22%3ESince%20you%20have%20already%20tried%20many%20things%2C%20I%20can%20only%20point%20out%20something%20simple.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20What%20the%20Excel%20file%20unsaved%20before%20you%20made%20it%20a%20txt%20file%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Especially%20when%20importing%20SAP%20Excel%20files%20this%20error%20occurs%20(sometimes%20...%20not%20always)%2C%20so%20it%20would%20be%20good%20if%20you%20save%20the%20Excel%20file%20in%20your%20PC%20beforehand%20and%20then%20convert%20it%20...%20according%20to%20the%20idea%2C%20it%20should%20not%20happen%20then%20...%20is%20just%20a%20little%20hint.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
New Contributor

Hoping someone can help here - I'm at my wits end.

I have data from an excel file that I have saved as a .txt file that I'm importing into MYOB.  This data includes references to account codes - 5-0105, 4-5100 & 1-1410.  When I view the data in the text file these values are correct.  After the file is imported the error log shows that the values have been changed to 1488, 1314 & 1585.

I've attempted the following solutions:

 - changing format of the whole sheet from General, to Number and to Text (some of the test sheets have preserved the format of 5-0105 and 1-1410 account numbers but the 4-5100 is always changed)

- using text import wizard to bring in text data to new Excel sheet, selecting delimiters, changing the account column formatting to both Text and General

- rewriting data in individual cells and then tabbing out (rather than enter)

- saving as .csv file - all of the same issues

- MYOB support tried similar solutions without success.

NO JOY.  HOURS LOST.

 

Hoping someone can assist!

 

 

7 Replies

@bree_anastasi 

can you share a sample file

Have just added - thanks @Ramiz_Assaf 

@bree_anastasi
it seems this is not an Excel issue.
There is not problem with Excel exporting the data.
From the error log, I understood some values are supposed to be numbers and your program is not accepting them because they are text.

I would ask you to export data from the program then show it in excel (upload it here pls), maybe it gives us a clue what is going on.
regards

Thanks @Ramiz_Assaf 

I've uploaded a list as exported out of MYOB into Text Document and the contents copied and pasted into the excel spreadsheet.  Hope you can see something I can't!!

I do not know how MYOB works, what does the 5 and the 0105 refer to.
I am sorry I do not know what is the problem, I leave it to somebody else to answer,. have a nice day
Since you have already tried many things, I can only point out something simple.

Was the Excel file unsaved before you made it a txt file?

Especially when importing SAP Excel files this error occurs (sometimes ... not always), so it would be good if you save the Excel file in your PC beforehand and then convert it ... according to the idea, it should not then happen ... is just a little hint.


Nikolino
I know I don't know anything (Socrates)