Import CSV File - One Drive and C Drive
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Import CSV File - One Drive and C Drive
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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"
DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=Environ("OneDrive") & "\Documents\Trinity Solutions\TrinitySolutionsDatabaseFiles\Import\Import.csv"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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.
, 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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
Sounds good. I wondered if this would be another example of storing the path in table.
Thanks!!
Thanks!!
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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"
DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=tblAdministrativeInformation.ImportPathLink\Import.csv"
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
No, use
DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.csv"
DoCmd.TransferText TableName:="tblImportYLCSVTemp", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.csv"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
Ok will give it a shot. Thanks so much!
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
Thanks,
Leesha
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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!
Thanks!
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
You can specify that the file is UTF-8 in the import specification:
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
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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
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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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
Thanks!
Leesha
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
I'll have to search my code archive, I might have something for this purpose.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
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.
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.
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
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
At least I asked :-)
Leesha
-
- Administrator
- Posts: 78586
- 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 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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import CSV File - One Drive and C Drive
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.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import CSV File - One Drive and C Drive
I'll work on it, but it's dinner time here now.
Best wishes,
Hans
Hans