dannymccann
Member
Registered: 9th Aug 06
Location: Doddington, Lincolnshire
User status: Offline
|
Doing my nut in, and no amount of Googling is helping...
I get given an .xlsx sheet in a set format which I need to save as a CSV file so the accounting system can upload it.
My problem is the accounting system has to have bank sort codes entered as ##-##-##, and whenever I 'Save As' CSV and open the CSV to check the contents it's turning certain sort codes into dates
quote:
11-04-60 becomes 11/04/1960
This then throws up stupid errors all over the place and is generally a royal PITA. I've tried all sorts of cell formatting on the xlsx sheet before Saving As (Text, General, custom format 00-00-00) but nothing is working. I've even tried ' in front of the sort code, it doesnt work, but even if it did I cant be doing it hundreds of times every week.
Does anyone have any ideas? I have access to Excel 2003 or 2010 (2 different systems at work), I don't care which one it works on! 
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Check the CSV file within Notepad/Notepad++ (or similar) as you'll probably find the contents is correct, as it's likely Excel is attempting to reformat the cell data when you load the CSV file.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Find/replace hyphen with em-dash in source CSV using Notepad before opening in Excel?
To type an em-dash, hit Alt+0151
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Oh hang on, I think I've misunderstood the issue - is the problem that when you SAVE an Excel file as a CSV, your correctly-formatted sort codes get mangled into Dates?
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
If so, I reckon Dom is spot on - the actual CSV file will be fine, it's only upon reopening it to check in Excel that the sort codes get bollocksed.
|
dannymccann
Member
Registered: 9th Aug 06
Location: Doddington, Lincolnshire
User status: Offline
|
I'm thinking you guys are right. When I loaded the unopened CSV file into my software it seems to have gone alright.
Such an annoyance though! Cheers
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
I was always under the impression csv's held no formatting so regardless of what format it was in when its opened once its closed its gone anyway.
|
