Import CSV File - One Drive and C Drive

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Not a problem! BTW, I personally LOVE your spreadsheet! Have a nice dinner.

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

See the attached database.
First, take a look at the table.
Close it, then open the form.
It has 3 buttons:

Split Names: populates the Prefix, FirstName etc. fields.
Clear Fields: clears the Prefix. FirstName etc. fields so that you can start over (Split Names does this automatically).
Close: closes the form.

If you want to use this in your own database, make sure that you have a table with the same field names as tblNames. (If you want different field names, you'll have to modify the code accordingly).

Leesha.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hi Hans!
This is very impressive and I can definitely make and work and the user won't mind any clean up that may be needed.
Thank you soooooo much!
Leesha

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

BTW, reading your code (which I could never have written) gave me such a chuckle. I love the "Get me out of here" and "clean up the mess".

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hi Hans,
I've got this working in the database and it's awesome! Three more questions (at least for now)...........

1. When the .csv file is imported it creates import_importerrors. Is it possible to stop this table from being created or can it be deleted with code?
2. The names in the file are all in upper case. How do I convert them to proper case?
3. Is there a way to set the import specifications to include a Zero in zipcodes that should start with a zero? IE 06360 imports as 6360.

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

1. You can use

CurrentDb.Execute "DROP TABLE import_importerrors", dbFailOnError

2. Change the SplitNames procedure to

Code: Select all

Sub SplitNames()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim nme As String
    Dim arr() As String
    ' Prevent code from breaking
    On Error GoTo ErrHandler
    ' Clear fields just in case they have already been filled
    Call ClearFields
    ' Reference to current database
    Set dbs = CurrentDb
    ' Open recordset on tblNames
    Set rst = dbs.OpenRecordset("tblNames", dbOpenDynaset)
    ' Loop through the records
    Do While Not rst.EOF
        ' Edit the record
        rst.Edit
        ' Get the full name
        nme = Trim(rst!FullName)
        ' Remove double spaces
        nme = Replace(nme, "  ", " ")
        ' Remove spaces before/after comma
        nme = Replace(nme, " ,", ",")
        nme = Replace(nme, ", ", ",")
        ' Process prefixes and remove them from the full name
        If LCase(Left(nme, 4)) = "dr. " Then
            rst!Prefix = "Dr."
            nme = Mid(nme, 5)
        ElseIf LCase(Left(nme, 3)) = "dr " Then
            rst!Prefix = "Dr."
            nme = Mid(nme, 4)
        End If
        ' Process suffixes and remove them from the full name
        If LCase(Right(nme, 4)) = ",iii" Then
            rst!Suffix = "III"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " iii" Then
            rst!Suffix = "III"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",ii" Then
            rst!Suffix = "ii"
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " ii" Then
            rst!Suffix = "ii"
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 6)) = ",ph.d." Then
            rst!Suffix = "Ph.D."
            nme = Left(nme, Len(nme) - 6)
        ElseIf LCase(Right(nme, 6)) = " ph.d." Then
            rst!Suffix = "Ph.D."
            nme = Left(nme, Len(nme) - 6)
        ElseIf LCase(Right(nme, 4)) = ",phd" Then
            rst!Suffix = "Ph.D"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " phd" Then
            rst!Suffix = "Ph.D"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = ",jr." Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 5)
        ElseIf LCase(Right(nme, 4)) = " jr." Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",jr" Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " jr" Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 4)) = ",sr." Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " sr." Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",sr" Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " sr" Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 3)
        End If
        ' Process the actual name
        ' Does it contain a comma?
        If InStr(nme, ",") Then
            ' If so, it's last/first
            arr = Split(nme, ",")
            rst!LastName = StrConv(Trim(arr(0)), vbProperCase)
            arr = Split(Trim(arr(1)))
            rst!FirstName = StrConv(arr(0), vbProperCase)
            ' Do we have a middle name?
            If UBound(arr) > 0 Then
                rst!MiddleName = StrConv(arr(1), vbProperCase)
            End If
        Else
            ' It's first/last
            arr = Split(nme)
            rst!FirstName = StrConv(arr(0), vbProperCase)
            ' Do we have a middle name?
            If UBound(arr) = 2 Then
                rst!MiddleName = StrConv(arr(1), vbProperCase)
                rst!LastName = StrConv(arr(2), vbProperCase)
            Else
                rst!LastName = StrConv(arr(1), vbProperCase)
            End If
        End If
        ' Save changes
        rst.Update
        ' Move to the next record
        rst.MoveNext
    Loop

ExitHandler:
    On Error Resume Next
    ' Close the recordset
    rst.Close
    ' Get me out of here
    Exit Sub
    
ErrHandler:
    ' Report the error
    MsgBox Err.Description, vbExclamation
    ' Clean up the mess
    Resume ExitHandler
End Sub
Warning: this won't handle names such as McDonald and LaForge correctly - they will become Mcdonald and Laforge.

3) Specify that the ZIP code is a text field instead of a number field.
Regards,
Hans

User avatar
StuartR
Administrator
Posts: 12168
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Import CSV File - One Drive and C Drive

Post by StuartR »

I have been following this thread with interest as the approach might be useful in something that I need.

Is there a reason that the only prefix you recognise is Dr? What about Mr, Mrs, Ms, Mx, Master, Miss, Professor, Sir, Lord, ...
StuartR


User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

It would be easy to add those using the same methods. Since Leesha didn't mention them, I left them out of the code.
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Good Morning!
I can't wait till Monday to show the end user what you have accomplished for me and how I have it working for them. They will be so thrilled.

To answer StuartR's question, there are no Mr, Mrs, etc in these tables but I can imagine that there would be on a different user's tables. I will be tucking this sample DB aside with all of my other "Han's Samples".

Re my questions above, 1 and 2 worked perfectly! As for 3, I'm tickled to say that I had already made the field a text field in the specs on import. The problem is that the [zip] column in the .csv file is already missing the 0. The zipcodes are in this format prior to import:

06384-0333
6385
66385

The issue is the the 4 digit zipcode should be 5 digit with a zero in front of it = 06385.
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

You can either create an update query with the following SQL and run it:

Code: Select all

UPDATE TableName SET ZipCode = "0" & ZipCode WHERE Len(ZipCode)=4
(substituting the correct table and field names of course), or run it from code:

Code: Select all

CurrentDb.Execute "UPDATE TableName SET ZipCode = '0' & ZipCode WHERE Len(ZipCode)=4", dbFailOnError
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Thanks Hans!

I am about to shoot myself at not being able to figure this out. I downloaded a new file from the site the user pulls from to test with and I'm getting the attached message. I redid the specs to be sure everything was correct. I made sure the to type of csv file is selected correctly. I've even copied and pasted the name "Member ID" from the csv file to the specs. Nothing works. If I delete "Member ID" from the file then the next column name comes up as not being in the table. I've been at this for 2 hours. What am I doing wrong?
Leesha
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

There are a few weird characters before Member ID...
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

I believe those are due to the type of CSV file. I found that out earlier in this process and set the type of code to adjust for it in the specs. Is there a universal code I should be using in the specs. Would it be better to have the user save the CSV file as an excel file? The code I'm using (from earlier in this thread) is to import a csv file.

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

An Excel file would probably be better.
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Sounds good. I even managed to change the code to import the file as a .xlsx file! The only issue I'm running into now is that the code you gave me to delete the import error table returns an error that that table doesn't exist. It was created each time I imported the csv file, but didn't happen with Excel. ? if I need the code with Excel?

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

If you don't get import errors, you don't need that line. But if you want to keep it, you might insert

On Error Resume Next

above it, and

On Error Goto 0

below it (or On Error Goto the name of your error handler, if you have an error handler)
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hi Hans,
Back again. I like your idea of keeping in the code to delete the table showing the table showing the import errors. I added the code as suggested (please see below) and it didn't delete the table. I noted that the table name is different with an Excel import so I changed the name. Problem is it's not deleting the table. The other issue is that I'm getting an error that says "subscript out of range". I tracked it down to being the code the split the names. Wouldn't you know it there is a name that is simply a one word name. No space, no comma, etc. It simply says "Turner". It appears to stop the code because all names after that did not split. Is it possible to add this type of scenerio into your code? The only other exception I can think is if there is a name with a space before it and it's a one work name, like " Turner"
Thanks!
Leesha


' Drops import error table from database
On Error Resume Next
CurrentDb.Execute "DROP TABLE import$_importerrors", dbFailOnError
On Error GoTo 0

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import CSV File - One Drive and C Drive

Post by HansV »

Why don't you leave the ImportErrors table alone? End users shouldn't look at tables anyway.

As for the single-word name: cleaning up names is always a multi-step process. Do a run, find a problem. Fix the problem, do another run, find the next problem, etc.
This version will handle single-word names - they will be placed in the LastName field (we have no way of telling whether it is a first name or a last name).

Code: Select all

Sub SplitNames()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim nme As String
    Dim arr() As String
    ' Prevent code from breaking
    On Error GoTo ErrHandler
    ' Clear fields just in case they have already been filled
    Call ClearFields
    ' Reference to current database
    Set dbs = CurrentDb
    ' Open recordset on tblNames
    Set rst = dbs.OpenRecordset("tblNames", dbOpenDynaset)
    ' Loop through the records
    Do While Not rst.EOF
        ' Edit the record
        rst.Edit
        ' Get the full name
        nme = Trim(rst!FullName)
        ' Remove double spaces
        nme = Replace(nme, "  ", " ")
        ' Remove spaces before/after comma
        nme = Replace(nme, " ,", ",")
        nme = Replace(nme, ", ", ",")
        ' Process prefixes and remove them from the full name
        If LCase(Left(nme, 4)) = "dr. " Then
            rst!Prefix = "Dr."
            nme = Mid(nme, 5)
        ElseIf LCase(Left(nme, 3)) = "dr " Then
            rst!Prefix = "Dr."
            nme = Mid(nme, 4)
        End If
        ' Process suffixes and remove them from the full name
        If LCase(Right(nme, 4)) = ",iii" Then
            rst!Suffix = "III"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " iii" Then
            rst!Suffix = "III"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",ii" Then
            rst!Suffix = "ii"
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " ii" Then
            rst!Suffix = "ii"
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 6)) = ",ph.d." Then
            rst!Suffix = "Ph.D."
            nme = Left(nme, Len(nme) - 6)
        ElseIf LCase(Right(nme, 6)) = " ph.d." Then
            rst!Suffix = "Ph.D."
            nme = Left(nme, Len(nme) - 6)
        ElseIf LCase(Right(nme, 4)) = ",phd" Then
            rst!Suffix = "Ph.D"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " phd" Then
            rst!Suffix = "Ph.D"
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = ",jr." Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 5)
        ElseIf LCase(Right(nme, 4)) = " jr." Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",jr" Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " jr" Then
            rst!Suffix = "Jr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 4)) = ",sr." Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 4)) = " sr." Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 4)
        ElseIf LCase(Right(nme, 3)) = ",sr" Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 3)
        ElseIf LCase(Right(nme, 3)) = " sr" Then
            rst!Suffix = "Sr."
            nme = Left(nme, Len(nme) - 3)
        End If
        ' Process the actual name
        ' Does it contain a comma?
        If InStr(nme, ",") Then
            ' If so, it's last/first
            arr = Split(nme, ",")
            rst!LastName = StrConv(Trim(arr(0)), vbProperCase)
            arr = Split(Trim(arr(1)))
            rst!FirstName = StrConv(arr(0), vbProperCase)
            ' Do we have a middle name?
            If UBound(arr) > 0 Then
                rst!MiddleName = StrConv(arr(1), vbProperCase)
            End If
        Else
            arr = Split(nme)
            If UBound(arr) = 0 Then
                ' Just a single name
                rst!LastName = StrConv(nme, vbProperCase)
            Else
                ' It's first/last
                rst!FirstName = StrConv(arr(0), vbProperCase)
                ' Do we have a middle name?
                If UBound(arr) = 2 Then
                    rst!MiddleName = StrConv(arr(1), vbProperCase)
                    rst!LastName = StrConv(arr(2), vbProperCase)
                Else
                    rst!LastName = StrConv(arr(1), vbProperCase)
                End If
            End If
        End If
        ' Save changes
        rst.Update
        ' Move to the next record
        rst.MoveNext
    Loop

ExitHandler:
    On Error Resume Next
    ' Close the recordset
    rst.Close
    ' Get me out of here
    Exit Sub
    
ErrHandler:
    ' Report the error
    MsgBox Err.Description, vbExclamation
    ' Clean up the mess
    Resume ExitHandler
End Sub
Regards,
Hans

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

I totally get what you are saying on the names. I think that with the new code we've caught as much as is within reason to trap. RE leaving the error tables I have no issue with that if you don't think it's going to bog down the various user db's over time.
Thanks for everything!

Leesha
BronzeLounger
Posts: 1433
Joined: 05 Feb 2010, 22:25

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hi Hans,
Just wanted to let you know that I imported 2 years worth of monthly files without a hitch. There were very few items that needed correcting and no blow ups after you added the piece for 1 name names. Awesome job!
Thanks again,
Leesha