Import CSV File - One Drive and C Drive

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

Import CSV File - One Drive and C Drive

Post by Leesha »

Hi,
The issue of One Drive and C Drive for folder storage will drive me nuts. I have a csv file that needs to be imported into tblImportYLCSVTemp. The file is called "Import.csv" and is located on my computer in the following path:

C:\Users\Leesh\OneDrive\Documents\Trinity Solutions\TrinitySolutionsDatabaseFiles\Import

The file location and file name will always be the same. The username for the computer is stored in tblAdministrativeInformation in a filed called ComputerUserNameSupplied.

What would the code look like to import the file?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
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 »

Have you created an import specification for the csv file? I don't know if it will work without one. But you can try

DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=Environ("OneDrive") & "\Documents\Trinity Solutions\TrinitySolutionsDatabaseFiles\Import\Import.csv"
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hi Hans,
I will need some education here. I didn't create an import specification file and as a result all the fields come in as F1, F2, F3 etc. If I create an Import specification file, does that stay within the database or does it need to be done for each computer this is on?

My next question, the code to import specifies OneDrive. There are computers who don't use OneDrive. Will this still import?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
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 »

Does the csv file contain field names / column headers in its first row? If so, you can add

, HasFieldNames:=True

at the end of the DoCmd.TransferText line.

When you create an import specification, it is stored in the database, so it travels with the database.

When we were discussing email attachments, I suggested to use the path of the database; that was dropped. I then suggested storing the full path in a table.
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Sounds good. I wondered if this would be another example of storing the path in table.
Thanks!!

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Ok, so I've got a field [ImportPathLink] in tblAdministrativeInformation which contains the path the where Import.csv is stored. Is this how I would put it into code?

DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=tblAdministrativeInformation.ImportPathLink\Import.csv"

User avatar
HansV
Administrator
Posts: 78236
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 »

No, use

DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.csv"
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Ok will give it a shot. Thanks so much!

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

I set up the import specs. When I go to import I get the attached error. I don't see this field in the .csv file being imported.
Thanks,
Leesha
You do not have the required permissions to view the files attached to this post.

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Found the answer. It's the version of csv file that is being imported. It is a CSV UTF-8 file. When I save it as CSV(comma deliminated) it pulls in OK. Is there anything that should be in the import code that accounts for this so that the user doesn't have to do anything to the file after downloading it, before it is imported?
Thanks!

User avatar
HansV
Administrator
Posts: 78236
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 specify that the file is UTF-8 in the import specification:

S1327.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

OMG Hans, you never fail to amaze me and you make my life so much easier. I hope you know how much I appreciate you and your brain!

Now here's another thing with this file. There are over 5000 names in it. [Account Name] contain the person's full name. The database has the client's name as [lastName] and [FirstName]. The issue for me in trying to spit apart the data is that there is no uniform way that the records are in the database. They are in the following formats:

First Lase
First Middle Last
Last, First
Last, First Middle
Last ,First
Last ,First Middle

I have a hard enough time copying the code you've given me in the past to split apart information, I don't know where to start with this or if it can even be done.

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
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 »

Will it be just those formats, or could there be titles:

Jane Elliot, PhD

or suffixes:

Mark P. Jones Jr.

or

Anne Evans III

or prefixes:

Dr. Marcia F. Williams

or double last names:

Andrew Lloyd Webber
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Hans you think of everything. I just looked further into the list and yes there are some with Titles, and some with Prefixes. I suppose some like Andrew Lloyd Webber could be a double last name but I have no way of knowing. My though would be to split out the middle name and I will put in a field in the table that says [MiddleName]. I could also put in fields [Prefix] and [Title] unless you think we should just leave those out as there is no consistent formatting as to how people have data entered in the info. I'm open to whatever you sugest.
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
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 »

I'll have to search my code archive, I might have something for this purpose.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
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 »

Some 20 years ago, I had to clean a very large set of names. After some experimenting, I decided to export the data to Excel, clean and split them there, then import them back into Access. It is unavoidable that you'll have to do some of the cleaning manually, since it is virtually impossible to catch everything - double last names are an example. I found it easier to do that in Excel, since you can drag text from one cell to another.
I have attached a workbook that you can use.
I have entered a few names as examples.
If you want to split the names in your file, copy the names into column A of the workbook, from A2 down. Then click the button.
You can import the workbook into your database, or copy/paste the data if you already have a table with fields Prefix, First Name, Middle Name, Last Name and Suffix.

S1328.png
SplitNames.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

Thanks Hans! I will take a look at this. I mentioned to the main user that they may need to use this approach and they weren't very happy with me as they get multiple files like this that need to be imported and were trying to avoid going between Excel and Access. They want to just push a button. Wouldn't that be nice. I told them I would ask if there was any way to do this and not bother with the Prefix and Suffix?
At least I asked :-)
Leesha

User avatar
HansV
Administrator
Posts: 78236
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 possible to create similar code that runs in Access, but as I mentioned in my previous reply, it is fairly certain that manual cleanup will be needed. Are they, or are you, willing to do that in Access?
Best wishes,
Hans

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

Re: Import CSV File - One Drive and C Drive

Post by Leesha »

I am not going to do lol. I'm just building them the option. They know they will need to manually go in and clean up and the manager would prefer they do it in Access since not all staff are terribly proficient with Excel. If it were me, I'd do it in excel and import in that way.

User avatar
HansV
Administrator
Posts: 78236
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 »

I'll work on it, but it's dinner time here now.
Best wishes,
Hans