Changing .DAT to .CSV to Automatically Import into Tables

**jjz0512** · 08-26-2015, 07:02 AM

Hi, I relatively new to the forum and have a question.

I have 3 files on a network share; FW_H1PD_OE-STRP1508252345220956.dat, FW_IMGC_RS-STRP1508252345220954.dat, & FW_ITXM_RS-STRP1508252345220955.dat which have a timestamp created daily, the numbers after STRP. I want to change them to; FW_H1PD_OE-STRP.cvs, FW_IMGC_RS-STRP.csv, & FW_ITXM_RS-STRP.csv.

Then, I would like to import the files with the new names using the same import specs, move the original files to another folder and delete a 3 of them when I close the database.

Your help is appreciated, Thanks
Jay

**ranman256** · 08-26-2015, 07:17 AM

you dont have to rename them to import them.
Do you NEED them renamed for some other reason? they import fine as they are.

**ranman256** · 08-26-2015, 07:25 AM

put the code below into a module.
(press ctl-G, on the menu bar, click INSERT, MODULE. paste the code, save as module1)

to run the code,
create a new macro
for the action type RUNCODE
for the function name type: LoadAllTextFiles()
save

Code:

'----------------
function LoadAllTextFiles()
'----------------
     ScanAllFilesInDir "C:\folder\"
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, vTarg

  'make a copy
vDir = "c:\ImportFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.txt"

   'import data
FileCopy pvFile, vTarg
DoCmd.TransferText acExportDelim, "specName", "table", 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

**jjz0512** · 08-26-2015, 08:05 AM

No, renaming not need if I can import with the original name. Thought I had to as .dat file aren't support, I thought. I look at your next reply.

**jjz0512** · 08-26-2015, 08:24 AM

ranman256,
Sorry, I'm still a novice with Macro and Modules and not sure what I'm sure where to replace everything. I created the Module and Macro, changed the function to; ScanAllFilesInDir "N:\REDSIII\STRIPEReceive\". But then I'm stuck.

**jjz0512** · 08-26-2015, 08:31 AM

Also, I need to strip off the first 15 characters of each file name, add the .csv, to match my import spec, do I?

For example; This FW_H1PD_OE-STRP1508242345310739.dat is changed to FW_H1PD_OE-STRP.csv

**jjz0512** · 08-26-2015, 08:45 AM

Also, I need to only import these 3 files daily. Each file has it's own spec and respective table. FW_H1PD_OE-STRP.csv is imported to the [H1PD XRAY Data] table, FW_IMGC_RS-STRP.csv to [IMCG IMAGE Data], and FW_ITXM_RS-STRP.csv to [ITXM BLOOD Data].

**ranman256** · 08-26-2015, 10:53 AM

then build 1 macro that imports THOSE 3 import commands

TransferText acImportDelim, 'SPECname1', "H1PD XRAY Data", "c:\folder\FW_H1PD_OE-STRP.csv" ,true
TransferText acImportDelim, 'SPECname2', "IMCG IMAGE Data", "c:\folder\FW_IMGC_RS-STRP.csv" ,true
TransferText acImportDelim, 'SPECname3', "ITXM BLOOD Data", "c:\folder\FW_ITXM_RS-STRP.csv" ,true

**jjz0512** · 08-26-2015, 11:24 AM

I'm assuning I can place this in the sample that you sent me, because I need to check that location for those 3 original files, right? So, I need a statement that looks for FW_H1PD_OE-STRP* to create a FW_H1PD_OE-STRP.csv so I can run the TransferText statement, correct?

Sorry, still learning. Thanks for all your help!

**ranman256** · 08-26-2015, 11:31 AM

yes, but you MUST have the EXACT file path /name,
if it keeps changing, then you must use the code: Import1TextFile(), i sent above.

modified to:

Code:

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


  'make a copy
vDir = "c:\ImportFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.txt"


   'import data
FileCopy pvFile, vTarg


SELECT CASE TRUE
   case instr(pvFile,"FW_H1PD_OE-STRP")>0 
      vSpecFile = "XRAYspec"
      vTbl = "tXray"


   case instr(pvFile,"FW_IMGC_RS-STRP")>0 
      vSpecFile = "IMGspec"
      vTbl = "tIMAGE"


   case instr(pvFile,"FW_ITXM_RS-STRP")>0 
      vSpecFile = "BLOODspec"
      vTbl = "tBLOOD"


   CASE ELSE
      exit sub
end select

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

**jjz0512** · 08-26-2015, 11:58 AM

The directory and files never change. The Directory is N:\REDSIII\STRIPEReceive\. The files are always; FW_H1PD_OE-STRP1508252345220956.dat, FW_IMGC_RS-STRP1508252345220954.dat, & FW_ITXM_RS-STRP1508252345220955.dat with only the timestamp part of the file name changing for each. So FW_H1PD_OE-STRP1508252345220956.dat ot FW_H1PD_OE-STRP.csv will work using inport spec Import-FW_H1PD_OE-STRP to [H1PD XRAY Data] table.

**jjz0512** · 08-26-2015, 01:31 PM

I'm having trouble learning how to do this. So, lets try this again.

In C:\REDSIII\STRIPEReceive\, I have 3 files; FW_H1PD_OE-STRP1508252345220956.dat, FW_IMGC_RS-STRP1508252345220954.dat, & FW_ITXM_RS-STRP1508252345220955.dat with only the timestamp part of the file name changing for each. The location of the folder and files never never change, only the timestamp part of the file name changing for each.

I would like to autimatically load these daily files into there respective tables using there respective import specs. The import specs are looking for files called; FW_H1PD_OE-STRP.csv, FW_IMGC_RS-STRP.csv, folder\FW_ITXM_RS-STRP.csv

I would like to, either through a loop or by creating copies of these files from the .dat to .csv files so I can use the existing import specs. I would like to rename them, move the original .dat files to C:\REDSIII\ImportedFiles directory and delete the .csv file after importng.

Ideally I would like to do this via a macro/module code, but having difficulty do so as I'm new to Access, Macro and Modules. I can do this manually, but would really like to create a automation process since these daily files will be coming until 5/31/16.

Can you please help? Sorry if I'm not catching what has been shared so far.

Thanks again!!

**jjz0512** · 08-28-2015, 08:15 AM

I guess I'm not getting any more help with this. I'll just keep doing the daily manual thing.

**jjz0512** · 08-31-2015, 12:46 PM

Well, i'm still at it. I think I'm entering this right, but only get a Done wihen stepping through.
Here's my code; All 3 files; FW_H1PD_OE-STRP1508252345220956.dat, FW_IMGC_RS-STRP1508252345220954.dat, & FW_ITXM_RS-STRP1508252345220955.dat are in the directory daily.
'----------------
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, vTarg
'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
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

**jjz0512** · 08-31-2015, 12:50 PM

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

Changing .DAT to .CSV to Automatically Import into Tables

Thread Tools

Changing .DAT to .CSV to Automatically Import into Tables

Similar Threads

Import from Excel is changing every field Format to @

Changing Environ field automatically in report.

automatically refresh import from file.

Automatically import all objects from one database to other

Automatically changing a Lookup field when data is entered

Posting Permissions