Importing text files with double quotes - with a specification

as you can see form my screenshot above, ...even when I stripped the double quotes form your textfile, ...Access still reports only 4 records...

The file comes with header file.  That will show you the columns name.  I will send the original text file with it.  Sometimes the users include fields with double quotes in the name.  This suppose to be a comma delimited file.
Producer-Header.csv
PRODUCER20150430071000.TXT

In other words, please post a sample of everything you get, and also include an example of the expected output.

So far you have posted a database file and 3 text files, ...so I am unclear as to what is what...

I don't think you're going to be able to do this with an import spec, because that is too rigidly defined for your data.

If you look at the text file as a whole, there are TWO different delimiters at work.

1.  The text "N", including the double quotes separates the data into records
2. After that, Commas separate the data into distinct fields.

So you need to first parse the text file in its entirety, separating the data in to rows (which are separated by "N").

Then you need to divide each record into its fields (which are separated by commas)

... and in the process you need to clean up/remove all of the extraneous spaces and double quotes.

There are a few VBA functions needed for this:

1.  Split() :  Parses the data based on a delimiter into distinct array elements
2. Trim(): Removes extraneous spaces
3.  Replace(): Can be used to replace the double quotes with empty strings, effectively removing the double quotes

Try this, reading the instructions in the comments.  It uses these techniques to take the text file as a whole (You'll need to get that into a table, in a single memo field... copy/pasting the whole file is fine for testing this), and parses it into fields in an output table.

You'll have to modify this to suit your exact needs (table/field names, etc)

Sub ProcessText()
    Dim s() As String
    Dim s2() As String
    Dim sTrimmed As String
    Dim sFieldTrimmed As String
    Dim sImport As String
    Dim i As Integer
    Dim j As Integer
    Dim rs As DAO.Recordset
    
    ' read the raw imported data.  I'm assuming a table named tblImport, with the entire contents of your text file in a single field called TextField.
    ' You can copy/paste the contents of the file into that field for testing purposes.
    sImport = "" & DLookup("TextField", "tblImport")
    
    ' Split the string into records using "N" as a delimiter
    s = Split(sImport, Chr(34) & "N" & Chr(34))
    
    ' Open the output table as a recordset for the processed data.  I'm using the name 'tblSplitData', which has approximately 30 fields.
    ' Replace this name with the name of the table you want your data inserted into.
    ' The order of fields in the output table should match the order of fields in the text file.
    Set rs = CurrentDb.OpenRecordset("tblSplitData", dbOpenDynaset)
    
    ' loop through the records, splitting them into fields using the comma as a delimiter
    For i = 0 To UBound(s)
        sTrimmed = Replace(Trim(s(i) & ""), Chr(34), "")
        If sTrimmed <> "" Then
            Debug.Print sTrimmed
            s2() = Split(sTrimmed, ",")
            rs.AddNew
            
            ' loop through the fields, saving the data in the output table
            For j = 0 To UBound(s2)
                sFieldTrimmed = Trim(s2(j))
                rs(j) = sFieldTrimmed
            Next j
            rs.Update
        End If

    Next i
    rs.Close
    Set rs = Nothing
    
End Sub

Select allOpen in new window

This shows a portion of the data parsed by that function.  

It looks like the text file might contain some extraneous/redundant fields.  If that is the case, then you should use a staging table for this 'cleaned up' data... and then use append queries and/or code to get only the needed fields into the final 'destination' table.


On a side note, this data looks potentially sensitive.  If it is, you should hit the Request Attention button to ask a moderator to remove the files... which you can replace with files containing junk or obscured data.  (Everything posted here is publicly visible and searchable)

You can try using DOS to concatenate the two files and create a .csv file.  Then import that .csv file into Access ONCE manually and create an import spec.  You will need to specify " as the text qualifier.  Change the data types of the columns as necessary.

The first file you posted has no carriage return/line feeds to separate records but the second file does.  If the file does not have proper record delimiters, you are reduced to writing code to parse it yourself such as what Mbizup posted.  But if it has delimiters, you might be able to import it using the wizard.

On the last form of the wizard, press the advanced button and save the spec.  Then, you can automate the process by using the TransferText method and referencing the spec name.

Or,...
Again, ...perhaps it would be easier to see if the Originator/Source of this file could export it as a standard CSV, or Excel file
(it never hurts to ask, and you could avoid all the machinations needed to get this to work in its current format)

I've requested that this question be deleted for the following reason:

I forgot about the questin

So, did you get a solution.  mbizup posted code that would help. Did it?

I've requested that this question be deleted for the following reason:

I cant remember what I a was asking for.

Your text file was not in the correct format to be imported smoothly.
AFAICT, ...these look lie the best solutions...
https://www.experts-exchange.com/questions/28676341/Importing-text-files-with-double-quotes-with-a-specification.html?anchorAnswerId=40787505#a40787505
https://www.experts-exchange.com/questions/28676341/Importing-text-files-with-double-quotes-with-a-specification.html?anchorAnswerId=40787530#a40787530

Posting an objection, to stop the auto-delete.

See aikimark and boag2000's comments from October and today.

Mike,

If you can't remember the question, you can always ask a moderator for input closing the question...

But even better would be to remain active in your own questions and actually READ the comments...so that you remember them, and so that we don't pour valuable time and effort into helping a non-responsive author.

I've requested that this question be deleted for the following reason:

Please delete my question