0

I have a .dat file that I saved as a .csv and it imports in a table, OK. But this file has in the first column = HOUR "hnnss". The file name contains the date that I already managed to separate it and save it in a variable=date.

My problem is: when saving the file as .csv I need to open the file, change the values of the first column from hnnss to data hh:nn:ss and then save and close, only then do I import it into the table. It needs to be in that sequence. Thanks for help.

PS: I'm using Access 365 + VBA 7.1

enter image description here

Mesut Akcan
  • 899
  • 7
  • 19
andreia
  • 1
  • 2
  • Which part you do not know how to do? Reading file, finding the column or changing the time format ? – Vlado Mar 10 '20 at 19:04
  • Hi Vlado, thanks for your answer. I need read the value in column HOUR (red) and change to the format green dd/mm/yyyy hh:nn:ss . This part red is just the time hh;nn:ss , but I have an variable with the date, so I have to do Mydate & hh:nn:ss in this column. – andreia Mar 11 '20 at 07:23

1 Answers1

0

Andreia: You can read the file using method like in here Import csv to array and in this loop :

    For i = 2 To UBound(aryFile) - 1  ' in your case start with second line
        tsOut.WriteLine aryFile(i)
    Next

implement the formatting like this:

    ' in your case start with second line if the 1st is header
    For i = 1 To UBound(aryFile) - 1  
        aryRow = Split(aryFile(i), ",")
        aryRow(0) = formatTime(aryRow(0), aryRow(?)) ' replace ? with index of date field
        tsOut.WriteLine Join(aryRow,",")
    Next

The above code is using function formatTime(fieldWithTime, fieldWithDate) which you need to write and which returns your formatted string for the whole date. I leave it for you. If you won't be able to code it let me know but in that case you rather read some books about VBA programming.

Note: I did not debug the code. This is just an idea.

Community
  • 1
  • 1
Vlado
  • 839
  • 6
  • 16
  • I checked my previous scripts and saw that I was converting the .dat file to .csv and added a replace ":" and that's why I was wasting my ":" hours. So, I canceled that part and I didn't need to include ":" because the original file already has it.After that I used the query to concatenate the date I already had, with the hours.Thank you very, much!Now I'm going to fight with a selection of pens and date, to show on the Form (table) and then print that selection.My table has about 200 pens and I would like the Form to show only what the user selected. The same thing I must do for Graphics :/ – andreia Mar 13 '20 at 09:37