Import multiple text files from folder & adding filename as record in column.

[Niriv]

Hello All,

I am newbie, do not have coding knowledge. Looking for help after searching around in web.

I am in need of importing multiple 100+ text files from a folder in a single table (Table1) designed as below specification, Since i would be importing multiple text files over the 52 weekly period i am thinking of to create a new table after every import.



Also want to add filename as record along with imported data so I can create correspondence/association of where the data came from.

I will be getting 100+ files every time, and have to run this weekly

Is there any solution available?

My text file has following format and delimited by pipe character

Field Name	Data Type
Destination	Text
Description	Text
Tariff	Number
Qty	Number
Unit	Text
Value	Numer

Thank you
Niriv

[Niriv]

I got a good start by using the code listed by RuralGuy in following post https://www.accessforums.net/import-...ally-2030.html.

With my import specification and table design was able to get all the text file in my identified table.

Now I need to get following 2 achieved,

1)add filename as record to associate the imported data.

my text file name format is like following

E_INDEL4_19052013.txt
E_INCCU1_19052013.txt

2) After adding filename as record split the file name in 3 more columns,

Type	Listing	Date
E	INDEL4	19052013
E	INCCU1	19052013

Thank you
Niriv

[RuralGuy]

Are you planning to make a new table for the FileName or simply another field/fields in the existing table? Do you need to parse the file name now or could you simply store the string and parse it later when you need to? Parsing this filename string is easy.

[Niriv]

Let me take a step back to explain.


As a prerequisite, i created a Table1 design (post #1) and specification "ICE", I am able to pull all text files from my folder. Along with this import I like to add file name as field in a column so i could associate the imported records.

After import of all text files and file name i want to split the file name as post # 2

Hope was able to explain.

Option Compare DatabaseOption Explicit
'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder1(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer
With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With
dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder1 = Left$(szPath, wPos - 1)
Else
BrowseFolder1 = vbNullString
End If
End Function
'*********** Code End *****************

Public Sub GetScores()
'-- Import all of the student scores to the tblScores table.
Dim OurFolder As String
Dim OurFile As String
'-- First have the user select the folder
OurFolder = BrowseFolder1("Select the folder that contains the scores.")
'-- Dir returns a ZLS ("") when no more files match the criteria
OurFile = Dir(OurFolder & "\*.txt")
Do While OurFile <> ""
'-- I created and saved the ScoreImportSpecs earlier.
'-- I also created the tblScores table with the following
'-- four text fields:
'-- FirstName, LastName, ClassCode, Score
DoCmd.TransferText acImportDelim, "ICE", "Table1", OurFile
'-- Get the next *.txt filename
OurFile = Dir
Loop
End Sub

[Niriv]

Attaching a sample db, I want to get table 1 populated like sample table 2.

[June7]

No need for second table. Build table and include fields for the parsed file name parts (TYPE, LISTING, FILEDATE) and import data to existing table. Then run UPDATE sql action to populate the 3 fields.

[Niriv]

I do not know whole lot of coding, my effort was for table 2 was to show a sample to others helping me to see how i want my table 1 to look.

[June7]

Okay, do you still need help with import to existing table followed by UPDATE actions to populate the name parts fields?

[Niriv]

Yes, I do need help.

So the current code brings all text file in my folder.

I need to bring the filename along with data and then split the file name as I i indicated in post # 2

Thank you

[June7]

Looks like you already have the import code. So to update the 3 fields, code following the TransferSpreadsheet line like:

CurrentDb.Execute "UPDATE table1 SET TYPE='" & Left(OurFile,1) & "', LISTING='" & Mid(OurFile, 3, 6) & "', FILEDATE=#" & DateSerial(Mid(OurFile, 14, 4), Mid(OurFile, 12, 2), Mid(OurFile, 10, 2)) & "# WHERE LISTING Is Null"

Assumes the file names will always follow the structure of the posted examples and FILEDATE is a date/time data type.

[Niriv]

Thanks a bunch, i got code running properly.

To my current project I got two more items

1) Is there a way to put a code in VB to check a folder where any text is size is less than 2KB and if there is no data after header row ; delete such files.

2) In my data set (Attached) ran into import error, i found why import errors have one of the city name is not correctly defined in data set (Attached)

It should have been NUREMBERG|

Instead in my data set is N|RNBERG (NUREMBERG)|

because of this my data set is throwing off in columns.

Pleas help

[Niriv]

Another help, Is there a way I could add vlookup in same code?

I have table 'Location Code', which also has field 'Listing' similar to one we created in update command 'LISTING'

Can we do vlookup based on listing columns from location code table and bring following fields



Location Desc
Country Code
State

[Niriv]

For my post #12, I am trying to add below to my code above but throwing up and error Same table 'table1 referenced as both the source and destination in make table query

CurrentDb.Execute "SELECT table1.*, [Location Code].[Location Desc], [Location Code].[Country Code], [Location Code].State INTO [Table1]FROM table1 LEFT JOIN [Location Code] ON table1.Listing = [Location Code].Listing;"

[June7]

Can use FileLen() function to return file size and Kill to delete file: http://word.mvps.org/FAQs/MacrosVBA/DeleteFiles.htm

If FileLen(OurFile)/1000 <2 Then
Kill OurFile
End If

Not seeing the NUREMBERG issue in the files.

CurrentDb.Execute is only used with action SQL statements (UPDATE, DELETE, INSERT, etc). A SELECT is not an action statement. It is used to open a recordset object in VBA.

Explore the domain aggregate functions (DLookup, DSum, DCount, etc.). Access Help has guidelines.

[Niriv]

Thank you, i was able to implement SQL command to get

Further to FileLen()

I created a separate module, On the first run it worked well, then after adding more files to my folder it did not do anything neither got any error.

Sub ICEDEL()
'-- Import all of the student scores to the tblScores table.
Dim MyFolder As String
Dim MyFile As String
'-- First have the user select the folder
MyFolder = BrowseFolder1("")
'-- Dir returns a ZLS ("") when no more files match the criteria
My = Dir(MyFolder & "\*.txt")
Do While MyFile <> ""
'-- I created and saved the ScoreImportSpecs earlier.
'-- I also created the tblScores table with the following
'-- four text fields:
'-- FirstName, LastName, ClassCode, Score
If FileLen(MyFile) / 1000 < 1 Then
Kill MyFile
End If
'-- Get the next *.txt filename
MyFile = Dir
Loop
End Sub


What could be issue it did not run second time properly.?

Thnx