0

So I have a csv file containing a column with datetime as values. The format was mm/dd/yyyy, hh:mm:ss. I want to export it to mysql but it won't accept this format. Acceptable format is yyyy-mm-dd hh:mm:ss. I already tried changing the format via the custom formatting but nothing changes. Are there alternative ways to change it?

Screenshot of the file: image

jomx99
  • 3
  • 4
  • Try `text(yyyy-mm-dd)`? – findwindow May 04 '22 at 16:03
  • tried that with the time data , =text(yyyy-mm-dd hh:mm:ss), nothing changed – jomx99 May 04 '22 at 16:07
  • 1
    @jomx99 you have not considered the cell reference, did you ? `=TEXT(A2,yyyy-mm-dd hh:mm:ss)` if this doesn't change then may try using this `=TEXT(SUBSTITUTE(A2,CHAR(160),"")*1 yyyy-mm-dd hh:mm:ss)` – Mayukh Bhattacharya May 04 '22 at 16:20
  • @MayukhBhattacharya tried both. For the first one, there's no change. The second one pops a notification that says too few arguments – jomx99 May 05 '22 at 13:26
  • @jomx99 i think you are missing something, please copy properly or upload a screenshot or share a copy of the workbook. – Mayukh Bhattacharya May 05 '22 at 13:30
  • @MayukhBhattacharya Just edited the question to post the screenshot. Don't know how to upload a copy of the file. Also, is there a way to convert the data in place? By that I mean modify it in the same cell? – jomx99 May 05 '22 at 14:11
  • @jomx99 those timestamps are left aligned and they are not actual excel date times, have you tried using the Text To Columns From Data Tab? Yes using Text To Columns it will remain in the same cell only – Mayukh Bhattacharya May 05 '22 at 14:17
  • @MayukhBhattacharya Actually that's the first solution I tried but the process involves separating it into two columns then concatenating. This shouldn't be a problem but i got like thousands of these csv files. Combining these into a single csv file and doing the operation once is also not an option because I have to export it to a database and it will take a lot of time to just export that single file since my computer is don't have great specs. – jomx99 May 05 '22 at 14:32
  • @jomx99 if you could share a copy of the file, then it would have been helpful, so that i can find offending thing which occurring which is not letting to make life easy. Could you share a gdrive link? – Mayukh Bhattacharya May 05 '22 at 14:35
  • @MayukhBhattacharya here's the link: https://drive.google.com/file/d/1RebStgc2hfrxidkOScKY6Ci6BvXa5aZG/view?usp=sharing – jomx99 May 05 '22 at 14:43
  • @MayukhBhattacharya Do you know python? Newbie programmer here just know the basics. Can these be modified via python? – jomx99 May 05 '22 at 14:47
  • Not yet started. – Mayukh Bhattacharya May 05 '22 at 14:49
  • @jomx99 please download from here, [Download](https://1drv.ms/x/s!AlEkksoPTc2chBAiEZFI1jGjZlV3?e=uNOicb) – Mayukh Bhattacharya May 05 '22 at 15:02

0 Answers0