🔒 need to get a CSV into MySQL. TIMESTAMP col has datetime in two formats

[severe thunder]

I've attached a table snippet with the first three and last three timestamp entries.

When I try to format the two sections of the whole thing, the top part looks better but when you click on a cell, it still shows the old version (with the forward slash instead of dash between parts, and AM/PM instead of 24 hour clock) so I don't think excel formatting is actually changing the cell contents. And then the bottom part refuses to do anything on attempts to format it. Nothing changes.

So ok fine let's see if we can open the csv or open as xlsx and save as a csv later?

I had tried to do stuff with pandas but am not exactly sure how to use pd.to_datetime()
and I would need to slice it like "df where timestamp contains "/" then do this" and "df where timestamp not contains "/" then do this": df[df['TIMESTAMP'] ? I can see there is a series.str.contains but that is a series, not a df...

I tried to refresh on openpyxl

Plant_Generation_Data = 'C:/path/to/file'
wb = load_workbook(Plant_Generation_Data)
ws = wb.active
for ea in ws.iter_cols(min_col=1, max_col=1, values_only=True):
    #values_only=True means it shows the value, but the generator returns a tuple. so when I try to interact with it, I'm doing it wrong.  (but I was thining trying to do a datetime.datetime thing like I think that has a to_datetime somewhere?```

```df['TIMESTAMP'] = df['TIMESTAMP'].astype({'TIMESTAMP': 'datetime64'})```
also I had tried to cast the column as datetime and it looks like the bottom part was successfully turned into the datetime type and the top part is still read...as whatever it is. I need to double check. huh ok yea that is saying it is a datetime.datetime(2020, 1, 6, 1, 30), ok. ...wtf I double checked the type of the bottom section and it is just spitting out the contents ('31-05-2020 23:45', '31-05-2020 23:45') <class 'tuple'>

[terse saffron]

@severe thunder

Python help channel opened

Remember to:

• Ask your Python question, not if you can ask or if there's an expert who can help.
• Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
• Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.

[severe thunder]

uneditted at this point

[dry swift]

use a simple text editor (such as notepad or similar, not wordpad though) to look at the raw CSV file instead of using Excel, that way you can see what is actually in the file

[severe thunder]

1/6/2020 1:30,4135001,7JYdWkrLSPkdwr4,0,0,0,7727821
1/6/2020 1:30,4135001,McdE0feGgRqW7Ca,0,0,0,7286760
1/6/2020 1:30,4135001,VHMLBKoKgIrUVDU,0,0,0,7334405
...
31-05-2020 23:45,4135001,z9Y9gH1T5YWrNuG,0,0,6300,7133897
31-05-2020 23:45,4135001,zBIq5rxdHJRwDNY,0,0,0,6463239
31-05-2020 23:45,4135001,zVJPv84UY57bAof,0,0,6047,7242024

[dry swift]

you mention to_datetime but that is part of pandas, but i don't see you importing pandas in the little code that you have posted so far, but df suggests "dataframe" to me

[severe thunder]

mport regex as re
import pandas as pd
import csv
import mysql.connector

#Plant_Generation_Data = 'C:/ProgramData/MySQL/MySQL Server 9.4/Uploads/Plant_Generation_Data1.xlsx'
Plant_Generation_Data = 'C:/ProgramData/MySQL/MySQL Server 9.4/Uploads/Plant_Generation_Data1.csv'
#df = pd.read_excel(Plant_Generation_Data, engine='openpyxl')
df = pd.read_csv(Plant_Generation_Data)

df['PLANT_ID'] = df['PLANT_ID'].astype(str)
df['TIMESTAMP'] = df['TIMESTAMP'].astype({'TIMESTAMP': 'datetime64'})

I"ve tried a bunch of different stuff

[dry swift]

do you have some rows with one format and other rows with another format
that is not a good situation if you want to use convert the datetime field with just the library without some extra python code

[severe thunder]

rows 0, 1, and 2 and then the last three rows look like this, not fussed with at all

1/6/2020 1:30,4135001,7JYdWkrLSPkdwr4,0,0,0,7727821
1/6/2020 1:30,4135001,McdE0feGgRqW7Ca,0,0,0,7286760
1/6/2020 1:30,4135001,VHMLBKoKgIrUVDU,0,0,0,7334405
...
31-05-2020 23:45,4135001,z9Y9gH1T5YWrNuG,0,0,6300,7133897
31-05-2020 23:45,4135001,zBIq5rxdHJRwDNY,0,0,0,6463239
31-05-2020 23:45,4135001,zVJPv84UY57bAof,0,0,6047,7242024

so what I did is in excel I copied out each part and tried to mess with them separately

[dry swift]

looks like two different things have written to the same file with different formats

[severe thunder]

yes. I said that.

or I hope I did anyway

the way the datetime info got recorded got changed

[dry swift]

aha, i see

[severe thunder]

which, fine, but it's killing me that what I'm doing hasn't worked.

[dry swift]

then i think you need to handle that manually

[severe thunder]

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver17 SQL should be able to handle datetime in so many different formats and I tried to get the two into the same format

[dry swift]

you said MySQL (by Oracle) not MSSQL (Microsoft SQL server, by Microsoft as the name implies), they are vastly different, and that documentation is for MS SQL not MySQL

@severe thunder date formats are really hard for computers to guess, how should it know if DD/MM/YYYY (British format) or MM/DD/YYYY (American format) in the first three rows in your example?
the last three rows one can use deduce that it must be DD-MM-YYYY since there is only 12 months in a year and the number that shows there is 31 and the second group of numbers doesn't have any values that exceed 12

[severe thunder]

correct. I changed the order of the first part to dd-mm-yyyy so it would all be that

[dry swift]

okay, so now all rows are in the same format?

@severe thunder you could just use the mysql client to import the data directly similar to (form one of your previous thread): #1403441704572289194 message
or if you want to use python (with pandas and sqlalchemy) you could do it in a similar fashion as i showed just a few messages after that in the same help thread: #1403441704572289194 message

[severe thunder]

that's what notepad++ shows

[severe thunder]

cool I get that 1265 erorr

and every timestamp is 0000-00-00 00:00

[dry swift]

when you do which one of those?

you would have to customize it to this specific file though

[severe thunder]

drop table maxxenergy.plant_generation_data;

CREATE TABLE maxxenergy.plant_generation_data(
TIMESTAMP datetime,
PLANT_ID text,
SOURCE_KEY text,
DC_POWER float,
AC_POWER float,
DAILY_YIELD float,
TOTAL_YIELD float
);

LOAD DATA LOCAL INFILE 'C:\ProgramData\MySQL\MySQL Server 9.4\Uploads\plant_generation_data1.csv'
INTO TABLE maxxenergy.plant_generation_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

1265: Data truncated for column 'TIMESTAMP' at row 1 1265 Data truncated for column 'TIMESTAMP' at row 2 1265 Data truncated for column 'TIMESTAMP' at row

for every row

and as I said, sql should accept datetime in all of those formats

[dry swift]

you are quoting the documentation for MS SQL server, not MySQL, they don't work the same and it's no guaranties that MySQL can automatically determine the date format that it should use when ingesting that data

[severe thunder]

oh shoot

https://dev.mysql.com/doc/refman/8.4/en/datetime.html

I found the right doc I think

[dry swift]

@severe thunder try something like this

LOAD DATA LOCAL INFILE 'C:\ProgramData\MySQL\MySQL Server 9.4\Uploads\plant_generation_data1.csv' 
INTO TABLE maxxenergy.plant_generation_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@date_column,
`PLANT_ID`,
`SOURCE_KEY`,
`DC_POWER`,
`AC_POWER`,
`DAILY_YIELD`,
`TOTAL_YIELD`)
SET `TIMESTAMP` = STR_TO_DATE(@date_column, '%d-%m-%Y %H:%i');

ah, you got time in there as well

[severe thunder]

didn't error with the %H%i

hold

let me check it

[dry swift]

and the documentation literally says:

Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

[severe thunder]

INTO TABLE maxxenergy.plant_generation_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@date_column,
`PLANT_ID`,
`SOURCE_KEY`,
`DC_POWER`,
`AC_POWER`,
`DAILY_YIELD`,
`TOTAL_YIELD`)
SET `TIMESTAMP` = STR_TO_DATE(@date_column, '%d-%m-%Y %H:%i');```

DID IT

Thanks for pointing out my docs mistake

If I have this kind of issue again, I want to be able to figure it out on myown

where did you learn how to do that using string to date in the load data step?

[dry swift]

here is the docs for the formatting and parsing of date and times for mysql
https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-format

[severe thunder]

if I can read up how to apply this kind of fix in a general sense, I can do that instead of whining

[dry swift]

i gave you the url for that page in that last help thread as well: #1403441704572289194 message

but for completeness this is the url to that page: https://dev.mysql.com/doc/refman/8.4/en/load-data.html#load-data-input-preprocessing

[severe thunder]

imput preprocesing oooo OK I will read that after I eat some food

thank you so much!!!

[dry swift]

you're welcome, hope importing data to mysql databases will be easier after this 🙂

[severe thunder]

I think if I read carefully and slowly, it might

[terse saffron]

Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.