Importing multiple text files into one table


Hello, I'm very new to Access and writing code (this is day one). I am trying to import multiple text files from one folder into a table and add a new column that identifies the file name.

I've tried to adapt the following code to my needs, but Access 2010 apparently does not like FileSearch. I apologize if my question is too simple for this forum.

Function ImportCSVFiles() Dim FilesToProcess As Integer Dim i As Integer Dim bArchiveFiles As Boolean Dim sFileName As String Dim sOutFile As String Const TOP_FOLDER = "H:Test" 'adjust folder name to suit Const ARCHIVE_FOLDER = "H:TestImported" 'adjust folder name to suit Const DEST_TABLE = "tblUsers" 'change to suit Const IMPORT_SPEC = "CSV_Import_Spec" 'change to suit Const PATH_DELIM = "" 'set to False if you DON'T want to move imported files to new folder bArchiveFiles = True 'the FileSearch object lets you search a folder and, optionally its subfolders, 'for files of a defined type. It loads the names of all found files into an array, 'which we can use to import those files. With Application.FileSearch .NewSearch .LookIn = TOP_FOLDER .SearchSubFolders = False 'we only want to search the top folder .FileName = "*.csv" 'change this to suit your needs .Execute FilesToProcess = .FoundFiles.Count 'check that files have been located. If not, display message and exit routine. If FilesToProcess = 0 Then MsgBox "No files found, nothing processed", vbExclamation Exit Function End If For i = 1 To FilesToProcess 'import each file DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, _ .FoundFiles(i), True 'archive the imported files If bArchiveFiles Then 'code for archiving imported files... sFileName = StrRev(Left(.FoundFiles(i), Len(.FoundFiles(i)) - 4)) sFileName = Left(sFileName, InStr(1, sFileName, PATH_DELIM) - 1) sFileName = StrRev(sFileName) sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " _ & Format(Date, "yyyymmdd") & ".csv" FileCopy .FoundFiles(i), sOutFile Kill .FoundFiles(i) End If Next i End With End Function 'The StrRev function reverses a text string. We are using it here to simplify 'extracting the file name: once the full path is reversed, we can pull out everything 'to the left of the first path delimiter. Reversing this string gives us the file name. 'Note: VBA has a StrReverse function that you can use instead of this custom function. Function StrRev(sData As String) As String Dim i As Integer Dim sOut As String sOut = "" For i = 1 To Len(sData) sOut = Mid(sData, i, 1) & sOut Next i StrRev = sOut End FunctionThanks


Sponsored Links:



I searched this forum and found vba code to import multiple text files into ONE Access table, but I need mine to be kept separate. Also, I didn't understand the copyrighted code for that posted procedure. Any suggestions? Thank you.




I'm working on a project in which I have to import a text file into a table. I'm importing the text using the following code in the button's on click event:

DoCmd.TransferText acImportDelim, "TEXT", "tblExisting", "c:textfile.txt", True, ""

This appends the records to the table. My question is, where can I find the code that brings up the open document window so the user can choose the text file to be imported?

Thanks!

Judy




I am looking to Import and append multipe ASCII Comma Quote delimited files into one Access table. The files can be imported one at a time but I would like to have this process automated as much as possible. Is there some sample code that I can reference to accomplish this task? Thanks in advance for your help.




Hi all,
this is the first time i write in a forum.I've a problem with VBA and i hope someone of you can help me.
I use this piece of code to import some csv files into one new access table.

function ImportCSV()
dim input_file as variant

input_file = Dir("c:temp*.csv", vbSystem)

do until input_file = vbNullString
Docmd.TransferText acImportDelim, "spec", "table name","c:temp" & input_file, False
loop
end function

Is there a way to insert into the new table also the name of the origin sheet?(like a field)
Thanks a lot in advance,
jcvd_76




Hi,

I have a text file with comma separated values. What I want is a single access query to import this text file into an access table. I know this can be done through import text wizard in access - but what I need is a single query.
I have done the same thing for transferring oracle data to an access table - but for text file to access, I am getting -7778 error.

This is the query I have written
SELECT * into MY_ACCESS_TBL from [odbc;Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=MY_TEXT_FILE_PATH;].[MY_TEXT_FILE.txt];

Please help!!

Regards,
Suneesh




Hi,

I want to import multiple text files to Access 2007 Table called "Data_Import" using VBA code. The text file names are not same so I the are located in a folder. So I should be able to select them through windows. When i import the text file, the first Row of the table should contain the file path along with the file name and the second row should just contain the file name and the data from the text file should be appended after that. The same procedure should be followed for subsequent text files.

Any Help would be appreciated.

Thanks
rk




I have hundreds of text files (CSV) that I need to import into Access and export as dbf files. I can do it one at a time, but it's painstakingly slow. I can concatenate all the files into one text file before import, but when I do that I lose the ability to tell what value is associated with what original file (which is important... think of the file names as dates and the data is XYZ positions).

What I would like to do is write a macro that imports all the text files in a directory into separate tables, and exports each as a dbf, preserving the original filenames. Or, imports all the texts files into one table, but adds the attribution of the original file name to each record (so I can query by file name later).

Any thoughts?

Thanks.




Hi there,
Can someone please look at my code and help me out help.

I am trying to convert over VB code to work with VBA in Access 2003. The code to list all of the available text files in a particular folder is working fine, and they get populated in a listbox called lstFiles, the problem is when I click on one of the textfile from this listbox I want it to show the contents of the text file in the listbox below named lstFileLines. I get the Compile Error: Method or data member not found, and highlights Text from lstFiles.Text on the following line of code:
Set oFile = fso.GetFile(txtFolder.Text & lstFiles.Text)

Here is all of my code, I am ultimately trying to import this text file into a table named CardTest, in case you notice any other code errors that I may have.

Option Compare Database
Private Sub cmdClear_Click()
Me.lstFileLines = ""
Me.lstFiles = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmImportCards", acSaveYes
End Sub
Private Sub cmdGetFiles_Click()
'Declaring the following variables
Dim fso As Scripting.FileSystemObject
Dim oFolder As Scripting.Folder
Dim oFile As Scripting.File
Dim i As Long

'Instantiating the filesystem object
Set fso = New Scripting.FileSystemObject

'Clearing the file list
'Me.lstFiles = ""

txtFolder.SetFocus
txtFolder.Text = "E:Cards1980-81O-Pee-Chee"

If Len(txtFolder.Text) > 0 Then

If Right(txtFolder.Text, 1) "" Then
txtFolder.Text = txtFolder.Text & ""
End If

'Checking to see if the folder name typed in the textbox actually exists
If fso.FolderExists(txtFolder.Text) Then
'Setting the oFolder object to what is in the textbox
Set oFolder = fso.GetFolder(txtFolder.Text)

'Looping through the files in the folder object
For Each oFile In oFolder.Files
'Checking the file extension of the file
If Len(oFile.Name) >= 4 Then
'If the file has a txt file extension then add the files
If UCase(Right(oFile.Name, 4)) = ".TXT" Then
lstFiles.AddItem oFile.Name
End If
End If
Next

Else
MsgBox "This folder does not exist."

End If
End If

'Removing the oFile object from memory
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing

End Sub

Private Sub cmdImport_Click()

Dim i As Long, iPos As Long
Dim sLine As String
Dim sCardNo As String, sFirstName As String, sLastName As String
Dim sRookieCard As String, sAdditionalDescription As String
Dim sYearId As String, sSetId As String, sSubsetId As String

Me.Refresh
DoEvents

If lstFileLines.ListCount > 0 Then
m_oConnection.Open

If m_oConnection.State = adStateOpen Then
sSetId = InputBox("What is the Set #?", "Set", "0")
sSubsetId = InputBox("What is the Subset #?", "Set", "0")
sYearId = InputBox("What is the Year Id#?", "Set", "0")

For i = 1 To lstFileLines.ListCount
sLine = lstFileLines.ListCount(i - 1)
Debug.Print sLine
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sCardNo = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sFirstName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sLastName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sRookieCard = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))
sAdditionalDescription = Replace(sLine, """", """""")
End If
End If
End If
End If
ImportRecord sCardNo, sFirstName, sLastName, sRookieCard, sAdditionalDescription, sSetId, sSubsetId, sYearId
Next i
m_oConnection.Close
End If
End If

End Sub


'Private Sub Form_Load()

'm_sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:CardsAnotherTry.mdb"

'Set m_Connection = New ADODB.Connection
'm_oConnection.ConnectionString = m_sConnectionString
'End Sub
Private Sub lstFiles_Click()
Dim fso As Scripting.FileSystemObject
Dim oStream As Scripting.TextStream
Dim oFile As Scripting.File

Set fso = New Scripting.FileSystemObject

Set oFile = fso.GetFile(txtFolder.Text & lstFiles.Text)

Set oStream = oFile.OpenAsTextStream(ForReading)

'Me.lstFileLines = ""

Do While Not oStream.AtEndOfStream
lstFileLines.AddItem oStream.ReadLine
Loop

oStream.Close

If lstFileLines.ListCount > 0 Then
cmdImport.Enabled = True
End If


End Sub

Private Sub ImportRecord(ByVal sCardNo As String, ByVal sFirstName As String, ByVal sLastName As String, ByVal sRookieCard As String, ByVal sAdditionalDescription As String, ByVal SetId As String, ByVal SubsetId As String, ByVal sYearId As String)
Dim sSQL As String
Dim iSetId As Integer
Dim iSubsetId As Integer
Dim iYearId As Integer

iSetId = Val(sSetId)
iSubsetId = Val(sSubsetId)
iYearId = Val(sYearId)

If Len(sCardNo) 0 And Len(sFirstName) 0 And Len(sLastName) 0 Then
sSQL = "INSET INTO CARDTEST (CardNo, FirstName, LastName, RookieCard, AdditionalDescription, SetId, SubsetId, YearId)VALUES (""" & sCardNo & """, """ & sFirstName & """, """ & sLastName & """, """ & sRookieCard & """, """ & sAdditionalDescription & """, " & iSetId & ", " & iSubsetId & ", " & iYearId & ")"
m_oConnection.Execute sSQL
End If
Debug.Print sSQL

End Sub


Thanks




Hello,

I am after some help on writing soem VBA to import multiple text sheets into 1 access table. The files are in the same format and they will all be in one folder and there will be nothing else in folder.

Any help on this would be great as I need to import lots of files on a rolling basis.

Cheers




This may be a bigger project than I first thought but what I need to do is import a text file into an existing table. First of all I have created a form with a listbox that I would like to be able to store the text file in. How do I go about loading the text file from my hard drive to the listbox? I then need to parse the text file so that it will import into the proper fields in my table, and finally get the users responses and append these numbers into the YearId field, SetId field, and the SubsetId field, depending on what the user choses as the correct YearId, SetId and SubsetId.

The tricky part is with the text file, all of the formats can be as follows:
CardNum ex: 22
FirstName ex: Babe
LastName ex: Ruth
Rookie ex: R (If it is a rookie card it is always just an R, but not all of the cards in the text file are rookie cards)
AdditionalDescriptionex: Record Breaker

My text files are all space delimited

The table in my db is named ImportingCards and has the following fields:
CardId, AutoNum (will uniquely identify each single card)
CardNum, Text (because cards can be numbered with letters or numbers or both)
FirstName, Text
LastName, Text
Rookie, Yes/No
AdditionDescription, Text
YearID, Number (have all of the years numbered in a Year Table)
SetId, Number (have all of the sets numbered in a Set Table)
SubsetId, Number (have all of the subsets numbered in a Subset Table)


I know that it will be quite a bit of work but could someone please start me in the right direction and maybe break down how I should tackle this, I am just a junior programmer.

Thanks
So much




Hey all,

Before i start. This is one amazing community and proud to be here. I have learnt access in 4 days 4 hours each day . So i just learnt the basics of it i guess. The reason why i am here is to eliminate the excel sheets i use.

1. I know how to import the excel sheet, however in this case im trying to be specific in this case. I have about a full month report in one excel file with multiple sheets having one complete day records. i was wanting to import all the sheets(feb1,feb2,feb3 etc.) into one table. I have seen some vb codes in this site but however, im not really sure how and where to put them and run them.
In other words, What is the step by step procedure to make sure all the multiple sheets come into one table. Point a to b (starting from entering the code, where do i go to put the code and how to run it each time).

2. About linking excel sheets, im a little confused. Once i link up the excel sheet to access and if i try to update the excel with more transactions will access update that or do i have to edit/add the data itself in access?.. this in relation to the 1st above point. Each day etc transactions.

I have attached a example excel sheet which i use everyday.
Appreciate everybody's assistance in advance.




I am trying to import a TEXT file into ACCESS using a macro (using import delimited). But what happens is that it creates a table with only one field and also generates an error table which seems to state that Row 2 is "unparseable"

Now when I import the same TEXT file using File->Get External Data-> Import (and then choosing a file to import), it does it without any problem. Would appreciate an insight and solution to this problem. Thanks.




Hi. I am trying to set up a macro to import many text files into a table. I have written a macro to import the text file, but I not only need to change the text file name, I would also like a way to import ALL of the text files without being prompted for each one. (There are thousands of files.) I could get all of the file names into a table to reference if that would help. Does anyone have any suggestions on where to start with this? Thank you so much for any help!




hello

i have one text file and i want to have one button and upload text file into access table
i thing that want a vba code

can you help me????




Hi,

I am tryig to import many text files into MS Access. They are all tab delimited and have NO column headers, but they all have varying number of columns and data types.

Rather than using the Import wizard for every single text file, is there a way to write some code in VBA to upload the file, which I can then just edit as neccessary for each type of file?

Thanks for your help.




Hi,

I have about 100 text files of the following format:

Name: John Doe
Address: 123 Main Street
Zip: 55555

Name: Jill Doe
Address: 234 Clyborne
Zip: 66666

I need to import all these text files into a Single Table in Access. Obviously, the table would look like this:
Name Address Zip
John Doe 123 Main Street 55555
Jill Doe 234 Clyborne 66666

Due to my limited knowledge in Access, I don't know how to get this done nor use the right terminology to search (I tried in this forum but was unsuccessful).

Would appreciate if anyone can point me in the right direction to solve this problem please. Thanks!




Hi.

I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.

I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.


I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.

Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.


I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.

my worksheet is in the following format

ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....

All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)

Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)

thank you very much.


FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.


Sub Import_multiple_excel_files()

Const strPath As String = "C:Price DataNYMEX2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")

While strFile ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub




I have a text file that has 37 lines in it with two columns of information.
I would like to be able to import certain rows into the table.
The way the file is created it has the would be column names in what would be column 1 instead of on the first row.
a few of the 37 lines has the item name field on one line and on the next the entry for that record.

is there some simple code that can be crafted to easily import these files?

thanks,
ed




I would like to use a macro to Import (Transfer Text File) a text file into one of my tables, then delete the text file (on my C: drive) so it won't be imported a second time by mistake. Can someone help me with this?

Thanks.