Using MS Access 2013 to move a large input .txt file with fixed length fields to a SQL Server table

I am developing an MS Access 2013 application with a SQL Server backend for a client.  As part of the project I have to write conversion logic to transform their legacy data into the new format, resident in SQL Server tables.

I wrote and tested my conversion routines with a subset of the legacy data provided by the client.  Everything worked fine.
The client is supplying the data as .txt file.  Within the txt file the fields are fixed length.  I created Import Specs for each data set being converted and then imported each file to be converted using this construct.
Public Sub importThisOne(passedImportFIle As String, _
                         passedImportTable As String, _
                         passedImportSpec As String, _
                Optional passedClearFileBeforeImporting As Boolean = True)
'
If passedClearFileBeforeImporting = True Then
    deleteString = " delete * from " & passedImportTable
    DoCmd.SetWarnings False
    DoCmd.RunSQL deleteString
    DoCmd.SetWarnings True
End If
'
DoCmd.TransferText acImportFixed, _
                   passedImportSpec, _
                   passedImportTable, _
                   passedImportFIle, _
                   False
'
End Sub

Open in new window

passedImportFIle – is the complete path, including the file name, to the .txt file to be imported
PassedImportTable – is the linked SQL Server table linked where the imported data will go
passedImportSpec – Name of the Import Spec for the table being imported
passedClearFileBeforeImporting -= Boolean flag.  If yes, the SQL table is cleared of data before the import

Everything worked perfectly with the subset of the data provided by the client for initial testing.  The client just provided a complete set of legacy data to test against my import and one very large input table is causing issues.

When the logic executes the above ‘Docmd.Transfer’ with the large table as input I get this error.

Import Error
My accdb is 1GB and my disk is not close to full.

It appears that I will have to change my technique for moving the large input txt files to their related SLQ table.  I think txt files can be linked to an application but I’ve never done that.

What method would you use to get this large, fixed length .txt input file to the SQL table?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
fabrice:  I just tried linking to the txt file and got an error saying it does not contain data (I know it does) or is too large.  No luck
Linking a text file to the front-end database is like using DAO, but I'm talking about ADO.
Since ADO can handle much more data than DAO, it can be an option.

See this article on MSDN: https://msdn.microsoft.com/en-us/library/ms974559.aspx
0
 
NorieVBA ExpertCommented:
You could set up a linked server in SQL Server to a folder that contained the text files and the import spec.

Do you have such a folder?
0
 
mlcktmguyAuthor Commented:
Thanks Norie but I don't understand your answer.  I am relatively new to SQL server.

Sounds like you're suggesting that I import the tables directly into SQL Server which sounds like a very good idea but I have no knowledge of how to accomplish that.  I've created table, built indexes and written lots of SP's but that's about the extent of my SQL experience.

All this is being done on my standalone work computer so I have control of the work environment.  I should be able to set it up any way you suggest.  I'll just need more information.

The import specs are internal to MS Access and I don't know of any way to export them.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aranaCommented:
since you already have your import specs and rules in your access code, I would write them out to a TEXT file (CSV) in the format that you require and then iimport them with sql server management studio:
spNvT6h-1-.png
0
 
Fabrice LambertFabrice LambertCommented:
Hi,

you can set up an ADO connection on the text file, and use a recordset.
0
 
mlcktmguyAuthor Commented:
arana:  I know of no way to export 'Import specs'.  What does an import spec look like in SQL?  I could just recreate it.



fabrice:  I just tried linking to the txt file and got an error saying it does not contain data (I know it does) or is too large.  No luck
0
 
mlcktmguyAuthor Commented:
Here's some more information.  This is the SQL table I want to import the data into:
CREATE TABLE [dbo].[SQCS_CostFile_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CostName] [nvarchar](30) NULL,
	[MuniCode] [nvarchar](4) NULL,
	[LotBlock] [nvarchar](17) NULL,
	[TaxType] [nvarchar](1) NULL,
	[MuniCode02] [nvarchar](4) NULL,
	[ControlNumber] [nvarchar](7) NULL,
	[TaxType02] [nvarchar](1) NULL,
	[CostDetail01_100] [ntext] NULL,
	[GRBNumnber] [nvarchar](30) NULL,
	[GDNumber] [nvarchar](30) NULL,
	[Remarks] [nvarchar](76) NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaSQCS_CostFile_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


Here is a cut and pasted together look at the import spec.  

Import Spec
The import spec is not that extensive, I can key into SQL if I have to.  Can someone explain how to set up the import spec in SQL and then import the file 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\TQCSFILE.txt' into the SQL table?
0
 
mlcktmguyAuthor Commented:
Fabrice:  I read the linked article and it looks promising.  I tried creating the code in my Access front end and it wont even compile.  I think the code shown is only a piece of the complete code needed.

Do you have a complete example reading in a fixed length text file that you could post?

I get an error: 'variable not defined on the statement

Set objConnection = CreateObject("ADODB.Connection")

Public Sub readFixedLengthTextFile()

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Databases\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"

objRecordset.Open "SELECT * FROM PhoneList.csv", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo "Name: " & objRecordset.Fields.Item("Name")
    Wscript.Echo "Department: " & _
        objRecordset.Fields.Item("Department")
    Wscript.Echo "Extension: " & objRecordset.Fields.Item("Extension")
    objRecordset.MoveNext
Loop

End Sub

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
You probably have an "Option Explicit" statement on top of your module (wich is a good thing).

This statement enforce you to declare all variables.
Since the article is about VBScript, where (alas) variables declaration isn't required, this explain why your code don"t compile at all.
Declare your variables and it will fix it.
Dim objConnection As Object
Dim objRecordset  As Object
Dim strPathtoTextFile  As String

Open in new window

And replace WScript.Echo by Debug.Print
0
 
mlcktmguyAuthor Commented:
Fabrice: Yes I always use 'Option Explicit'.  I found that it saved many headaches.

Thanks for your answer, I think we're getting really close.  I made your change and revised the .echo to Debug.Print

Revised code with my file names:
Public Sub readFixedLengthTextFile()

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim recCount As Long
Dim totRecs As Long
'
recCount = 0

Dim objConnection As Object
Dim objRecordset  As Object
Dim strPathtoTextFile  As String

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'strPathtoTextFile = "C:\Databases\"
strPathtoTextFile = "I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"

objRecordset.Open "SELECT * FROM TQCSFILE.txt", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

    recCount = recCount + 1
    totRecs = totRecs + 1
    '
    If recCount = 500 Then
    Stop
        recCount = 0
        Debug.Print totRecs
        DoEvents
    End If

    Debug.Print "CostName: "; objRecordset.Fields.Item("CostName")
    
    objRecordset.MoveNext
Loop

End Sub

Open in new window


After reading the linked atricle you mentioned I also set up a 'Schema.ini' fiel in the same directory as 'TQCSFILE.txt'. This is it:
[TQCSFILE.txt]
Format=FixedLength

Col1=CostName Text Width 30
Col2=MUniCode Text Width 4
Col3=LotBlock Text Width 17
Col4=TaxType Text Width 1
Col5=MuniCode02 Text Width 4
Col6=ControlNumber Text Width 7
Col7=TaxType02 Text Width 1
Col8=CostDetail Text Width 14000
Col9=GRBNumber Text Width 30
Col10=GDNNumber Text Width 30
Col11=Remarks Text Width 75

Open in new window


The code runs and every 500 times thru the loop it 'Stops' and Debug.Prints the 'totRecs', which is literally the number of times it's gone thru the loop but I'm also hoping it is the number of records read.  I then comment the 'Stop' and the code continues to debug.print the totrecs to over 10,000,000.

The issue is that the other debug.print I have doesn't generate any output.  

Debug.Print "CostName: "; objRecordset.Fields.Item("CostName")

I want to 'CostName' as defined in the 'Schema.ini' file but nothing shows in the immediate window except 500, 1000, 1500, 2000, etc...

Any idea what I'm doing wrong?
0
 
mlcktmguyAuthor Commented:
Never even noticed the "On Error Resume Next" the author had in the top of the routine.  I commented that and now I'm getting

Runtime '3001' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"  on statement:
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"

Open in new window

0
 
Fabrice LambertFabrice LambertCommented:
Hi,

concerning your data issue, make sure that your schema.ini file describe precisely your text file, even fields you don't care about must be described in the correct order.

As for your connection string, you're missing 2 double quotes at the end:
1:objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"""

Open in new window

Also, today JET OLEDB is deprecated as provider, use ACE OLEDB instead:
:objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"""

Open in new window

[/code]
0
 
mlcktmguyAuthor Commented:
Thank you Fabrice, your statement enabled the connection and the logic is reading the file.

I executed a loop thru the file without taking any action on each record, except counting the record, and read thru the entire file of 152,501 records.  I then added logic to move fields from the input record to the output SQL table and write a record in the SQL table.

I tested the logic reading thru 5000 records and it worked perfectly.

Unfortunately, when I set up the logic to read thru all of the records in the file I get the same error message shown above somewhere between the 70,000 and 80,000 record.

Error
Here is the logic I am using to dynamically link to, read and write the file
Public Sub readFixedLengthTextFile()

'On Error Resume Next

clearSQLTable_Sproc "SQCS_CostFile_Import"
' output file
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("SQCS_CostFile_Import", dbOpenDynaset, dbSeeChanges)


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim recCount As Long
Dim totRecs As Long
'
recCount = 0
wkStartTime = Now

Dim objConnection As Object
Dim objRecordset  As Object
Dim strPathtoTextFile  As String

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
strPathtoTextFile = "I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\"

       
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=NO;FMT=FixedLength"""
          

objRecordset.Open "SELECT * FROM TQCSFILE.txt", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

    recCount = recCount + 1
    totRecs = totRecs + 1
    
    
    '
    If recCount = 10000 Then
      '  Stop
        recCount = 0
        wkCurrTime = Now
        Debug.Print totRecs & ", " & RunTime(wkStartTime, wkCurrTime)
        DoEvents
    End If
    
    
   With rsOut
      .AddNew
            ![CostName] = Nz(objRecordset.Fields.Item("CostName"))
            ![MuniCode] = Nz(objRecordset.Fields.Item("MUniCode"))
            ![LotBlock] = Nz(objRecordset.Fields.Item("LotBlock"))
            ![TaxType] = Nz(objRecordset.Fields.Item("TaxType"))
            ![MuniCode02] = Nz(objRecordset.Fields.Item("MuniCode02"))
            ![ControlNumber] = Nz(objRecordset.Fields.Item("ControlNumber"))
            ![TaxType02] = Nz(objRecordset.Fields.Item("TaxType02"))
            ![CostDetail01_100] = Nz(objRecordset.Fields.Item("CostDetail"))
            ![GRBNumnber] = Nz(objRecordset.Fields.Item("GRBNumber"))
            ![GDNumber] = Nz(objRecordset.Fields.Item("GDNNumber"))
            ![Remarks] = Nz(objRecordset.Fields.Item("Remarks"))
            '![xxx] = Nz(objRecordset.Fields.Item("Filler"))
      .Update
   End With
    
    objRecordset.MoveNext
Loop

rsOut.Close
Set rsOut = Nothing

Debug.Print "AllRecs: "; totRecs & ", " & RunTime(wkStartTime, wkCurrTime)
MsgBox "Complete"

End Sub

Open in new window

There is no query involved in this processing, any idea why this  error is occurring or what I can do as a work around to get by it?

My ACCDB size is only 62MB and my hard drive has over 75GB of freespace.

Is there a way I can allocate more temp storage?
0
 
mlcktmguyAuthor Commented:
Thanks You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.