2

I have a problem. I have a CSV file which we are trying to load into an Oracle database. The problem I believe is with the size of date field. When I open the file up in a text editor the field is only 19 characters in size. When I open it up in Excel this goes to 20 characters, with 2 spaces between the date and time (see below).

enter image description here

The cell format is below.

enter image description here

Why does it show up as 2 characters between the date and time in excel but only 1 space when opened up in a text editor? What can I do for the space to be only 1 character when I open it up in excel?

Shaun Kinnair
  • 495
  • 2
  • 10
  • 27
  • 3
    You can't open this in excel and expect to get good results. Excel will BUTCHER your CSV file. If it sees something it thinks is a date it will convert it to it's date format (Today is 43823). What you SEE in excel is a formatted version of that date, that format is 100% NOT representative of your data in the CSV before you let excel butcher it. If you are working with data in a csv and a database, don't let excel get it's hands on it. And if you do open it in excel, DON'T save it. – JNevill Jul 02 '18 at 12:43
  • In short, from an excel perspective, there is: 1) The way excel stores the date (43823) and 2) the way excel displays the date (Cell's formatting). Neither of these represent the original date/string that was in the CSV before it was opened in excel. – JNevill Jul 02 '18 at 12:46
  • 1
    ... well some of ^^^this^^^ but more of a 'choose import and select the right DMY vs MDY date import mask instead of double-clicking the CSV file from an Explorer window and expecting it to arrive correctly'. [this](https://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date/30609676#30609676) –  Jul 02 '18 at 12:47
  • If the presented data even after all of above occurs with two spaces. You should trim the specific column, possible through vba. Set a range, rng.value = Application.trim(rng) – JvdV Jul 02 '18 at 12:52
  • So basically what you are all saying the data in the date field is correct in the CSV file, its just when you open it up in excel it gets butchered and makes it look incorrect. I'll look into the oracle package this maybe a red hearing, well i'm certain judging by your feedback. – Shaun Kinnair Jul 02 '18 at 13:05
  • 1
    I agree! Never open it in Excel (unless you just want a nice visual representation). This in no way represents the data in the CSV file. If you want to inspect it, open it in something like Notepad++ and enable hidden characters. – Nick.Mc Jul 02 '18 at 13:29
  • @ShaunKinnair That is correct. But if you need it in Excel, you should **Import** during which you can specify the date formatting of the CSV file. And, if this is repetitive and you are using Excel 2010 or later, you can set up a data connection which will remember the process (`Power Query` aka `Get & Transform`) – Ron Rosenfeld Jul 02 '18 at 19:11

0 Answers0