[Resolved] Change value of the column .csv file using Access VBA RRS feed

  • Question

  • Hello, 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


    • Edited by ChiuratoA Friday, March 13, 2020 9:35 AM Resolved
    Tuesday, March 10, 2020 11:45 AM

All replies

  • I could see a couple options:

    1. Instead of importing the file directly, link it. Then use an append query to insert the data into the desired table. For the first column of the query, use a function to do your date conversion.

    2. Import the file into a temp table consisting of all text fields. Then use an append query to insert the data into the desired table, again using a function to convert the first column to the correct date format.

    Tuesday, March 10, 2020 12:59 PM
  • Hi Alphonse, thanks for your reponse :D I have this code , below, i'm thinking of putting the new code in this excerpt .... i already looked for some options on the internet but some codes are between vba and excel and give me object errors ... would you like to indicate some code please?

    I need do this change before import to table, directly in file.csv

    My code : ( some variables are public)

    Private Sub btConvertDAT_Click()
    Dim ServerA As String
    Dim ServerB As String
    Dim objFS  As Object
    Dim objTS  As Object
    Dim strContents As String
    Dim arrLines
    Dim i As Integer
    Dim AnyArray(10)
    arrLines = UBound(AnyArray)
    Dim strFileExists As String

    ServerA = sNetwork & sFileDAT
    ServerB = sFolderConvert & sFileCSV
    '***   if file exist, exit,... if not, create a new file.csv
    sFileName = sFolderConvert & sFileCSV
     strFileExists = Dir(sFileName)
       If strFileExists = "" Then
                    FileCopy ServerA, ServerB    '***   create a new file.csv, copy from network .dat and save as .csv
                    '***** replace symbols
                    Dim sBuf As String
                    Dim sTemp As String
                    Dim iFileNum As Integer
                    sFileName = sFolderConvert & sFileCSV
                     iFileNum = FreeFile
                    Open sFileName For Input As iFileNum
                    Do Until EOF(iFileNum)
                    Line Input #iFileNum, sBuf
                    sTemp = sTemp & sBuf & vbCrLf
                    Close iFileNum
                    ' *** this is to replace symbols in the names of columns
                    sTemp = Replace(sTemp, "[]", "")
                    sTemp = Replace(sTemp, " ", "")
                    sTemp = Replace(sTemp, ":", "")
                    sTemp = Replace(sTemp, ".", "")
                    sTemp = Replace(sTemp, "[", "")
                    sTemp = Replace(sTemp, "]", "_")
                    '*********   // HERE // ******************
                    'I'd like put the code to read HOUR column and change the format of the value to HH:nn:ss AND concatenate with my variable DATE and sabe in the same column but with the format "dd/mm/yyyy hh:nn:ss"
                    '*Save file as 
                    iFileNum = FreeFile
                    Open sFileName For Output As iFileNum
                    Print #iFileNum, sTemp
                    Close iFileNum
            MsgBox "The selected file already exists !! "
     End If

    End Sub

    • Edited by ChiuratoA Tuesday, March 10, 2020 1:51 PM
    Tuesday, March 10, 2020 1:49 PM
  • I don't do much with direct file manipulation, but I would think that you could take the first 'field' of each line and reformat it as needed.

    I never trust any imported data. What I typically do is import it into a temp table of all text fields.
    Then run queries to clean up the data. Replace Like the characters that you are removing/replacing. But I do it all with queries. Once cleaned up I then insert the data into the destination table. To me, that is much simpler than re-writing files.

    Tuesday, March 10, 2020 2:32 PM
  • It would be much more efficient to import the file into a temp table and run an UPDATE query to change the data. Then Run an INSERT query to put it in the desired table.

    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Tuesday, March 10, 2020 2:48 PM
  • Hi Bill, thanks for replay....and how can I do this?  I'm starting to use VBA and Access and I'm not an expert on the subject ;( ...
    Tuesday, March 10, 2020 4:19 PM
  • Create the temp table (let's call it "temp") by making a copy of the table you will be inserting the records. Copy the structure only. If the source table has an AutoNumber for a key, remove that from your temp table.

    Next, import the text file into temp using an import specification. That way, you can re-use the spec for each import.

    Once you have temp filled, run an UPDATE query that replaces the HOURS data with the formatted data. Something like this:

    UPDATE temp
    SET myField = myVariable & " " & IIf(Len(myField) = 5, "0" & Left(myField, 1) & ":" & Mid(myField, 2, 2) & ":" & Mid(myField, 4, 2), Left(myField, 2) & ":" & Mid(myField, 3, 2) & ":" & Mid(myField, 5, 2))

    The IIF statement checks to see if the time has 1 or 2 digits for the hour. If it has a total of 5 digits it formats the time with a leading zero; If it has a length other than 5 it formats it as is.

    This is assuming the data coming in is not flawed.

    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Tuesday, March 10, 2020 6:41 PM
  • Hi Bill, I am really grateful for your help, but nothing happened, I did the following:
    1-created temp table
    2- created the query
    3- I put a button on the form and the temp table and run the query. 

    a blank query appears, requesting data entry.

    HOUR is a number type as long



    sequence....3 after run the query, the expression is in other place, second column...

    PS: the expression doesn't accept  (,) only (;), an expression error appears.

    Thanks a lot !

    Tuesday, March 10, 2020 7:24 PM
  • Sorry to take so long getting back to you. I couldn't log in.

    First, you need to create an Update query. Open your query in design view and then click on the Update icon in the query type section of the Ribbon at the top. That will change the grid so it has an "Update To" line. That's where that long line I wrote goes. Don't use an equal sign just paste it in starting with myVariable. Change the field name to match what you have in the temp table.

    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Thursday, March 12, 2020 9:38 PM
  • 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, 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   :/ 

    Thanks  Bill and Alphonse   ;D

    Friday, March 13, 2020 9:33 AM