import multiple txt files to different tables (1 Viewer)

L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
HI!
I am trying to create a automation. Onclick button will copy the text files to different tables respectively

below is the single text file to be copied to single table. Below is the code

Code: 
Private Sub cmdImport_Click()
    
    DoCmd.SetWarnings False
    
    Dim path1 As String
    
    path1 = "E:\Test1\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt"
        
    DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", "ICCSC01001026" & Format(Date, "yymmdd") & 111900, path1, False
            
    MsgBox "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & "imported" & " " & DCount("*", "ICCSC01001026" & Format(Date, "yymmdd") & 111900)
    
    DoCmd.SetWarnings True
    
End Sub

But while doing this I am getting below error

Runtime error 2498
An expression you entered is the wrong data type for one of the arguments
Click to expand...

kindly let me know what I am doing wrong. I have searched the forum for solution and not able to find one.

I also want to tell everyone that whatever I have know about access is from this forum and you guys support. Thank you all
 
Ranman256

Ranman256

Well-known member
Local time
Yesterday, 21:52
Joined
Apr 9, 2015
Messages
4,337
most likely, your 'spec' has the wrong datatype. You are trying to put a string into a date or number field.
Review/edit your spec and make sure.
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Hi! Ranman,
I have modified the data type for the table and the specification also, but I am still getting the same error.
mind if you can have a look at the attached db file and txt file.

kindly note the txt file name changes on daily basis so I have to import the data to the _temp table and then move it to the master and delete the temp table, which I will do later, but first my initial import is not working.

regards
 

Attachments

  • Import1.1.accdb
    752 KB · Views: 63
  • ICCSC01001026170908111900.txt
    115 bytes · Views: 60
J

JHB

Have been here a while
Local time
Today, 03:52
Joined
Jun 17, 2012
Messages
7,732
As the error message shows, the arguments are not correct.
You have the below:
DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", "ICCSC01001026" & Format(Date, "yymmdd") & 111900, path1, False
Click to expand...
Correct is, (your are missing the file extension and the "path1" is total wrong):
DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt", False
Click to expand...
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Thank you for your response, I am getting the below error

Run-time error '3078'
The Microsoft Access database engine cannot find the input table or query 'ICCSC01001026170909111900.txt'.
Make sure the exists and that its name is spelled correctly.
Click to expand...
I have renamed the file name with todays date instead of 170908" to "170909" to todays date.
However to note that the data is getting moved to the expected table "tb_26_temp" along with the above message

What I am doing wrong pls.
 
J

JHB

Have been here a while
Local time
Today, 03:52
Joined
Jun 17, 2012
Messages
7,732
Try the below:
DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", path1, False
Click to expand...
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
can I repeat the same code while I import the other txt files as well or I have to put the code differently.
I have to import daily 8 txt files to 8 tables on daily basis and then move the txt files to the archived folder.

I will give this a try first and experiment for the others too
 
J

JHB

Have been here a while
Local time
Today, 03:52
Joined
Jun 17, 2012
Messages
7,732
Does it import correct now?
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Yes JHB, I have done this for other txt files and it working as expected. Thank you for this

Now I am trying to get message for the deleted items which is displaying a pop-up msg as mentioned below

Code: 
    MsgBox a & " " & "rows deleted from tbl_26_temp" & _
    vbNewLine & b & " " & "rows deleted from tbl_34_temp" & _
    vbNewLine & c & " " & "rows deleted from tbl_37_temp" & _
    vbNewLine & d & " " & "rows deleted from tbl_51_temp"

but when I am putting the below code for the import I am getting an error for 1st txt file.

Code: 
MsgBox "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt" & " " & DCount("*", "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt")
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Hi! I have finally able to put the code in place to get this working, however there is one issue when there is no text files in the folder I get an error message. Here I am trying to put a pop-up message that the text files are not placed in the folder for the user to know and take action
below is the code

Code: 
Private Sub cmdDelete_Update_Click()

    Application.Echo False
    DoCmd.SetWarnings False
    
    Dim sql1 As String, sql2 As String, sql3 As String, sql4 As String
    Dim tab1 As String, tab2 As String, tab3 As String, tab4 As String
    Dim a As Long, b As Long, c As Long, d As Long

    tab1 = "tbl_26_temp"
    tab2 = "tbl_34_temp"
    tab3 = "tbl_37_temp"
    tab4 = "tbl_51_temp"
    
    a = DCount("*", "tbl_26_temp")
    b = DCount("*", "tbl_34_temp")
    c = DCount("*", "tbl_37_temp")
    d = DCount("*", "tbl_51_temp")
    
    sql1 = "Delete * from tbl_26_temp ;"
    sql2 = "Delete * from tbl_34_temp ;"
    sql3 = "Delete * from tbl_37_temp ;"
    sql4 = "Delete * from tbl_51_temp ;"

    DoCmd.RunSQL sql1
    DoCmd.RunSQL sql2
    DoCmd.RunSQL sql3
    DoCmd.RunSQL sql4

    MsgBox a & " " & "rows deleted from tbl_26_temp" & _
    vbNewLine & b & " " & "rows deleted from tbl_34_temp" & _
    vbNewLine & c & " " & "rows deleted from tbl_37_temp" & _
    vbNewLine & d & " " & "rows deleted from tbl_51_temp"

    Dim path1 As String, path2 As String, path3 As String, path4 As String

    path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt"
    path2 = "E:\Import\Folder1\Daily download folder\ICCSC01001034" & Format(Date, "yymmdd") & 111900 & ".txt"
    path3 = "E:\Import\Folder1\Daily download folder\ICCSC01001037" & Format(Date, "yymmdd") & 111900 & ".txt"
    path4 = "E:\Import\Folder1\Daily download folder\ICCSC01001051" & Format(Date, "yymmdd") & 111900 & ".txt"
    
    DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", path1, False
    DoCmd.TransferText acImportDelim, "34_Specification", "tbl_34_temp", path2, False
    DoCmd.TransferText acImportDelim, "37_Specification", "tbl_37_temp", path3, False
    DoCmd.TransferText acImportDelim, "51_Specification", "tbl_51_temp", path4, False

    MsgBox DCount("*", "tbl_26_temp") & " " & "rows imported from file " & "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_34_temp") & " " & "rows imported from file " & "ICCSC01001034" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_37_temp") & " " & "rows imported from file " & "ICCSC01001037" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_51_temp") & " " & "rows imported from file " & "ICCSC01001051" & Format(Date, "yymmdd") & 111900

    Application.Echo True
    DoCmd.SetWarnings False
    DoCmd.SetWarnings True
  
End Sub

I am not sure where can I put the code to prompt for the pop-up msg
 
isladogs

isladogs

MVP / VIP
Local time
Today, 02:52
Joined
Jan 14, 2017
Messages
18,247
Whilst its always better to run code that is error free, sometimes its acceptable to get Access to ignore an error if there are no side effects.

You've not actually said which error occurs but are implying that if you ignore it, all is well.

You really ought to have error handling in your code so lets do now.
In the code below I'll assume its error 53 - modify as necessary to the error number you get ....

Code: 
Private Sub cmdDelete_Update_Click()

[COLOR="Red"]On Error GoTo Err_Handler[/COLOR]

    Application.Echo False
    DoCmd.SetWarnings False
    
    Dim sql1 As String, sql2 As String, sql3 As String, sql4 As String
    Dim tab1 As String, tab2 As String, tab3 As String, tab4 As String
    Dim a As Long, b As Long, c As Long, d As Long

    tab1 = "tbl_26_temp"
    tab2 = "tbl_34_temp"
    tab3 = "tbl_37_temp"
    tab4 = "tbl_51_temp"
    
    a = DCount("*", "tbl_26_temp")
    b = DCount("*", "tbl_34_temp")
    c = DCount("*", "tbl_37_temp")
    d = DCount("*", "tbl_51_temp")
    
    sql1 = "Delete * from tbl_26_temp ;"
    sql2 = "Delete * from tbl_34_temp ;"
    sql3 = "Delete * from tbl_37_temp ;"
    sql4 = "Delete * from tbl_51_temp ;"

    DoCmd.RunSQL sql1
    DoCmd.RunSQL sql2
    DoCmd.RunSQL sql3
    DoCmd.RunSQL sql4

    MsgBox a & " " & "rows deleted from tbl_26_temp" & _
    vbNewLine & b & " " & "rows deleted from tbl_34_temp" & _
    vbNewLine & c & " " & "rows deleted from tbl_37_temp" & _
    vbNewLine & d & " " & "rows deleted from tbl_51_temp"

    Dim path1 As String, path2 As String, path3 As String, path4 As String

    path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & 111900 & ".txt"
    path2 = "E:\Import\Folder1\Daily download folder\ICCSC01001034" & Format(Date, "yymmdd") & 111900 & ".txt"
    path3 = "E:\Import\Folder1\Daily download folder\ICCSC01001037" & Format(Date, "yymmdd") & 111900 & ".txt"
    path4 = "E:\Import\Folder1\Daily download folder\ICCSC01001051" & Format(Date, "yymmdd") & 111900 & ".txt"
    
    DoCmd.TransferText acImportDelim, "26_Specification", "tbl_26_temp", path1, False
    DoCmd.TransferText acImportDelim, "34_Specification", "tbl_34_temp", path2, False
    DoCmd.TransferText acImportDelim, "37_Specification", "tbl_37_temp", path3, False
    DoCmd.TransferText acImportDelim, "51_Specification", "tbl_51_temp", path4, False

    MsgBox DCount("*", "tbl_26_temp") & " " & "rows imported from file " & "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_34_temp") & " " & "rows imported from file " & "ICCSC01001034" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_37_temp") & " " & "rows imported from file " & "ICCSC01001037" & Format(Date, "yymmdd") & 111900 & _
    vbNewLine & DCount("*", "tbl_51_temp") & " " & "rows imported from file " & "ICCSC01001051" & Format(Date, "yymmdd") & 111900

    Application.Echo True[COLOR="red"]
   'remove next line - its already false[/COLOR]
    DoCmd.SetWarnings False[COLOR="Red"] '<==SCRAP THIS[/COLOR]
    DoCmd.SetWarnings True

[COLOR="red"]Exit_Handler:
    Exit Sub

Err_Handler:
    If err=53 Then 
         Resume Next 'tells the routine to ignore error and continue
    Else
         MsgBox "Error " & err.Number & "  cmdDelete_Update_Click procedure : " & vbcrlf & err.Description[/COLOR]
  
End Sub

I am not sure where can I put the code to prompt for the pop-up msg
Click to expand...

Without reading the whole thread, I'm not sure what this is about so I've not answered it
 
J

JHB

Have been here a while
Local time
Today, 03:52
Joined
Jun 17, 2012
Messages
7,732
lookforsmt said:
.. however there is one issue when there is no text files in the folder I get an error message. Here I am trying to put a pop-up message that the text files are not placed in the folder for the user to know and take action
below is the code
Click to expand...
If me, I would check for the all the files before I started the import, using the Dir function.
And also using a table to hold the string of the path, the first and last part of the file name, the names of the import specification and the table names.
And then use a loop and a recordset.
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Thank you Dear Colin for the code. I was great difficulty of putting the error code, largely due to my lack of vba knowledge (I would be glad if anyone can teach me step by step. I am just trying to do copy/paste codes from the net without understanding the vba code what it is purpose is for)

Uncle Gizmo, I have seen the link and like the idea of showing the pop-up of number of files in the folder rather then just popup message of zero records transferred. But I am looking for a simple solution.

Dear JBH, I was looking for a simple solution, but I guess I must admit I did not think of the errors/issues that I would be encountering while doing this.

Again now I am facing another issue and that is the last line of my text file has "-End of Report-"

Due to this it creates a error conversion table telling me the data is mis-match.

How can I exclude this line or before I transfer the text run it through a sql code to exclude this line.

PS: pls excuse if I have accidently hurt anyone here
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
HI! I have done using the TableDef option to delete the import errors table

ImportErrors table is created since the txt file has below data in the last line

"-,EndOfReport,-,-,-,-"

To delete the table I have used the below code.
Is there another way like while transfer exclude the last line Or
Execute a query before the transfer to exclude the last line Or
etc.

Code: 
    Dim tbl As TableDef
Dim db As Database

Set db = CurrentDb
For Each tbl In db.TableDefs
  If InStr(tbl.Name, "importerrors") > 0 Then
     db.Execute "drop table " & tbl.Name
  End If
Next

End Sub

I have attached the db
 

Attachments

  • Import1.4.accdb
    656 KB · Views: 73
isladogs

isladogs

MVP / VIP
Local time
Today, 02:52
Joined
Jan 14, 2017
Messages
18,247
Hi

I've had a look at your db and made some suggestions for changing code in your form.

As previously mentioned, it can be OK to bypass errors in some cases.
However this code of yours isn't a good idea:

Code: 
If err > 0 Then
         Resume Next 'tells the routine to ignore error and continue
    Else
         MsgBox "Error " & Err.Number & "  cmdDelete_Update_Click procedure : " & vbCrLf & Err.Description
    End If

Error messages are there for a good reason - don't bypass them all!
I got error 3044 so used this ...

Code: 
If err =3044 Then
         Resume Next 'tells the routine to ignore error and continue
    Else
         MsgBox "Error " & Err.Number & "  cmdDelete_Update_Click procedure : " & vbCrLf & Err.Description
    End If

Have a look at my various comments.
As I've no files to test this against, I'm sure there are other things can could be improved.

I would also caution against deleting the ImportErrors table - again if it occurs, its there to advise you that something needs fixing

As for your End Of Report error, perhaps you could try one of these:
a) OMIT the final line from your text file!
b) try adding a single quote at the start of that line & see if its then treated as a comment ...???? Unlikely but hard to tell without trying it!
c) use your CountLines code to import all except the last line (more complicated!)

However, a word of warning - don't just copy code from various sources without understanding what each item does.
 

Attachments

  • Import1.4-CR.accdb
    752 KB · Views: 69
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
Thank you Colin, really appreciate your help and making me understand the code.

First of all my apologies for the delay response, got tied up with personal work.

Although my response are below, but it seems the more i try to learn vba the more i am sinking in. It seems to be a difficult subject.

My response in line with your comments
1) The txt file is already uploaded earlier.

2) All the txt files have the same nos of columns and nos of rows. Will this would be still an issue if I use the importerrors code?

3)
a) There is possibility the user might accidently delete other lines and this can lead to another level issue
b) I have no clue how this is done, but i will try and send you the code
c) I am not sure how this is done, but i will try and send you the code

4) This is because I have no knowledge on vba code. But yes I am interested in learning access vba. Can you direct me to any particular trainings that can make me get started.

There are so many things I can now see that can be improved in this. I am trying to learn from you helpful people for your guidance
Looking at the db, I can see that there are plenty of things I can improve on this like:
1) The Update_Master button diplays yes/no for each update. It should just popup one yes/no msg and display all the rows updated once said "yes", else it should cancel on "no"
2) If the text files are for different dates in the target folder, then it should throw a popup msg, "Text files date invalid" and move the text files to archived folder
3) If the text files are less than the actual nos of files(4) then it should still go ahead and import into the temp table and then upload to master table

for the time being this is what I want to do. I will try and send you my codes, let me know if they are good enough

This is just to tell you that I want to really learn access vba desperately
 
isladogs

isladogs

MVP / VIP
Local time
Today, 02:52
Joined
Jan 14, 2017
Messages
18,247
A very good resource for learning Access are the videos by Steve Bishop on You Tube.
There's probably over 100 in all going from Beginners through to Advanced in a structured course
Do a You Tube search and you'll find them

Fellow AWF member Tony Hine (Uncle Gizmo) also has a number of very good videos online

There are also many books out there at different levels - just be sure to choose something at the right level for you
 
L

lookforsmt

Registered User.
Local time
Today, 05:52
Joined
Dec 26, 2011
Messages
672
HI! how can i write the below code if the text file keeps on changing on daily basis with date as todays date

text file name: ICCSC01001026170915111900 Where the

ICCSC01001026 is always constant
170915 is the date (format "ddmmyy") which changes everyday
111900 changes everyday
Click to expand...

how can i put the above in the below ccode

Code: 
Dim path1 As String
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & [U]111900[/U] & ".txt"

and also in the pop message the actual file name should appear.
Code: 
MsgBox DCount("*", "tbl_26_temp") & " " & "rows imported from file " & "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & _
 
isladogs

isladogs

MVP / VIP
Local time
Today, 02:52
Joined
Jan 14, 2017
Messages
18,247
lookforsmt said:
HI! how can i write the below code if the text file keeps on changing on daily basis with date as todays date

text file name: ICCSC01001026170915111900 where the
ICCSC01001026 is always constant
170915 is the date (format "ddmmyy") which changes everyday
111900 changes everyday

how can i put the above in the below ccode

Code: 
Dim path1 As String
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & [U]111900[/U] & ".txt"

and also in the pop message the actual file name should appear.
Code: 
MsgBox DCount("*", "tbl_26_temp") & " " & "rows imported from file " & "ICCSC01001026" & Format(Date, "yymmdd") & 111900 & _
Click to expand...

Change it as follows:

Code: 
Dim path1 As String
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") &[COLOR="Red"] "111900.txt"[/COLOR]

I don't believe you can underline part of a filename so I've removed that

Code: 
MsgBox DCount("*", "tbl_26_temp") & " rows imported from file " & "ICCSC01001026" & Format(Date, "yymmdd") & "111900.txt"
[/QUOTE]
 
You must log in or register to reply here.

Similar threads

N
Solved Mass Importing Txt Files
Replies
14
Views
437
ebs17
E
M
DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names after conversion to Office 365
Replies
12
Views
205
Gasman
Gasman
M
Solved Converting txt file to xlsx
Replies
5
Views
408
miacino
M
N
Importing Txt File
Replies
12
Views
603
gemma-the-husky
gemma-the-husky
M
Multiple attachments and a report in the same email
Replies
15
Views
981
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom