Import CSV File - One Drive and C Drive
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
Not a problem! BTW, I personally LOVE your spreadsheet! Have a nice dinner.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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).
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).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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".
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
1. You can use
CurrentDb.Execute "DROP TABLE import_importerrors", dbFailOnError
2. Change the SplitNames procedure to
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.
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
3) Specify that the ZIP code is a text field instead of a number field.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Import CSV File - One Drive and C Drive
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, ...
Is there a reason that the only prefix you recognise is Dr? What about Mr, Mrs, Ms, Mx, Master, Miss, Professor, Sir, Lord, ...
StuartR
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
It would be easy to add those using the same methods. Since Leesha didn't mention them, I left them out of the code.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
You can either create an update query with the following SQL and run it:
(substituting the correct table and field names of course), or run it from code:
Code: Select all
UPDATE TableName SET ZipCode = "0" & ZipCode WHERE Len(ZipCode)=4
Code: Select all
CurrentDb.Execute "UPDATE TableName SET ZipCode = '0' & ZipCode WHERE Len(ZipCode)=4", dbFailOnError
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
There are a few weird characters before Member ID...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
An Excel file would probably be better.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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?
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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)
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)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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).
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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!
Thanks for everything!
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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