Unable to save Calmonth in MM/YYYY format in .CSV file

[Former Member]

Hello All,

I have to convert the excel data provided by the user to .csv file to upload to BW.

my excel data is:

Month Issue Count

01/2008 Fleet 2

01/2008 HR 6

02/2008 Expense 1

02/2008 Fleet 2

03/2008 Fleet 3

03/2008 HR 2

03/2008 Other 1

when i save this to a .csv file and close and reopen, then the file looks like:

Month Issue Count

Jan-08 Fleet 2

Jan-08 HR 6

Feb-08 Expense 1

Feb-08 Fleet 2

Mar-08 Fleet 3

Mar-08 HR 2

Mar-08 Other 1

why is it happening this way?

i want the first column to be stored as mm/yyyy only.

If i click on the cell Jan-08 , the cell value it is showing is 01/01/2008.

Is there any other way i can format calmonth to be able to load to BW through.csv file.

request you to please help me regarding this.

Regards,

Dhanya

[Former Member · Accepted solution]

Hi Dhanya,

After saving the file as CSV, open the file in Excel. Then choose the calmonth column and right click on it. Choose format cell and then choose Custom option. On the right hand side you will see "Type" input box with General. On that "000000" key in 7 zeros then save your .CSV file. If you need to edit the file then open it as notepad this will retain the format.

Hope it helps.

Cheers,

Balaji

[Former Member · Accepted solution]

Open your CSV file and then choose the calmonth column and right click on it.

Choose format cell and then choose Custom option on the left hand side.

On the right hand side you will see "Type" input box, you have to enter MM/YYYY in that input box and then Give OK.

The data in calmonth column would be converted to MM/YYYY format.

--- Thanks...

[Former Member · Accepted solution]

Hello,

if i format the calmonth column to MM/YYYY by using custom option.

After i close and reopen the .csv file in MS excel, i still the data as Jan-08.

I am not able to see the data in MM/YYYY format.

Regards,

Dhanya

[Former Member · Accepted solution]

The format will be gone if you open your CSV in Excel. Open it in Notepad it will retain your format..

[Former Member · Accepted solution]

Hello Balaji,

yes, i understand that.

I have to automate the file load to BW.

For this i will have to store the data to be picked up by BW in .csv format only as it is better than .txt format.

Hence , i have to provide the users the .CSV template and they will feed in the data in it and save.

If they visually see Jan-08 when they enter 01/2008 and save, it will be confusing for them.

It will also be difficult for me to explain in what format they will enter, what they will get to see if they open again etc...

In my previous project experiences, i used to load calmonth from CSV file.

It always used to store in mm/yyyy format.

I am not sure why now , this strange feature is happening.

Please let me know if anyway i can display in the format MM/YYYY in .CSV.

Regards,

Dhanya

[Former Member · Accepted solution]

The problem is with the separator '/' in the format MM/YYYY

I think this is a bug in excel. you have to try MMYYYY or YYYYMM format as a work around.

--- Thanks...

[Former Member · Accepted solution]

hi,

MMYYYY or YYYYMM is not user friendly.

But even if i type in MMYYYY format.

The value 012008 after saving and reopening , it shows as 12008.

Regards,

Dhanya

[Former Member · Accepted solution]

Hi Dhanya,

It is issue with the column in your template , when your users key in 012008 and they can save the file as .CSV. If you want to validate the entry then ask them to open the .CSV file through notepad. All you need to see is "," separator between the fields.

Hope it is clear.

Balaji

[Former Member · Accepted solution]

If thhis CSV file needs to be loaded to a DSO then CALMONTH fielld should be maintained as YYYYMM.So you can ttry storing the data on this format like 201103.

[Former Member · Accepted solution]

HI Dhanya,

Open you csv and select the entire colum (date value) and right click , Choose text from general tab.

may be your existing value been changed to some other value. try to enter your required date as you wish to enter and see.

Hope this will help you to resolve your issue ... Correct me if i am wrong in understanding ur requirement ...

best regards

BVR