[Solved]User-defined date format (yyyymmdd) & data to column

[robleyd]

I note that in the final file the date format is back to DD-MM-YYYY - is this as needed or is this an error?

Is it the case that columns D and E have to be added from elsewhere? Or how are they produced? Are they always the same?

It seems to me that this process might be fairly easily done without macros. Depending on the answers to those questions, it may be possible to make a simple process proposal that wouldn't need a macro.

[Zizi64]

I am using Open office 3.1 version only.

Why? (Nobody will fix the bugs of a such old version...)

[Kprogrammer]

I am using Open office 3.1 version only.

Why? (Nobody will fix the bugs of a such old version...)

I Didn't think that using old version can cause bugs.
I will update it to latest version 4.1.5.

[Kprogrammer]

I note that in the final file the date format is back to DD-MM-YYYY - is this as needed or is this an error?,

This is what i am getting but this is not needed. It should be 20180831 as in PRODUCT_TEMP(Column c(Price_Date)).for this using user defined format (yyyymmdd) then applying data-> text to column->fixed width.

Is it the case that columns D and E have to be added from elsewhere? Or how are they produced? Are they always the same?

columns D and E are always same. just have to be removed or added as per the no. of records in source file.

It seems to me that this process might be fairly easily done without macros. Depending on the answers to those questions, it may be possible to make a simple process proposal that wouldn't need a macro.

Thanks for the suggestion.
Sorry, but what is simple process propsal , i didn't get it.

If this problem is resolved then on a single click i will be able to prepare my files which takes 5 seconds as i have to prepare multiple files like this and have to save time as well , so started working on macros.

Thank You for your reply.

[robleyd]

Sorry, but what is simple process propsal , i didn't get it.

I haven't proposed it yet Leave it with me - I'll have to do some screenshots and write up a suggestion. It is evening here; hopefully I can have something within 24 hours.

[Kprogrammer]

Thanks in advance.

As I am almost done with my macro code so trying to resolve this problem also.
I want my macro code to completely work to achieve this.
As using macro I will not get tired preparing multiple files doing it all manually and my 4 hours of time will be reduced to 5 seconds.My speed is also not constant sometimes it takes more time sometimes less.
So my complete focus is to resolve this problem and make my macro work successfully.

If there is any alternative available, that would also be a great help.

[Villeroy]

My first answer to this topic still applies. You must not import data as text that are meant to be numbers. Then you can apply number formats, preferably through cell styles like you would do in the GUI.
Converting wrong data (text dates) into right ones (day numbers) by means of macro code is error prone and difficult. It is completely impossible if you do not understand the application you are hacking upon.

[Kprogrammer]

My first answer to this topic still applies. You must not import data as text that are meant to be numbers.

As the source file(csv file) I am receiving , it has date stored in text format. Opening file is the first step of my code. when I open it , the date is text.

Now , should I change the type of date which is text to number?
If I have to do so then I can follow this post
(viewtopic.php?f=9&t=9923)

Then you can apply number formats, preferably through cell styles like you would do in the GUI.

After that I should apply this
oCell2.CellStyle = "DateStyle"
to format it as user-defined yyyymmdd and then data ->text to column.
Is it so?

This is how I am mapping your solution with my code.
Is my understanding right?
Is this how I should proceed? If not please guide where I am going wrong and what I should try.

Converting wrong data (text dates) into right ones (day numbers) by means of macro code is error prone and difficult. It is completely impossible if you do not understand the application you are hacking upon.

Yes, now I understand I tried converting text dates that's why I didn't get any result which is completely wrong.

Thank You.

[Kprogrammer]

My first answer to this topic still applies. You must not import data as text that are meant to be numbers.

As the source file(csv file) I am receiving , it has date stored in text format. Opening file is the first step of my code. when I open it , the date is text.

Now , should I change the type of date which is text to number?
If I have to do so then I can follow this post
(viewtopic.php?f=9&t=9923)

Then you can apply number formats, preferably through cell styles like you would do in the GUI.

After that I should apply this
oCell2.CellStyle = "DateStyle"
to format it as user-defined yyyymmdd and then data ->text to column->fixed.
Is it so?

This is how I am mapping your solution with my code.
Is my understanding right?
Is this how I should proceed? If not please guide where I am going wrong and what I should try.

Converting wrong data (text dates) into right ones (day numbers) by means of macro code is error prone and difficult. It is completely impossible if you do not understand the application you are hacking upon.

Yes, now I understand I tried converting text dates that's why I didn't get any result which is completely wrong.

Thank You.

[Zizi64]

rem https://wiki.openoffice.org/wiki/Docume ... er_Options

try this:

Code: Select all

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,1/1/2/4/3/10/4/1"
rem                                1: import from row 1
rem                                  1/1: 1. column is "Normal"
rem                                      2/4: 2. column is date with DD/MM/YY format
rem                                          3/10: 3. col. is US/EN (for the 'dot' dec.sep.)
rem                                               4/1: 4. column is "Normal" 

[Kprogrammer]

rem https://wiki.openoffice.org/wiki/Docume ... er_Options

try this:

Code: Select all

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,1/1/2/4/3/10/4/1"
rem                                1: import from row 1
rem                                  1/1: 1. column is "Normal"
rem                                      2/4: 2. column is date with DD/MM/YY format
rem                                          3/10: 3. col. is US/EN (for the 'dot' dec.sep.)
rem                                               4/1: 4. column is "Normal" 

Thank You for the solution.
I tried this.
Now in ProductUpdate30-aug-2018(source file) Date column(B) is 08/31/18
and in PRODUCT_temp(destination file),(Price_date)column(C) its 43343 which is a datevalue.

PFA for the output.
1)ProductUpdate31-Aug-2018_Trial1.csv
2)PRODUCT_Temp_Trial1.xls

[Villeroy]

The "apostrophe" thing is the most frequently asked spradsheet question in 4 decades. It is the exact same convention since 1979 (Visicalc, mother of all spreadsheets). You find hundreds of topics on this forum alone. Just search for "apostrophe" in the Calc forum.

Without any macro, there are methods to distinguish constant text values from constant numbers:
1) menu:View>Highlight Values [Ctrl+F8] shows constant numbers in blue
2) left vs. right orientation. constant numbers are bound to the right cell border, text is left oriented (unless you force some orientation for all types of values which is a common mistake)
3) various functions such as ISNUMBER(cell)

Since you strictly refuse to show us any example of the data, you are trying to import (edit: until now), I gave you a little helper routine which shows the fiter options of a loaded text file.
See your other topic 26-Aug-2018: viewtopic.php?f=20&t=94789&p=452188&hil ... ns#p452188
Import your data with as many options as possiblle, encoding, language, separators, text delimiters (or none), how to treat numbers in text delimiters and always check the special numbers options unless you know exactly why you do not want it, but even then you can define exceptions to the rule by marking columns as text.
Now run that macro. It pops up an input box with the FilterOptions string representing all the options you have set in the GUI dialog including the "special numbers" option. Copy the FilterOptions string from the input box and use it in your code.

Always keep in mind that the formatting of numbers is unimportant until you have to present (print) them. Don't waste your time with formatting attributes and when formatting becomes important use styles.
YEAR(43352) => 2018
MONTH(3352) => 9
DAY(43352) => 9
WEEKDAY(43352) => 1
simply because 43352 is the correct day number of 2018-09-09 (Sunday) and you can calculate with that day number with the same results regardless of its number format being $43,352.00 or 9-Sep-18 or showing some date from the Malayan calendar. Formatting does not count. Values count. And before you know the value of a cell, you should be aware if the displayed string represents a number or a text.

[robleyd]

Use File | Open and navigate to the source CSV file. When the ASCII text filter dialog opens, select Separated by as comma and ensure Detect special numbers is checked. This is a very important part of the process as it will allow Calc to import some items as numbers, such as dates, currency etc. Click on the header (Standard) for the column Ref No which you don't want to import, and select Hide from the Column Type dropdown list

Click OK. Only the first three columns will be imported.

Select the dates in the column under the header Date; right click and select Format Cells. Set the format code to YYYYMMDD and click OK

Now to move the Unit Price column to the left of the date column. Click on the label Unit Price; Shift+Ctrl+Down arrow to select all the price column data; drag the selected column over column B and while still holding the mouse button down, press and hold Alt, release the mouse button and release Alt. It may take a little practice to get this right.

And voila we have imported the data, formatted it and reorganised it.

This process takes me less than a minute.
Now all you need to do is add the information you need in columns D and E. Possibly a copy-paste , or perhaps you could use Edit | Fill to insert the data you need. Then just save - or save as - to the final file.

No crappy macro needed.

[Zizi64]

and in PRODUCT_temp(destination file),(Price_date)column(C) its 43343 which is a datevalue.

You can preformat the columns in the Template file by some user defined cell styles,
or you can format the date values in the destination file by your macro.

[Villeroy]

Finally we got some data! Praise the Lord.

I import your data with UTF-8 encoding, English (USA) as import language (because of the M/D/Y dates and the decimals with point as decimal separator. I choose comma as the only column separator and nothing as text separator. The "quoted fields" option is pointless here but I definitively want to interprete the special numbers in the second column. I mark the first and second columns as Text because these are identifiers with no arithmetic relevance.
Finally I run my macro showFilterOptions and get this FilterOptions string: 44,0,76,1,1/2/4/2,1033,false,true

And this is how the resulting sheet import looks in my German LibreOffice window with value highlighting turned on:
Columns 1 and 4 are left bound and black.
Columns 2 and 3 are right bound and blue with German default format for dates and comma delimited decmals

[Kprogrammer]

and in PRODUCT_temp(destination file),(Price_date)column(C) its 43343 which is a datevalue.

You can preformat the columns in the Template file by some user defined cell styles,

or you can format the date values in the destination file by your macro.

I tried this.

Code: Select all

rem getting 2nd cell of column B from source file 
 CellRange = Sheet1.getCellByPosition(1,1) rem cell has 08/31/18
oAllData = CellRange.getvalue()     
 msgbox oAllData,0, "oAllData(date)"   rem oAllData has 43343
rem formatting 2nd cell of column B of source file 
 dim res
 res= Format(oAllData,"yyyymmdd") rem it worked.
 msgbox res,0, "after formatting"  rem 20180831
 rem After formatting  pasting 2nd cell of column B from source file to 2nd cell of column C
rem of destination file
 CellRange = Sheet2.getCellByPosition(2,1)
  oAllData = CellRange.getvalue()
   res= Format(oAllData,"yyyymmdd")
 oAllData.setdata(res)

I am facing problem with these:-
1)pasting the formatted date to destination file didn't work.

2)Tried to select range and appying formatting to the cellrange

Code: Select all

CellRange = Sheet1.getCellRangeByPosition(1,1,1,n-1)
  oAllData = CellRange.getDataArray()
res= Format(oAllData,"yyyymmdd") rem Didn't work
 oAllData.setdata(res)

output:-Got error
2)tried using getdata() to get the data
output:- Got error.

Code: Select all

CellRange = Sheet1.getCellByPosition(1,1)
  oAllData = CellRange.getdata()

output:- Nothing displayed. It was blank.

Any solution for these?
Adding the screenshots.
PFA

Thank You

[Kprogrammer]

Use File | Open and navigate to the source CSV file. When the ASCII text filter dialog opens, select Separated by as comma and ensure Detect special numbers is checked. This is a very important part of the process as it will allow Calc to import some items as numbers, such as dates, currency etc. Click on the header (Standard) for the column Ref No which you don't want to import, and select Hide from the Column Type dropdown list

kprog001.png

Click OK. Only the first three columns will be imported.

kprog002.png

Select the dates in the column under the header Date; right click and select Format Cells. Set the format code to YYYYMMDD and click OK

kprog003.png

Now to move the Unit Price column to the left of the date column. Click on the label Unit Price; Shift+Ctrl+Down arrow to select all the price column data; drag the selected column over column B and while still holding the mouse button down, press and hold Alt, release the mouse button and release Alt. It may take a little practice to get this right.

kprog004.png

And voila we have imported the data, formatted it and reorganised it.

kprog005.png

This process takes me less than a minute.
Now all you need to do is add the information you need in columns D and E. Possibly a copy-paste , or perhaps you could use Edit | Fill to insert the data you need. Then just save - or save as - to the final file.

No crappy macro needed.

Thank You.
If i wouldn't make it possible using macro , will follow this.

[Kprogrammer]

and in PRODUCT_temp(destination file),(Price_date)column(C) its 43343 which is a datevalue.

You can preformat the columns in the Template file by some user defined cell styles,

Sorry, i didn't get it. How i can do this?

or you can format the date values in the destination file by your macro.

I tried this.

Code: Select all

rem getting 2nd cell of column B from source file 
 CellRange = Sheet1.getCellByPosition(1,1) rem cell has 08/31/18
oAllData = CellRange.getvalue()     
 msgbox oAllData,0, "oAllData(date)"   rem oAllData has 43343
rem formatting 2nd cell of column B of source file 
 dim res
 res= Format(oAllData,"yyyymmdd") rem it worked.
 msgbox res,0, "after formatting"  rem 20180831
 rem After formatting  pasting 2nd cell of column B from source file to 2nd cell of column C
rem of destination file
 CellRange = Sheet2.getCellByPosition(2,1)
  oAllData = CellRange.getvalue()
   res= Format(oAllData,"yyyymmdd")
 oAllData.setdata(res)

I am facing problem with these:-
1)pasting the formatted date to destination file didn't work.

2)Tried to select range and appying formatting to the cellrange

Code: Select all

CellRange = Sheet1.getCellRangeByPosition(1,1,1,n-1)
  oAllData = CellRange.getDataArray()
res= Format(oAllData,"yyyymmdd") rem Didn't work
 oAllData.setdata(res)

output:-Got error
2)tried using getdata() to get the data
output:- Got error.

Code: Select all

CellRange = Sheet1.getCellByPosition(1,1)
  oAllData = CellRange.getdata()

output:- Nothing displayed. It was blank.

Any solution for these?
Adding the screenshots.
PFA

Thank You

[Kprogrammer]

The "apostrophe" thing is the most frequently asked spradsheet question in 4 decades. It is the exact same convention since 1979 (Visicalc, mother of all spreadsheets). You find hundreds of topics on this forum alone. Just search for "apostrophe" in the Calc forum.

Without any macro, there are methods to distinguish constant text values from constant numbers:
1) menu:View>Highlight Values [Ctrl+F8] shows constant numbers in blue
2) left vs. right orientation. constant numbers are bound to the right cell border, text is left oriented (unless you force some orientation for all types of values which is a common mistake)
3) various functions such as ISNUMBER(cell)

Since you strictly refuse to show us any example of the data, you are trying to import (edit: until now), I gave you a little helper routine which shows the fiter options of a loaded text file.
See your other topic 26-Aug-2018: viewtopic.php?f=20&t=94789&p=452188&hil ... ns#p452188
Import your data with as many options as possiblle, encoding, language, separators, text delimiters (or none), how to treat numbers in text delimiters and always check the special numbers options unless you know exactly why you do not want it, but even then you can define exceptions to the rule by marking columns as text.


Now run that macro. It pops up an input box with the FilterOptions string representing all the options you have set in the GUI dialog including the "special numbers" option. Copy the FilterOptions string from the input box and use it in your code.

Always keep in mind that the formatting of numbers is unimportant until you have to present (print) them. Don't waste your time with formatting attributes and when formatting becomes important use styles.
YEAR(43352) => 2018
MONTH(3352) => 9
DAY(43352) => 9
WEEKDAY(43352) => 1
simply because 43352 is the correct day number of 2018-09-09 (Sunday) and you can calculate with that day number with the same results regardless of its number format being $43,352.00 or 9-Sep-18 or showing some date from the Malayan calendar. Formatting does not count. Values count. And before you know the value of a cell, you should be aware if the displayed string represents a number or a text.

Thank You.
In other posts also you had explained this .
Thank You again,
Now it's clear to me.

[Zizi64]

1)pasting the formatted date to destination file didn't work.

Do not paste any formatted date. Paste only the VALUES of the date into the preformatted column of the template.

[Zizi64]

Do not paste any formatted date. Paste only the VALUES of the date into the preformatted column of the template.

Of course you must (but at least: better) use an ODF (.ots) type template formatted with User Defined cell styles - if you want use the "preformatting" method. Or you can format the result file by you macros after pasting the VALUES.

[Zizi64]

You can pate only the VALUES by this option:

Code: Select all

args5(0).Name = "Flags"
args5(0).Value = "V"
rem but not Strings and Values: "SV"

[Kprogrammer]

Do not paste any formatted date. Paste only the VALUES of the date into the preformatted column of the template.

Of course you must (but at least: better) use an ODF (.ots) type template formatted with User Defined cell styles - if you want use the "preformatting" method.

Or you can format the result file by you macros after pasting the VALUES.

I did exactly what you said and Finally , i achieved what i wanted to.

Code: Select all

for i =1 to n-1
CellRange = Sheet2.getCellByPosition(2,i)
oAllData = CellRange.getvalue()
res1= Format(oAllData,"yyyymmdd")
CellRange = Sheet2.getCellByPosition(2,i)
CellRange.string=res1
next i

Attaching the output.
PFA

Thank You so much for the guidance help and solution.

I have one question:-
In my code i have used loop and getting each cell then formatting it.
If i can get the entire cellrange and apply the formatting all at once to that cellrange.
I want to do this because when i have more than 3000 records then loop will take long time.
Is it possible?
Please suggest.

[Zizi64]

I have one question:-
In my code i have used loop and getting each cell then formatting it.
If i can get the entire cellrange and apply the formatting all at once to that cellrange.

I will repeat again:
USE THE .ods FILEFORMAT AND THE CELL STYLES. You can apply a cell style to a larger cell range in one step.

[Kprogrammer]

OK
Thank you.
I will follow this.

[Zizi64]

PRODUCT_Temp_Trial2.xls
Got the expected result
(47 KiB) Downloaded 1 time

There are Strings again (but not numeric values) in the Date column of your attached file.

[Kprogrammer]

PRODUCT_Temp_Trial2.xls
Got the expected result
(47 KiB) Downloaded 1 time

There are Strings again (but not numeric values) in the Date column of your attached file.

Yes. But at last the entire file has to be formatted as text.
and I tried formatting it as text ' removed and it got formatted as text.
So will it cause any problem?

[Zizi64]

So will it cause any problem?

I do not know it... it is depend on: what you want to do with the textual data...

[Kprogrammer]

Going to load the sheet in the database. So the sheet should not contain numbers and formulas that's why converting it as text at the end.

[Kprogrammer]

So will it cause any problem?

I do not know it... it is depend on: what you want to do with the textual data...

Going to load the sheet in the database. So the sheet should not contain numbers and formulas that's why converting it as text at the end.