Importing text files with double quotes - with a specification

I am trying import a text file as a comma delimited file.  In some of the columns the text has double quotes in some of the record values.  The import function errors these records out.   I have tried just changing the text qualifier to none and " " and neither function works correctly.  The import only import 4 of the 7 records.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
This appears to be a nonstandard text document. (No column headings, padded values, ...etc)
I can see no point at which there is a clear delineation between one record and the next...

It might be easier to ask the originator of this file to export it to a more standard format (ex.: Excel, or a standard CSV format)
Jeffrey CoachmanMIS LiasonCommented:
as you can see form my screenshot above, ...even when I stripped the double quotes form your textfile, ...Access still reports only 4 records...
centralmikeAuthor Commented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
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, 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, ",")
            ' 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
        End If

    Next i
    Set rs = Nothing
End Sub

Open 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.

Processed data
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.
Jeffrey CoachmanMIS LiasonCommented:
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)
You have a malformed CSV file.
contains an odd number of quote characters.

Everything else can be fixed with the following regular expression replace:
Pattern: (,"[^",]+)"([^",\n]+)"([^",]+",)
ReplaceWith: $1'$2'$3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
centralmikeAuthor Commented:
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?
centralmikeAuthor Commented:
I've requested that this question be deleted for the following reason:

I cant remember what I a was asking for.
Posting an objection, to stop the auto-delete.

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


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 that you remember them, and so that we don't pour valuable time and effort into helping a non-responsive author.
centralmikeAuthor Commented:
I've requested that this question be deleted for the following reason:

Please delete my question
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.