Changing .DAT to .CSV to Automatically Import into Tables

[ssanfu]

A couple of things:



You have spaces in your table names. Best thing would be to remove the spaces or replace spaces with underscores. Should NEVER use spaces in object names.
The next best would be to enclose the table names with brackets:

            vTbl = "H1PD XRAY Data"

would become

            vTbl = "[H1PD XRAY Data]"

In the sub "Public Sub Import1TextFile(ByVal pvFile)", there are variables not declared: "vSpecFile" and "vTbl"

<snip>I would like to import the files</snip>

The main thing is that the Transfertext command has the wrong parameter

    DoCmd.TransferText acExportDelim, vSpecFile, vTbl, vTarg, True

To Import data, this should be:

    DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True

Here is the sub with my changes: (my changes in blue)

'the source file is copied to a generic file everytime
'----------------
Public Sub Import1TextFile(ByVal pvFile)
    '----------------
    Dim vDir  As String 
    Dim vTarg   As String  
     Dim vSpecFile As String 
         Dim vTbl As String

    'make a copy
    vDir = "C:\REDSIII\ImportedFiles\"
    MakeDir vDir
    vTarg = vDir & "File2Import.txt"

    'import data
    FileCopy pvFile, vTarg

    Select Case True
        Case InStr(pvFile, "FW_H1PD_OE-STRP") > 0
            vSpecFile = "H1PD-XRaySpec"
            vTbl = "[H1PD XRAY Data]"

        Case InStr(pvFile, "FW_IMGC_RS-STRP") > 0
            vSpecFile = "IMGC-ImageSpec"
            vTbl = "[IMGC IMAGE Data]"

        Case InStr(pvFile, "FW_ITXM_RS-STRP") > 0
            vSpecFile = "ITxM-BloodSpec"
            vTbl = "[ITXM BLOOD Data]"

        Case Else
            Exit Sub
    End Select

    '   DoCmd.TransferText acExportDelim, vSpecFile, vTbl, vTarg, True
    DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True
End Sub

Oh, I keep getting 'The test file specification 'H1PD-XRaySpec' does not exist. You cannot import, export, oir link using the specification.

Did you create an import file spec named 'H1PD-XRaySpec'?
(BTW, shouldn't use special characters in object names. Only letters, numbers and the underscore)

[jjz0512]

Steve, thanks for you corrections/suggestions. I corrected the table names and replaced blank with _ and also corrected the spec name and replaced - with _. Here's my new code;

'the source file is copied to a generic file everytime
'----------------
Public Sub Import1TextFile(ByVal pvFile)
'----------------
Dim vDir As String
Dim vTarg As String
Dim vSpecFile As String
Dim vTbl As String


'make a copy
vDir = "C:\REDSIII\ImportedFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.csv"


'import data
FileCopy pvFile, vTarg


Select Case True
Case InStr(pvFile, "FW_H1PD_OE-STRP") > 0
vSpecFile = "H1PD_XRayImportSpec"
vTbl = "H1PD_XRAY_Data"


Case InStr(pvFile, "FW_IMGC_RS-STRP") > 0
vSpecFile = "IMGC_ImageImportSpec"
vTbl = "IMGC_IMAGE_Data"


Case InStr(pvFile, "FW_ITXM_RS-STRP") > 0
vSpecFile = "ITxM_BloodImportSpec"
vTbl = "ITXM_BLOOD_Data"


Case Else
Exit Sub
End Select


DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True
End Sub

But I'm still getting the error;

The test file specification 'H1PD_XRayImportSpec' does not exist. You cannot import, export, or link using the specification.

[jjz0512]

Thanks to all for your help, I was able to get the macro/module to work. But, I don't want to delete the file, need to move them to another directory after importing.

'----------------
Public Sub ScanAllFilesInDir(ByVal pvDir)
'----------------
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vTbl


On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"


Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)


For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(vFil, ".dat") > 0 Then 'ONLY DO .DAT FILES
Import1TextFile vFil
'DELETE THE FILE HERE
Kill vFil
End If
Next


Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub

[ssanfu]

Let me see if I've got this right:
1) You want to rename the files. Ex "FW_H1PD_OE-STRP1508252345220956.dat" would be renamed to "FW_H1PD_OE-STRP.csv"
Q: Why do you want to rename the file? The file doesn't need to be renamed to be imported.

2) You want to import the CSV file

3) You want to MOVE the original file or renamed file to a different directory?
Q: I think you said this happens daily. When you move the file does it go into the same folder every day or to different folders every day?



----------------
Hint:
Click on the hash (#) in the reply window menu before you paste in the code. This is how the code in my posts get enclosed - helps to preserve formatting/indenting of code.

[jjz0512]

I got the marco/module to run successfully, but I need to copy the 3 source file to another folder for archieve purposes. So I added this code;

'copy to final directory
mDir = "C:\REDSIII\STRIPEReceive\2015_Files\"
MakeDir mDirmTarg = mDir & pvFile
FileCopy pvFile, mTarg

but keep getting the error; Bad file name of Number

All help is appreciated !!

[jjz0512]

Thanks Steve, your right, I really don't need the .csv's, only to be able to import into access as the .dat files are in the correct format as the tables and the import specs. But, I do need to save the files from the source directory, C:\REDSIII\STRIPEReceive to a file directory of C:\REDSIII\STRIPEReceive\2015_Files. And yes, these 3 source file land into C:\REDSIII\STRIPEReceive daily. Currently I'm doing all this manually. Hope this helps.

Here's what I'm testing with now;

'----------------
Function LoadAllTextFiles()
'----------------
     ScanAllFilesInDir "C:\REDSIII\STRIPEReceive"
End Function

'----------------
Public Sub ScanAllFilesInDir(ByVal pvDir)
'----------------
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vTbl
On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(vFil, ".dat") > 0 Then      'ONLY DO .DAT FILES
           Import1TextFile vFil
              'DELETE THE FILE HERE
          Kill vFil
    End If
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub
'the source file is copied to a generic file everytime
'----------------
Public Sub Import1TextFile(ByVal pvFile)
'----------------
Dim vDir As String
Dim vTarg As String
Dim vSpecFile As String
Dim vTbl As String
Dim mDir As String
Dim mTarg As String
'copy to final directory
mDir = "C:\REDSIII\STRIPEReceive\2015_Files\"
MakeDir mDir
mTarg = mDir & pvFile
FileCopy pvFile, mTarg
'make a temporary copy
vDir = "C:\REDSIII\ImportedFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.csv"
'import data
FileCopy pvFile, vTarg
Select Case True
   Case InStr(pvFile, "FW_H1PD_OE-STRP") > 0
      vSpecFile = "H1PDImport"
      vTbl = "H1PD_XRAY_Data"
   Case InStr(pvFile, "FW_IMGC_RS-STRP") > 0
      vSpecFile = "IMGCImport"
      vTbl = "IMGC_IMAGE_Data"
   Case InStr(pvFile, "FW_ITXM_RS-STRP") > 0
      vSpecFile = "ITxMImport"
      vTbl = "ITXM_BLOOD_Data"
   Case Else
      Exit Sub
End Select
DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True
End Sub
'----------------
Public Sub MakeDir(ByVal pvDir)
'----------------
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir     'MkDir pvDir
Set fso = Nothing
End Sub
Sub Macro1()
End Sub

[ssanfu]

You said this is the time stamp: 1508252345220956
What are the parts??
15 = year
08 = month
25 = day
??????

I tried something different. Try this code:

Option Compare Database
Option Explicit

Public Sub ImportAndMove()
    Const SourcePath As String = "C:\REDSIII\STRIPEReceive\"     ' <<-MUST have a trailing back slash
    Const DestPath As String = "C:\REDSIII\STRIPEReceive\2015_Files\"   ' <<-MUST have a trailing back slash

    Dim vFile As String
    Dim vSpecFile As String
    Dim vTbl As String
    Dim OldPathName As String
    Dim NewpathName As String

    Dim knt As Integer  ' counter

    knt = 0
    vFile = Dir(SourcePath)
    While (vFile <> "")
        If InStr(vFile, ".dat") > 0 Then
            'clear pathe/name
            OldPathName = vbNullString     'changed name
            NewpathName = vbNullString   'changed name
            '            msgbox "found " & vFile

            Select Case True
                Case InStr(vFile, "FW_H1PD_OE-STRP") > 0
                    vSpecFile = "H1PDImport"
                    vTbl = "[H1PD_XRAY_Data]"

                Case InStr(vFile, "FW_IMGC_RS-STRP") > 0
                    vSpecFile = "IMGCImport"
                    vTbl = "[IMGC_IMAGE_Data]"

                Case InStr(vFile, "FW_ITXM_RS-STRP") > 0
                    vSpecFile = "ITxMImport"
                    vTbl = "[ITXM_BLOOD_Data]"

                Case Else
                    Exit Sub
            End Select

            'import data from .dat file
            DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vFile, True

            '   Syntax :   Name oldpathname As newpathname
            OldPathName = SourcePath & vFile
            NewpathName = DestPath & vFile
            ' Move file.
            Name OldPathName As NewpathName
            
            knt = knt + 1

        End If
        vFile = Dir
    Wend

    msgbox "Done -  " & knt & " files imported"

End Sub

If you create a button and name it "ImportAndMove", you can put the code in the form module.
Then change the name of the sub to "Public Sub ImportAndMove_Click()"

[jjz0512]

Steve, yes it's year, month, day, and a sequential number of the file. How do I test this? I had a macro that called the original function, what do I do here? Still new to macros/modules...

[ssanfu]

Create a new form.
Add a button. Hit Cancel when the wizard starts.
Open the properties dialog box
On the "Other" tab, change the button name to "ImportAndMove"
On the "Format" tab, change the "Caption" property to "ImportAndMove"
On the "Events" tab, click in the "On Click" row, then click on the 3 dots on the right.
Paste the code in the module.
Close and save.
In the form (normal view), click on the button.

[jjz0512]

Thanks Steve, your module was alot shorter. But I need a temporary file name to be able like what I was trying to use before, File2Import.csv, as I was using a common import file for the import specs. Otherwise I'll need a seperate file for each import with out the timestamps so they are the same every day. Can I use a temporary file name or does one have to be created like my original module had?


Thanks,
Jay

[ssanfu]

But I need a temporary file name to be able like what I was trying to use before, File2Import.csv, as I was using a common import file for the import specs. Otherwise I'll need a seperate file for each import with out the timestamps so they are the same every day.

The timestamp part of the file name do not matter in importing the data.

I don't understand the necessity of using a temp file name.
The steps are:
1) is the file a ".dat"
2) does the file name contain: "FW_H1PD_OE-STRP", "FW_IMGC_RS-STRP" or "FW_ITXM_RS-STRP" ?
2a) if yes, then use the respective import spec: "H1PDImport", "IMGCImport" or "ITxMImport"
2b) and import the data the respective table "[H1PD_XRAY_Data]", "[IMGC_IMAGE_Data]" or "[ITXM_BLOOD_Data]"
3) MOVE the .dat file to a different folder.

Can I use a temporary file name or does one have to be created like my original module had?

I can add code to create a temporary file name. But right now I don't understand how it will be used, so it is hard to know how to code for the temporary file name.

[jjz0512]

Steve,
Just getting back to the now. The temp was because I thought you couldn't import a .dat file into Access, no? I also need to import only a file named H1PD_OE-STRP.csv with the same named import spec. The file on the share is named FW_H1PD_OE-STRP1509132345410519.dat, the last part in a timestamp and sequential number for each file.

[jjz0512]

I also have another question, and haven't been able to figure it out or find a solution on the internet/forums. Today, i have to run each query manually and/or need to update if I have more that the 1 day for these file, ITxm, H1PD and IMGC, in the source directory. For example; here's my criteria for 1 column, Between Date()-2+#12:00:00 PM# And Date()-1+#11:59:00 PM#. When I run the queries, usally after midnight, i generate and don't worry about changeing anything. But when I have more the one day, I change the -2 to -3 and -1 to -2. What I would like to do is use a parameter in this query, expecailly of the weekends. Here's my sql and a typical column and criteria, I don't know how 12/30/1899 12:0:0# gets in there.

[ITXM Blood Data].[IssuedDate]+[ITXM Blood Data].[IssuedTime]

Between Date()-2+#12:00:00 PM# And Date()-1+#11:59:00 PM#

SELECT DISTINCT [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn") AS [CXR Image D/T]
FROM ([ITXM BLOOD Data] INNER JOIN [IMGC IMAGE Data] ON [ITXM BLOOD Data].MRN = [IMGC IMAGE Data].MRN) INNER JOIN ITxM_Data ON [IMGC IMAGE Data].MRN = ITxM_Data.MRN
WHERE ((([ITXM Blood Data].[IssuedDate]+[ITXM Blood Data].[IssuedTime]) Between Date()-2+#12/30/1899 12:0:0# And Date()-1+#12/30/1899 23:59:0#) AND (([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime]) Between Date()-2+#12/30/1899 23:45:0# And Date()-1+#12/30/1899 23:59:0#) AND ((DateDiff("n",[ITXM BLOOD Data].[IssuedDate]+[ITXM BLOOD Data].[IssuedTime],[IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime])) Between 0 And 751))
ORDER BY [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn");

Thanks again for all your help,
Jay

[ssanfu]

The temp was because I thought you couldn't import a .dat file into Access, no?

As long as the file is in CSV/Text format, you can name it almost anything you want.

I also need to import only a file named H1PD_OE-STRP.csv with the same named import spec

If I understand correctly, (in a copy) try making these two changes:

        If InStr(vFile, ".dat") > 0 Or InStr(vFile, ".csv") > 0 Then
            'clear pathe/name
            OldPathName = vbNullString     'changed name
            NewpathName = vbNullString   'changed name
            '            msgbox "found " & vFile

            Select Case True
                Case InStr(vFile, "H1PD_OE-STRP") > 0    ' <-- delete the "FW_"
                    vSpecFile = "H1PDImport"
                    vTbl = "[H1PD_XRAY_Data]"

Add the blue text......


Change the first case statement
from

Case InStr(vFile, "FW_H1PD_OE-STRP") > 0

to

Case InStr(vFile, "H1PD_OE-STRP") > 0

Delete the "FW_"




Have to think about the next question........

[jjz0512]

Thanks Steve, looking forward to your suggestion on inserting a parameter in stead of using Date()-2 in the criteria in the queries.

Changeing this; Between Date()-2+#12:00:00 PM# And Date()-1+#11:59:00 PM# to something like this; Between [Start Date]+#12:00:00 PM# And [End Date]+#11:59:00 PM#. In one column and others at the same time.