When I open my data file (originally in .dbf format, in excel, the date field in some cell is normal i.e. dd/mm/yyyy but in many it chages to yyyymmdd (without even the slash-"/". Such misformated cells rows are approximately 40-45% out of total rows. Because of this trouble I can not use the converted file in excel. The original data file is generated through a accounting software being used since more than 20 years and have never encountered any mistake while using in dbase.
I think it would help to see examples of the dates that and are not converted.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell c ontains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Thanks for an immediate reply. As asked attaching herewith both the files (1) TJNV1116.DBF & (2) Saved as TJNV1116.xlsx after opening the .dbf file. Thanks..
.dbf file could not be aploaded...
There's no .dbf file ...
It does not allow to upload .dbf file & gives a message saying "Invalid File"
You could put it a zip file and upload that.
Regards, TMS
Thanks for valued guidance. Attaching herewith zip file containing both files .xlsx & .dbf. The xlsx file is created by opening the .dbf file in excel in which come cells of Doc_date column (dd/mm/yyyy) are changed to yyyymmdd. I tried to reformate the cells by selecting the whole column but no success. But if you enter the date manually in dd/mm/yyyy it accept. This option is not feasible because of large number of rows. Awating for the valued suggestion/guidance & solution. Thanks..
Interestingly, the file opens OK for me and the dates are all fine. It looks as though it must be a problem with Regional Settings. All the dates where the day is less than or equal to 12 are shown as dates in your file. However, they're probably not the date(s) you think they are. For example, the first date in your Excel file is 11/01/2006. When I open the .dbf file, the first date is 01/11/2006 which seems logical as all the dates appear to be for November 2006.
When you are opening the file, it is taking what should be a dd/mm/yyyy formatted date and considering it to be a mm/dd/yyyy formatted date. Although wrong, it "copes" with dates where the day number could be a month number. It falls over when it gets to 13 because that is not a valid month (obviously).
If you select the cells that look like dates, and choose Data | Text to Columns, Delimited, Next, Next, Column Data Format = MDY, Finish, it will convert the dates to what they should be.
Similarly if you choose the text numbers that look like dates and choose the Column Data Format = YMD, it will fix those.
You could ssort on column D first which will bring all the "dates" together followed by all the text. Then you convert all the "dates in one go followed by all the text.
I've attached the .xlsx file I saved from the .dbf file for reference.
Regards, TMS
HTML Code:COM_CD DOC_TYPE DOC_NO DOC_DATE ENTRY_NO PARTY_CODE EXP_ACC REF_TYPE REF_NO REF_DATE PARTICULAR CR_AMT DB_AMT AMT FBT 01 JNV 01 1 01/11/2006 1 09018 09018 0.00 3000.00 0.00 01 JNV 01 1 01/11/2006 2 DNUJAL DNUJAL 3000.00 0.00 0.00 01 JNV 01 1 01/11/2006 3 22094 22094 0.00 1500.00 0.00 01 JNV 01 1 01/11/2006 4 DNUJAL DNUJAL 1500.00 0.00 0.00 01 CRN 04 1 04/11/2006 1 361001 361001 0.00 10317.00 0.00 01 CRN 04 1 04/11/2006 2 361002 361002 0.00 34846.00 0.00 01 CRN 04 1 04/11/2006 3 DGAME2 DGAME2 45163.00 0.00 0.00 01 CRN 04 2 04/11/2006 1 361001 361001 0.00 286212.00 0.00 01 CRN 04 2 04/11/2006 2 361002 361002 0.00 55161.00 0.00 01 CRN 04 2 04/11/2006 3 DNDJAL DNDJAL 341373.00 0.00 0.00 01 CRN 04 3 04/11/2006 1 361001 361001 0.00 268040.00 0.00 01 CRN 04 3 04/11/2006 2 433001 433001 0.00 662.00 0.00 01 CRN 04 3 04/11/2006 3 361002 361002 0.00 148102.00 0.00 01 CRN 04 3 04/11/2006 4 DNBBHA DNBBHA 416804.00 0.00 0.00 01 JNV 10 1 10/11/2006 1 10068 10068 0.00 320.00 0.00 01 JNV 10 1 10/11/2006 2 000712 000712 320.00 0.00 0.00 01 JNV 13 1 13/11/2006 1 01102 01102 0.00 784.00 0.00 01 JNV 13 1 13/11/2006 2 000712 000712 784.00 0.00 0.00 01 JNV 17 1 17/11/2006 1 14067 14067 0.00 5815.00 0.00 01 JNV 17 1 17/11/2006 2 16103 16103 5815.00 0.00 0.00 01 JNV 17 1 17/11/2006 3 14067 14067 0.00 1350.00 0.00 01 JNV 17 1 17/11/2006 4 20025 20025 1350.00 0.00 0.00 01 JNV 17 1 17/11/2006 5 14067 14067 0.00 1350.00 0.00 01 JNV 17 1 17/11/2006 6 11123 11123 1350.00 0.00 0.00 01 JNV 20 1 20/11/2006 1 20030 20030 0.00 765.00 0.00 01 JNV 20 1 20/11/2006 2 000712 000712 765.00 0.00 0.00 01 JNV 25 1 25/11/2006 1 22094 22094 0.00 530.00 0.00 01 JNV 25 1 25/11/2006 2 DNKRAS DNKRAS 530.00 0.00 0.00 01 JNV 24 1 24/11/2006 1 10040 10040 0.00 284.00 0.00 01 JNV 24 1 24/11/2006 2 000712 000712 284.00 0.00 0.00 01 JNV 27 1 27/11/2006 1 01102 01102 0.00 140.00 0.00 01 JNV 27 1 27/11/2006 2 000712 000712 140.00 0.00 0.00 01 JNV 28 2 28/11/2006 1 000708 000708 0.00 41.00 0.00 01 JNV 28 1 28/11/2006 1 02103 02103 0.00 14.00 0.00 01 JNV 28 1 28/11/2006 2 01035 01035 0.00 21.00 0.00 01 JNV 28 1 28/11/2006 3 13146 13146 0.00 3.00 0.00 01 JNV 28 1 28/11/2006 4 08056 08056 0.00 2.00 0.00 01 JNV 28 1 28/11/2006 5 22089 22089 0.00 21.00 0.00 01 JNV 28 1 28/11/2006 6 000708 000708 61.00 0.00 0.00 01 JNV 28 2 28/11/2006 2 12018 12018 13.00 0.00 0.00 01 JNV 28 2 28/11/2006 3 19153 19153 3.00 0.00 0.00 01 JNV 28 2 28/11/2006 4 16122 16122 22.00 0.00 0.00 01 JNV 28 2 28/11/2006 5 03047 03047 2.00 0.00 0.00 01 JNV 28 2 28/11/2006 6 14106 14106 1.00 0.00 0.00 01 JNV 08 1 08/11/2006 1 000705 000705 0.00 8140.00 0.00 01 JNV 08 1 08/11/2006 2 01038 01038 8140.00 0.00 0.00 01 JNV 03 1 03/11/2006 1 624 000503 0.00 18824.00 0.00 01 JNV 03 1 03/11/2006 2 01096 01096 18824.00 0.00 0.00 01 JNV 08 2 08/11/2006 1 624 000503 0.00 7233.00 0.00 01 JNV 08 2 08/11/2006 2 16039 16039 7233.00 0.00 0.00 01 JNV 10 1 10/11/2006 3 624 000503 0.00 15695.00 0.00 01 JNV 10 1 10/11/2006 4 10068 10068 15695.00 0.00 0.00 01 JNV 20 1 20/11/2006 3 000611 000611 0.00 15000.00 0.00 01 JNV 20 1 20/11/2006 4 20030 20030 15000.00 0.00 0.00 01 JNV 24 1 24/11/2006 3 624 000503 0.00 49304.00 0.00 01 JNV 24 1 24/11/2006 4 10040 10040 49304.00 0.00 0.00 01 JNV 24 1 24/11/2006 5 624 000503 0.00 51853.00 0.00 01 JNV 24 1 24/11/2006 6 10040 10040 51853.00 0.00 0.00 01 JNV 06 1 06/11/2006 1 000502 000502 0.00 1859.00 0.00 01 JNV 06 1 06/11/2006 2 000502 000502 0.00 1343.00 0.00 01 JNV 06 1 06/11/2006 3 000608 000608 0.00 410.00 0.00 01 JNV 06 1 06/11/2006 4 03051 03051 3612.00 0.00 0.00 01 JNV 09 1 09/11/2006 1 624 000503 0.00 6233.00 0.00 01 JNV 09 1 09/11/2006 2 02117 02117 6233.00 0.00 0.00 01 JNV 13 2 13/11/2006 1 000502 000502 0.00 2560.00 0.00 01 JNV 13 2 13/11/2006 2 22094 22094 2560.00 0.00 0.00 01 JNV 14 1 14/11/2006 1 000502 000502 0.00 521.00 0.00 01 JNV 14 1 14/11/2006 2 22094 22094 521.00 0.00 0.00 01 JNV 15 1 15/11/2006 1 000502 000502 0.00 2503.00 0.00 01 JNV 15 1 15/11/2006 2 001104 001104 0.00 1516.50 0.00 01 JNV 15 1 15/11/2006 3 000618 000618 0.00 145.00 0.00 01 JNV 15 1 15/11/2006 4 03051 03051 4164.50 0.00 0.00 01 JNV 10 2 10/11/2006 1 001101 001101 0.00 3175.00 0.00 01 JNV 10 2 10/11/2006 2 02086 02086 3175.00 0.00 0.00 01 JNV 29 1 29/11/2006 1 000502 000502 0.00 5500.00 0.00 01 JNV 29 1 29/11/2006 2 18099 18099 5500.00 0.00 0.00 01 JNV 29 1 29/11/2006 3 000611 000611 0.00 4500.00 0.00 01 JNV 29 1 29/11/2006 4 18099 18099 4500.00 0.00 0.00 01 JNV 29 2 29/11/2006 1 18099 18099 0.00 230.00 0.00 01 JNV 29 2 29/11/2006 2 000712 000712 230.00 0.00 0.00 01 JNV 30 1 30/11/2006 1 22039 22039 0.00 314.00 0.00 01 JNV 30 1 30/11/2006 2 000712 000712 314.00 0.00 0.00 01 JNV 30 2 30/11/2006 1 22094 22094 0.00 1000.00 0.00 01 JNV 30 2 30/11/2006 2 DNVYOG DNVYOG 1000.00 0.00 0.00 01 JNV 30 3 30/11/2006 1 624 000503 0.00 22464.00 0.00 01 JNV 30 3 30/11/2006 2 16129 16129 22464.00 0.00 0.00 01 JNV 18 1 18/11/2006 1 000704 000704 0.00 3750.00 0.00 01 JNV 18 1 18/11/2006 2 20033 20033 3750.00 0.00 0.00
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks