CSV FILE CHANGING DATA - NUMBERS BEING CHANGED TO DATE FORMAT

%3CLINGO-SUB%20id%3D%22lingo-sub-332056%22%20slang%3D%22en-US%22%3ECSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332056%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20produce%20a%20csv%20file%20to%20import%20data%20into%20MYOB.%26nbsp%3B%20After%20I%20import%20the%20data%20required%20and%20save%20the%20csv%20file%2C%20when%20I%20re-open%20it%2C%20the%20account%20codes%20are%20being%20changed%20to%20a%20date%20format%20-%20the%20first%2015%20lines%20are%20correct%20then%20from%20line%2016%20to%20the%20end%2C%20the%20below%20formatting%20occurs.%26nbsp%3B%20How%20can%20I%20keep%20the%20original%20data%3A-%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%20border%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDATE%3C%2FTD%3E%3CTD%3EAC%20CODE%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ESHOULD%20BE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3E6-1702%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-1702%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3ECORRECT%20FORMAT%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3E6-1704%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-1704%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3ECORRECT%20FORMAT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3E6-1705%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-1705%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3ECORRECT%20FORMAT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3EJun-00%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-1900%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3ECHANGED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3EJun-00%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-2000%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3ECHANGED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3CTD%3EJun-01%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6-2101%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3ECHANGED%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20incite%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-332056%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1137023%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1137023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%20-%20I%20am%20facing%20the%20issue%20where%20I%20want%20to%20add%20text%20values%20of%20the%20format%20'MM%2FDD%2FYYYY'%20to%20be%20read%20as%20text.%20Certain%20values%20seem%20to%20be%20read%20as%20text%20automatically.%20Certain%20values%20are%20read%20as%20Date%20format%20'MM-DD-YYYY'%20by%20default.%20I%20faced%20this%20issue%20specifically%20on%20csv.%20I%20had%20faced%20this%20issue%20on%20xlsx%20as%20well%2C%20but%20there%20I%20tried%20Data%20--%26gt%3B%20Text%20to%20Columns%20--%26gt%3B%20(next.%20next)%20--%26gt%3B%20Text%2C%20and%20it%20worked.%20The%20issue%20persists%20on%20csv.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20this%20peculiar%20issue%20does%20not%20recur%20on%20my%20colleague's%20excel%20csv%20file.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20sharing%20the%20screenshot%20as%20well%20as%20sample%20csv%20file.%26nbsp%3B%3CBR%20%2F%3E8%2F11%2F2019%2C%26nbsp%3B8%2F12%2F2019%26nbsp%3Bget%20auto%20converted%20into%2008-11-2019%2C%2008-12-2019%3C%2FP%3E%3CP%3E8%2F13%2F2019%2C%208%2F14%2F2019%20get%20saved%20as%20is.%20(General%20format)%20-%20This%20is%20what%20is%20expected.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-333963%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333963%22%20slang%3D%22en-US%22%3E%3CP%3EWill%20try%20this%20tip.%26nbsp%3B%20Thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332763%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332763%22%20slang%3D%22en-US%22%3E%3CP%3EFormat%20the%20column%20in%20which%20the%20codes%20are%20as%20text%20and%20don't%20just%20paste%20but%20paste%20special%20values%20into%20the%20template%20sheet.%20Perhaps%20that%20helps%20Excel%20not%20to%20convert%20information%20that%20resembles%20a%20date%20into%20a%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332639%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332639%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20data%20is%20fine%20in%20the%20original%20documents%2C%20I%20select%20the%20column%20data%20required%2C%20copy%20and%20paste%20into%20the%20the%20csv%20template%20(not%20an%20actual%20template%20documents%2C%20just%20a%20.csv%20file%20I%20use%20with%20specific%20column%20order%20for%20importing%20into%20MYOB).%26nbsp%3B%20The%20data%20looks%20fine%2C%20then%20I%20save%20and%20close.%26nbsp%3B%20Its%20when%20I%20re-open%20the%20document%20it%20shows%20incorrectly.%26nbsp%3B%20I%20am%20just%20confused%20as%20it%20is%20only%20one%20column%20that%20is%20effected%20and%20only%20half%20of%20the%20column.%26nbsp%3B%20I%20read%20your%20article%20and%20will%20follow%20your%20steps%20next%20time%20I%20have%20to%20do%20the%20import.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332216%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332216%22%20slang%3D%22en-US%22%3EIs%20the%20data%20already%20wrong%20after%20the%20import%2C%20or%20only%20when%20you%20save%20the%20data%20to%20a%20new%20CSV%3F%20If%20the%20latter%2C%20you%20should%20change%20the%20way%20you%20open%20the%20CSV%20file%20using%20the%20tips%20I%20wrote%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Farticles%2Fimporttext.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2Farticles%2Fimporttext.asp%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1139320%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20FILE%20CHANGING%20DATA%20-%20NUMBERS%20BEING%20CHANGED%20TO%20DATE%20FORMAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1139320%22%20slang%3D%22en-US%22%3ECan%20you%20please%20describe%20your%20*exact*%20steps%20one%20by%20one%3F%20It%20is%20unclear%20to%20me%20which%20steps%20you%20take%20exactly%2C%20and%20also%20what%20format%20your%20original%20documents%20are%20in%20(Excel%20file%2C%20other%20source%3F).%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Please help,

 

I need to produce a csv file to import data into MYOB.  After I import the data required and save the csv file, when I re-open it, the account codes are being changed to a date format - the first 15 lines are correct then from line 16 to the end, the below formatting occurs.  How can I keep the original data:-

DATEAC CODESHOULD BE 
1/31/20196-17026-1702

CORRECT FORMAT

1/31/20196-17046-1704CORRECT FORMAT
1/31/20196-17056-1705CORRECT FORMAT
1/31/2019Jun-006-1900CHANGED
1/31/2019Jun-006-2000CHANGED
1/31/2019Jun-016-2101CHANGED

 

Any incite would be greatly appreciated.

 

6 Replies
Highlighted
Is the data already wrong after the import, or only when you save the data to a new CSV? If the latter, you should change the way you open the CSV file using the tips I wrote here: https://jkp-ads.com/articles/importtext.asp
Highlighted

The data is fine in the original documents, I select the column data required, copy and paste into the the csv template (not an actual template documents, just a .csv file I use with specific column order for importing into MYOB).  The data looks fine, then I save and close.  Its when I re-open the document it shows incorrectly.  I am just confused as it is only one column that is effected and only half of the column.  I read your article and will follow your steps next time I have to do the import.

Highlighted

Format the column in which the codes are as text and don't just paste but paste special values into the template sheet. Perhaps that helps Excel not to convert information that resembles a date into a date.

Highlighted

Will try this tip.  Thank you for your help.

Highlighted

@Jan Karel Pieterse  - I am facing the issue where I want to add text values of the format 'MM/DD/YYYY' to be read as text. Certain values seem to be read as text automatically. Certain values are read as Date format 'MM-DD-YYYY' by default. I faced this issue specifically on csv. I had faced this issue on xlsx as well, but there I tried Data --> Text to Columns --> (next. next) --> Text, and it worked. The issue persists on csv. 

And this peculiar issue does not recur on my colleague's excel csv file.  

I am sharing the screenshot as well as sample csv file. 
8/11/2019, 8/12/2019 get auto converted into 08-11-2019, 08-12-2019

8/13/2019, 8/14/2019 get saved as is. (General format) - This is what is expected. 

Highlighted
Can you please describe your *exact* steps one by one? It is unclear to me which steps you take exactly, and also what format your original documents are in (Excel file, other source?).