Error reading text file from VBA

is your code in Access, or VB?

If in Access, you should attempt to read the data in to an Access table via the External Date option on the ribbon.  

Then, in the import wizard indicate that you want to use fixed width fields, drag the lines to define the fields or click the Advanced button (bottom left corner of the wizard) and enter the actual field widths.  While viewing the advanced options, save your import specification.  

You can then use the docmd.TransferText method, and provide the import specification name you created to that method.

* your file isn't delimited, it contains fixed length fields.
* you may need to create a schema.ini file
* it looks like there might be some inter-record spacing issues.  I see a blank line between the two non-blank lines.

Hi,
@Dale,
I have an Access ADP that is connected to SQL 2005 BE.
Do you suggest I change the approach of reading the file to the above?

Just looking at your example, it appears to be a fixed width file rather than a CSV file.  If it is fixed width, you can import it once manually as a fixed width file and save the import spec.  Then you can automate it by using the TransferText method and referencing the import spec.  Or it may be tab delimited.  You can try importing it manually as tab delimited and see if that works.  At some point in the import, click the Advanced button.  You will be able to specify the delimiter and also specify a date format so the next to last column would import as a date and you won't have to fix it later.

A properly formatted CSV file would look like this:

"111-22-3333","W688","FIRST LAST","141213",30.00	
"444-55-5555","W690","FNAME LNAME,PC","141206",44.50

Select allOpen in new window

A tab delimited file, which this may be, would contain the quotes but substitute tabs for the commas.

@aikimark,
Everything works besides those records with commas.

@Daly, Pat,
While trying to do the manually import process, I don't get the option to save it, its grayed out, see attached.
untitled.bmp

That's odd.  Compile the database and fix any compile errors.  Then compact and repair and try again.  If it still fails, try with a new, empty database.  If that fails, you will need to repair your Office installation.

PS, you also need to check the leading zeros in dates box.

Hi Experts,

It happen to be that I don't need the column containing the commas, the problem is that this comes from an external program that I don't have access to.

Is it possible to open the file and read thru a loop each character and when it finds a comma replace it with empty string, then I close the file and run my original code?

It probably doesn't like the field name you gave the SSN field, although I've set mine up just like yours and my "SaveAs" button is enabled.

BTW, I think you also need to change the name of your [YYMMDD] field to something like "HireDate" or something along those lines as well and probably want to change the data type of that field from long integer to text.

That is not going to be easier than getting the import spec to work but if you are comfortable writing code, have at it.

The bad name that Dale mentioned might actually be keeping the spec from being saved or it could be the name.

Also, if you want to import yymmdd as a real date, you need to get ALL the moving parts right.  So, in addition to the other settings, you need to change the data type from long integer to date.

If you don't need the EmployeeName column, then in the import specification, check the "Skip" checkbox so that it doesn't import that column.  

Additionally, you might want to consider simply linking to the document rather than importing it, then you can write a query to pull in the non-NULL records and only import the fields you actually need into your production table.

The stray comma will still cause a problem with linking unless you use an import spec to ignore the column.

@Pat, Dale,

Tried those suggestions (Compact, Change column names etc..) so far nothing helped, I might try soon with a another pc using  different office version (mine is 2000 and that is 2003).

However just wondering if this approach would work later on in all users pc's or I will need to repeat this import on each users pc?

@Dale,

This is an Access project, not sure what you mean link to external file?

Re the no need of the column, I meant to ask if by removing the comma is possible to avoid the problem I'm having with current code.

Ah, I missed the response which indicated that it is an ADP.  I've never written an ADP, but that may well be an issue.  If it were me, I would create a staging table in SQL Server to import the raw data into, and would then pull the fields you need from that staging table into your production table.

A2000 is very old.  I don't even remember it.  I think in earlier versions, you may have had to install certain features separately and this may be one of them.  I don't recall there being any problem related to the import spec in an Access project but who knows.  The file format for A97 was deprecated for A2013 so I don't imagine that A2K is far behind.  Access projects have also been deprecated so it might be time to think about moving to a new version of Access.  Skip A2007 but A2010 is solid.  A2013 has nothing new for client/server but it deprecates a lot of important stuff so that may be a bridge too far.  In addition to the two I mentioned, you also loose the ability to read .dbf files and you loose some ActiveX objects such as TreeView.  Of course, we lost ULS with A2007, although most people found that unusable and that's why it bit the dust.

@Dale,
Not sure what does it mean "create a staging table in SQL Server to import the raw data into", are you referring to kind of a memo field that would hold the full contents of the file, including all special characters like spaces tabs etc?

@Pat,
 thanks for those useful info, will keep in mind when holding by upgrading (it doesn't really depend on me, it needs approval from the higher ups..)

@All,
Tried with other version and doesn't let me save either, as Dale mentioned this looks like an ADP problem.
I might try to save it in a MDB and then use it in ADP, but first would need to know if that would work for all users.
What about  reading the file character by character and replacing the commas, does someone have an example of this?

@bfuchs,

No, a "staging table" generally has the same number of fields as are in the file to be imported.  I normally make mine so that all of the fields in the staging table are text fields of the appropriate length, which accept NULL values.  This allows you to import your data into this table first.  You could then delete all of the records that are NULL (the file you posted may have had an extra CRLF and loaded a NULL record between the two good records).  You can then perform a series of tests to confirm that the values in each of the fields is formatted properly (no decimal points for integer fields, "-" where appropriate for the SSN fields, etc...).  Then you can run a query to upload the records that have all of the required fields and which meet the formatting criteria into your production table.

I do this pretty consistently for all of the Excel, CSV, delimited, or fixed width text files I load into Access or SQL Server.  There is nothing worse than expecting data in a specific format and having someone dump inappropriate data into a field and foul up an upload process.

@Dale,
OK understood, however here the problem is that I cant get to read those records at the first place, as you can see from code originally posted, it tries reading line by line and when it comes to a record containing a comma it gets truncated. below is the full code I am using so you get a clear understanding of whats going on here.

Private Sub ImportEmpTovWeeklyHours()
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim f As ADODB.Field
   Dim v As Variant
   Dim strSql As String
   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\tovserver\E\XFER;"
   rs.Open "SELECT * FROM [empwehr.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
    
   rs.MoveFirst
  'CurrentProject.Connection.Execute ("delete  from dbo.FacilitiesTovLastInvoiceDate")
 
On Error Resume Next
   While Not rs.EOF
        If Not IsNull(rs.Fields(0)) Then
          v = Split(rs.Fields(0), Chr(9))
          
        If UBound(v) > 1 Then
            strSql = "Insert into EmpTovWeeklyHours" _
            & "(SocialSecurity,Day,Hours)" _
            & " Values ('" & Replace(v(0), Chr(32), "") & "','" & Mid(v(3), 3, 2) & "/" & Mid(v(3), 5, 2) & "/" & Mid(v(3), 1, 2) & "'," & Replace(Replace(v(4), Chr(32), ""), "'", "") & ")"
            
            CurrentProject.Connection.Execute strSql
        End If
          
          'Debug.Print v(2)
        End If
          
      rs.MoveNext
   Wend
   rs.Close
End Sub

Select allOpen in new window

just to clarify, the actual file being imported contains thousands of records, they all work fine except for those with commas

I tried your driver and both the Jet and ACE providers to no avail.  Looks like you're going to need to create a Schema.ini file (see attached example)

Reading your sample file, produces the following data in the recordset:

F1            111-22-3333
F2            W688
F3            FIRST LAST
F4             141213 
F5             30 
F6            Null

F1            Null
F2            Null
F3            Null
F4            Null
F5            Null
F6            Null

F1            444-55-5555
F2            W690
F3            FNAME LNAME,PC
F4             141206 
F5             44.5 
F6            Null

Select allOpen in new window

schema.ini

@aikimark,
I cant open the attached, it gives me an error

You don't read it.  You put it in the same folder as the myfile.txt file.

ok, sorry for asking this just never dealt with schema before.
what do I do next?
just run my original code or this works with the import procedure that Dale and Pat are suggesting?

yes.  If that doesn't work, I'll post one or both provider strings that I tested tomorrow morning.

Did you try using DoCmd.TransferText to import the file?  You also never said if the file is delimited or fixed.

@aikimark,@Pat,
See attached.
Aki.bmp
Pat.bmp

@Pat,
The file is delimited.

Did you put the schema file in the same folder as the tab delimited file, like I told you to do?

schema.ini is not a substitute for an Access-resident specification.  You can't use the schema.ini file with the TransferText method

@aikimark,

Yes I did.

Not sure what was your answer on my question, if that schema.ini was meant to work with my original code or with import methods, therefore I tried both..however neither worked.

let me know what else I am missing here.
Thanks

@bfuchs,
You didn't answer the question about TransferText.  What happens when you try to import as a delimited file using that method?

I get the error attached above.

This is the code I tested that produced the results I posted:

Sub testit()
    Dim conn As Object
    Dim rs As Object
    Const adOpenStatic = 3
    Const adLockReadOnly = 1
    Const adCmdText = 1
    Set conn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
                                                             'DBQ=\\myserver\E\XFER;
    conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\users\aikimark\downloads;"
'    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
'           "Data Source=c:\users\mark\downloads;" & _
'           "Extended Properties=""text;HDR=No;FMT=TabDelimited"";"
           
    rs.Open "SELECT * FROM [myfile.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
    do until rs.eof
        for each fld in rs.Fields
            Debug.Print fld.name, fld.value
        next
        rs.movenext
    loop
End Sub

Select allOpen in new window

Sorry I missed that.  It looks like the file is improperly delimited.  I think Access is not recognizing the tab as the delimiter, assuming that tab IS the delimiter in the file.

The last argument specifies that the file has a header row and it doesn't look like yours does.   Make sure you are not appending to an existing table.  You can only do that if the column headers match and yours won't.

Import the file into Excel and see what Excel thinks of it.

@aikimark,
Try running the code you tested with the file I just attached, and for each record do debug.print rs.fields(0), you will see what I mean.

@Pat,
I tried executing the following

    DoCmd.TransferText acImportDelim, , "F:\Fuchs\Temp\testIT2.xls", "F:\Fuchs\Temp\12-31-14.txt", False

Select allOpen in new window

didn't gave me an error, however doesn't look like anything was created, I don't see a file there.
12-31-14---Copy.txt

1. You have changed your data.  This appears to have column headers -- please use the updated schema.ini file I have uploaded
2. There is a problem with your data -- you put an extra tab character after "Name ZZZZZ"
3. Since you still have Null records, I have changed the SQL in the following code to eliminate them.

Sub Q_28588664(Optional parmDrivertype As String = "ISAM")
    Dim conn As Object
    Dim rs As Object
    Dim fld As Object
    Const adOpenStatic = 3
    Const adLockReadOnly = 1
    Const adCmdText = 1
    Const cPath As String = "c:\users\aikimark\downloads"    '\\myserver\E\XFER   'F:\fuchs\temp
    
    Set conn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    
    Select Case UCase(parmDrivertype)
        Case "ISAM"
            conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & cPath & ";"
        Case "JET"
            conn.Open "Provider=Microsoft.JET.OLEDB.4.0;" & _
                   "Data Source=" & cPath & ";" & _
                   "Extended Properties=""text;HDR=Yes;FMT=TabDelimited"";"
        Case "ACE"
            conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & cPath & ";" & _
                   "Extended Properties=""text;HDR=Yes;FMT=TabDelimited"";"
        Case Else
            MsgBox "Drivertype parameter must be string with one of these values: [ISAM,ACE,JET]"
    End Select
    
    rs.Open "SELECT * FROM [myfile.txt] Where YYMMDD Is Not Null", conn, adOpenStatic, adLockReadOnly, adCmdText
    Do Until rs.EOF
        For Each fld In rs.Fields
            Debug.Print fld.Name, fld.Value
        Next
        Debug.Print
        rs.movenext
    Loop
End Sub

Select allOpen in new window

Note: The code allows you to specify a different driver ["ACE", "JET", "ISAM"]

Note: You need to assign the correct path to the cPath constant before testing.  Alternatively, you may parameterize the path value by tweaking the code.

Note: [999-99-9999] is a bad name for a column.

Note: Be aware that social security numbers are a terrible thing to store in a database or text file.

All three drivers should produce the following results in the Immediate window.

999-99-9999   111-11-1111
TOV-CD        C1112
EMPLOYEE NAME                             Name ZZZZZ
YYMMDD         141213 
WEHRS          28 
NoName        Null

999-99-9999   222-22-2222
TOV-CD        C1113
EMPLOYEE NAME                             OSSSSS Csssssss,RN
YYMMDD         141213 
WEHRS          22.25 
NoName        Null

999-99-9999   333-33-3333
TOV-CD        C1116
EMPLOYEE NAME                             MWERRRR CQQQQ
YYMMDD         141213 
WEHRS          37.5 
NoName        Null

Select allOpen in new window

schema.ini

Hi aikimark,
1-Did all the above, and got attached error when running the following line

rs.Open "SELECT * FROM [myfile.txt] Where YYMMDD Is Not Null", conn, adOpenStatic, adLockReadOnly, adCmdText
    

Select allOpen in new window

2- As mentioned above, the file comes from an external program, there not much I can do about it.
1-1-15.png

what does your file look like?  I tested the posted code with the most recent text file that you uploaded.

I used the same file posted last as you can see from picture, and nothing was changed on that file, attaching again.
12-31-14---Copy.txt
1-1-15.png

You have failed to fix the file.  You have an extra tab character.  That was one of my earlier comments.

Which driver option did you use in your test?  Have you tested all three drivers?

OK, now fixed that and still the same error when not sending a param, when using Jet and Ace I get the error just attached.
Jet.png
12-31-14---Copy.txt

Please try this version of the file.
12-31-14---Copy--1-.txt

same thing, see attached.
tried with all 3 params.
NoParam.png

And the most recent version of schema.ini is in the f:\fuchs\temp directory?

yes, see attached.
schema.ini
Schema.png

since schema.ini has file names, you need to either rename the text file that I corrected as myfile.txt or add another section for whatever named file you are referencing in your Select statement.

Oh, I just  realized that, and after changing it actually gives me your output.
so now in order to accomplish the original insert, I should change the logic, instead of using rs.fields(0) and parsing it, I should loop thru the fields as in your example, and check if name of field is 999 its social, and so on?

Because of the header row, your fields have specific names, instead of just F1, F2, etc.

What are you trying to do with the text data?

there are 5 fields there, and I want to insert field 1, 4 and 5 into a table (as shown in original code above ID: 40524908).

Is EmpTovWeeklyHours an Access table?
What are the data types of the destination fields?

Hi Aikimark,
sql table varchar, datetime, decimal.
I will be out of the office Tom, will reply on sunday, good night.
Thanks

Also, how many rows will these tab-delimited files contain?

I'm thinking that SSIS would be a simpler and quicker way to import this data.  Why are you using Access as a middle man between the tab-delimited data and the database?

What about  reading the file character by character and replacing the commas, does someone have an example of this?

If you still remain interested in that at this point, it can be done.
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim f As TextStream
Set f = fs.OpenTextFile(SomeValidWindowsFullPath, ForReading, TristateFalse)
Dim TheLineContents As String
' Read the file line by line
Do While Not f.AtEndOfStream
    If f.AtEndOfStream = False Then
        TheFileContents = f.ReadLine
            'here you'd code what you wanted to do.
            'since it's a large string, I suspect that you use Left, Right, Mid and Trim
            'to get the characters you want and commit them to a table
             If f.AtEndOfStream Then
                 Exit Do
             end if
      end if
Loop

f.Close
Set f = Nothing
set fs = Nothing

@Nick,
I am really interested in that too, however at this point I just think its only fair to promote aikimark, after all the efforts he had put to solve this issue, and finally the latest suggestion seems to be working.
Therefore I posted a new question focusing on this topic, please look at the following link.
https://www.experts-exchange.com/questions/28590449/How-to-run-find-and-replace-command-in-a-text-file-from-VBA.html
Thanks.

Hi aikimark,
This latest (with minor modifications) seems to be working, well.
Thanks very much for all your efforts!!
Ben