Convert DD/MM/YYYY to MM/DD/YYYY and HH:MM to HH:MM AM/PM

[ALEXJ21]

Hello.

I have a CSV file with 2 rows that have the date in format DD/MM/YYYY and the hour in format HH:MM. I need to grab those values, convert them in MM/DD/YYYY and HH:MM AM/PM and then select those values from a dropdownmenu from a website. The rest is done, but the macro stops somehow here, but I got it done the first part(convert DD/MM/YYYY and choose from a dropdown the value).

Thank you in advance.

[Kevin]

hi alex to help us help you faster please do the following

In QM, to copy all or selected text, use menu Edit -> Other Formats -> Copy for QM Forum. It copies correct and colored code to the clipboard. Then simply paste in the forum. Don't use the Code button.

makes it a lot easier to see your code and to make suggestions and solutions.

i would suggest looking at 

Code:

Copy      Help

DateTime

DATE

in qm should be able to help with the conversions

[ALEXJ21]

Hi, and thank you for your answer.

[Kevin]

this is an easier way to work with csv will take csv string or file  convert date and time to the formats you wanted also shows how to access each row and cell

Function datefunction2

Code:

Copy      Help

out 
//// just for demo purposes
str csv=
;25/4/2018,18:23 
;25/4/2018,16:23
;25/4/2018,04:23

/////
out csv 
out "------------------------------------------------------------"
;create ICsv variable and load data
ICsv x._create
x.FromString(csv);; comment out or remove this line  and use line 15 below to load from a  csv file
// also remove lines 4-10
//x.FromFile("path of csv file") load csv data from file
str cell1 cell2
int nr=x.RowCount
int nc=x.ColumnCount
int r c
for r 0 nr ;;for each row
,for c 0 nc ;;for each column
,,if(c=0)
,,,cell1=x.Cell(r c)
,,,DATE d(cell1)
,,,cell1.timeformat("{MM/dd/yyyy}" d)
,,,x.Cell(r c)=cell1
,,else
,,,cell2=x.Cell(r c)
,,,DATE d1(cell2)
,,,cell2.timeformat("{hh:mm tt}" d1)
,,,x.Cell(r c)=cell2
x.ToString(csv); 
out csv ;;show CSV

also check out this topic  as it is similar to yours  http://www.quickmacros.com/forum/showthr...p?tid=4987
also check http://www.quickmacros.com/help/User/IDP_ICSV.html for more info on ICsv interface

---

You can access  each column  in the csv file  then use tok to split it further .
need more info where your code is failing to help you further

[Kevin]

example using date time to access the hours and mins 


Function datefunction2a

Code:

Copy      Help

out 
//// just for demo purposes
str csv=
;25/4/2018,18:23 
;25/4/2018,12:23
;25/4/2018,04:23

/////
out csv 
out "------------------------------------------------------------"
;create ICsv variable and load data
ICsv x._create
x.FromString(csv);; comment out or remove this line  and use line 15 below to load from a  csv file
// aslo remove lines 4-10
//x.FromFile("path of csv file") load csv data from file
str cell1 cell2
int nr=x.RowCount
int nc=x.ColumnCount
int r c
for r 0 nr ;;for each row
,for c 0 nc ;;for each column
,,if(c=0)
,,,cell1=x.Cell(r c)
,,,DATE d(cell1)
,,,cell1.timeformat("{MM/dd/yyyy}" d)
,,,x.Cell(r c)=cell1
,,else
,,,cell2=x.Cell(r c)
,,,DATE d1(cell2)
,,,cell2.timeformat("{hh:mm tt}" d1)
,,,x.Cell(r c)=cell2
,,,DateTime dd
,,,dd.FromStr(cell2)
,,,int  hours mins 
,,,dd.GetParts(0 0 0 hours mins 0)
,,,if(hours-12 >= 0)
,,,,if(hours =12)
,,,,,out "%i pm" hours
,,,,else
,,,,,hours-12
,,,,,out "%i pm" hours
,,,else
,,,,out "%i am" hours
,,,out mins
,,,out "////////////////////////////////////////////////////"
x.ToString(csv); 
out csv ;;show CSV

unfortunately DateTime GetParts returns hours in 24h format so kinda un does some of the formatting done before it but easy to convert back to 12h format

[ALEXJ21]

this is an easier way to work with csv will take csv string or file  convert date and time to the formats you wanted also shows how to access each row and cell

Function datefunction2

Code:

Copy      Help

out 
//// just for demo purposes
str csv=
;25/4/2018,18:23 
;25/4/2018,16:23
;25/4/2018,04:23

/////
out csv 
out "------------------------------------------------------------"
;create ICsv variable and load data
ICsv x._create
x.FromString(csv);; comment out or remove this line  and use line 15 below to load from a  csv file
// also remove lines 4-10
//x.FromFile("path of csv file") load csv data from file
str cell1 cell2
int nr=x.RowCount
int nc=x.ColumnCount
int r c
for r 0 nr ;;for each row
,for c 0 nc ;;for each column
,,if(c=0)
,,,cell1=x.Cell(r c)
,,,DATE d(cell1)
,,,cell1.timeformat("{MM/dd/yyyy}" d)
,,,x.Cell(r c)=cell1
,,else
,,,cell2=x.Cell(r c)
,,,DATE d1(cell2)
,,,cell2.timeformat("{hh:mm tt}" d1)
,,,x.Cell(r c)=cell2
x.ToString(csv); 
out csv ;;show CSV

also check out this topic  as it is similar to yours  http://www.quickmacros.com/forum/showthr...p?tid=4987
also check http://www.quickmacros.com/help/User/IDP_ICSV.html for more info on ICsv interface

---

You can access  each column  in the csv file  then use tok to split it further .
need more info where your code is failing to help you further

Hello and thank you for your input. Issue got resolved. Something was changed in the website structure, while I was trying to give you all the details, I reexamined the website code(option values and things like that).