11-05-13, 17:08 #1Registered User
- Join Date
- Nov 2013
Unanswered: Automating a Fixed Width Import from Text and CSV file
I'm using Access 2010
I've created a couple of databases at my new job for maintaining files that are brought in on a daily basis. Most of my work so far has just been with queries to update and format the data as it comes in.
We receive multiple text files and csv files each day. The text files go into one database and csv files in another.
Both files are around 2000 characters long, and are imported using a saved fixed width specification. It hasn't been too much of a hassle to import these manually as they come in, but things have picked up recently and it is becoming time consuming to do each of these files manually.
I'm looking for a way to "automate" this a bit as the import steps are the same each time. I haven't been able to use the "Saved Imports" feature as the files come with different names so I would like a way to still have the "Browse" feature open so that the file can be selected manually.
The Import Steps are:
External Data > From Text > Import into new table > Advanced > Specs > Open > Select Primary Key > Name Table(this is always the same).
I'm not familar with VB, but I'm always willing to learn and try and get my hands dirty.
Once this part and built, I will be having other people begin Importing the daily files into this database, so I'd like the end product to be as simple as possible where all thats needed is to select the file being imported and then all the other steps above are done behind the scenes.
Thanks for any help!
11-06-13, 05:39 #2Registered User
- Join Date
- Nov 2012
I have never done this before, but I think in VBA a function DoCmd.TransferText, acImportFixed would be appropriate.
If you want to use VBA you may find this overview helpful:
Google around and you will find what you'll looking for, this seemed to contain more that enough information:
TransferText - specificationname? - Xtreme Visual Basic Talk
I hope this helps!
11-06-13, 10:22 #3Moderator
Provided Answers: 14
- Join Date
- Mar 2009
Asserting that the structure of the files to be imported is constant (i.e. that the number of columns to be imported is always the same), you could build your own File Importation system. Here's an example:
Option Compare Database Option Explicit Private Function GetName(ByVal FilePath As String) As String Dim x As Long x = InStrRev(FilePath, "\") + 1 FilePath = Mid(FilePath, x) x = InStrRev(FilePath, ".") GetName = Left(FilePath, x - 1) End Function Public Sub Import(ByVal TableName As String, Optional ByVal FilePath As String, Optional ByVal Separator As String, Optional ByVal Append As Boolean) Const c_SQL As String = "SELECT * INTO @Table FROM @STable WHERE 1 = 2;" Const c_Prompt As String = "A table named @N already exist in the database." & vbNewLine & _ "Do you want to replace the existing table?" Dim strFileName As String Dim strTableName As String strFileName = SelectFile(FilePath) If Len(strFileName) > 0 Then If Append = True Then strTableName = TableName Else strTableName = GetName(strFileName) If DCount("*", "MSysObjects", "Name='" & strTableName & "'") > 0 Then Select Case MsgBox(Replace(c_Prompt, "@N", strTableName), vbInformation + vbYesNoCancel, "Import: Existing table") Case vbYes DoCmd.DeleteObject acTable, strTableName Case vbNo strTableName = strTableName & "_" & Format(Now, "yyyymm_hhnnss") Case vbCancel Exit Sub End Select End If CurrentDb.Execute Replace(Replace(c_SQL, "@Table", strTableName), "@STable", TableName) End If ImportFile strFileName, strTableName, Separator End If End Sub Private Sub ImportFile(ByVal FilePath As String, ByVal TableName As String, ByVal Separator As String) Dim rst As DAO.Recordset Dim intHandle As Integer Dim strLine As String Dim varItems As Variant Dim i As Long Set rst = CurrentDb.OpenRecordset(TableName, dbOpenDynaset) intHandle = FreeFile Open FilePath For Input As #intHandle Do Until EOF(intHandle) Line Input #intHandle, strLine If Len(Separator) > 0 Then varItems = Split(strLine, Separator) Else varItems = Splinter(strLine) End If With rst .AddNew For i = 0 To UBound(varItems) rst.Fields(i).Value = varItems(i) Next i .Update End With Loop rst.Close End Sub Private Function SelectFile(Optional ByVal Path As String) As String Dim Fdlg As FileDialog Set Fdlg = Application.FileDialog(msoFileDialogOpen) With Fdlg .AllowMultiSelect = False .InitialFileName = Path .Filters.Add "CSV files", "*.csv;*.txt", 1 If .Show = -1 Then SelectFile = .SelectedItems(1) End With End Function Private Function Splinter(ByVal Line As String) As Variant Const c_Positions As String = "1,12,20,30,255" ' ' Instead of the constant c_Positions, it's also possible to use a table ' (e.g. Tbl_Positions: 'CREATE TABLE Tbl_Positions ( Position LONG );') ' that contains the position of each data column. ' In such a case, the array varPos can be initialized with: ' ' Dim rst As DAO.Recordset ' Set rst = CurrentDb.OpenRecordset("Tbl_Positions", dbOpenSnapshot) ' varPos = rst.GetRows(999) ' rst.Close ' Set rst = Nothing ' Dim varPos As Variant Dim var As Variant Dim i As Long varPos = Split(c_Positions, ",") ReDim var(0 To UBound(varPos) - 1) For i = 0 To UBound(varPos) - 1 var(i) = Trim(Mid(Line, varPos(i), varPos(i + 1) - varPos(i))) Next i Splinter = var End Function
Import "Tbl_Source","Initial Path","Separator",Append (True/False)
- "Tbl_Source" is the name of a table with the proper structure to receive the data being imported (this table must exist although it can be empty).
- "Initial Path" is the path where the file to be imported should be found.
- "Separator" is the character used to separate the data elements when the file to be imported is in the "pure csv format (urually: "," or ";" or vbTab). Leave this argument empty for a fixed-length file.
- Append is a boolean value (True or False). If set to True, the imported data will be appended to the table "Tbl_Source". If set to False, a new table will be created on the model of "Tbl_Source" and with the name of the imported file (e.g. is the imported file is "C:\Data.txt", the name of the table will be "Data"). If a table with the same name already exists, a dialog box will pop up and propose 3 options:
Yes: Replace the existing table.
No: append a TimeStamp to the base name of the new table (e.g. "Data" becomes "Data_201311_144418").
Cancel: Abort the importation process.
For fixed-length data files, you could create a table containing the position of each "column" instead of using the constant c_Position (see the remarks in the Splinter() function).
A more complex system that would be able to handle various type of importations could be created using a Class with several initialization parameters.Have a nice day!