Import Advice please (1 Viewer)

Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
I need to be able to select a csv file which is a bank statment download and import into a database.
However I also need to mark each entry with additional data and also allow the option of not importing every transaction.

For the additional data I need to add a propertyID and a CategoryID to those records that will be finally imported into my tblTransaction.

My plan of attack was to
Create a main form that allows the selection of the latest csv file
Link to that file via VBA
Show the linked transactions in a continous sub form with combos for PropertyID, CategoryID and a checkbox to select those to be imported(and anything else that develops).

However where I am stuck is how to go about putting these records into my actual table tblTransaction.

Normally I would just run a query, but some of the fields will not exist in the import table? namely PropertyID and CategoryID

How would I go about doing it this way?, or is there a better way please.?

TIA
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:38
Joined
Aug 30, 2003
Messages
36,125
One common technique is to import data into a local table where you can manipulate it, do validation, etc. From there you then import into your actual destination table.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
Thank you Paul,
I'll go that way then. Do I just delete the old records before import, or delete and recreate the table, as I have seen mention of bloat in other posts. That was my idea of just a linked table?

There is not going to be a huge amount of data (at least for this DB) but good to know the best way. I would probably keep a empty table with the correct structure and delete the import table and copy the empty version again with the same name?

Just found out I cannot use a continous form as one checkbox clicked sets them all.:banghead:
 
Last edited:
NauticalGent

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 21:38
Joined
Apr 27, 2015
Messages
6,337
Gasman said:
Just found out I cannot use a continous form as one checkbox clicked sets them all.:banghead:
Click to expand...

That is because the check box is unbound? I have tried that in the past too and that was always the culprit.

I tried to do something silimar like this but gave up and paid a subscription to Quicken/Quickbooks...
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:38
Joined
Aug 30, 2003
Messages
36,125
I would use the same table, just empty it before importing records from the csv file. It would already have the desired structure and you could have a form bound to it for users to manipulate the data and choose records to import.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
NauticalGent said:
That is because the check box is unbound? I have tried that in the past too and that was always the culprit.

I tried to do something silimar like this but gave up and paid a subscription to Quicken/Quickbooks...
Click to expand...

Yes unbound, as would be the Property & Category IDs.

I'l create a table as Paul suggests and delete and append from the csv file.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:38
Joined
Aug 30, 2003
Messages
36,125
Gasman said:
Yes unbound, as would be the Property & Category IDs.

I'l create a table as Paul suggests and delete and append from the csv file.
Click to expand...

They should be bound to fields in the temp table.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
Thank you jdraw,

That gives some good info on what I am trying to achieve.
However I am a little puzzled as to why one needs to state the import has field names in the TransferText function f you have already stated that in the specification?
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 21:38
Joined
Jan 23, 2006
Messages
15,379
Gasman,

I try to suggest approaches/tools to help a poster with concepts and approach. I do not necessarily remember all aspects of the video or article. I was suggesting that video by PhilS because he is an experienced developer and offers free videos to the rest of us.

Here is another --this one Importing by Steve Bishop --it's intended for reference and concepts that will hopefully offer some insight.

Good luck.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
jdraw,

That is exactly what I am looking for.

However I have now hit a small snag, that I would like to be able to account for.
I want to set the initial path to the user's download folder.

Normally I could manage that with
Code: 
Environ("Homedrive") & Environ("Homepath") & "\Downloads\"
However even I have moved my Data to my F drive in case of a windows reinstall. and I did so via the windows dialog, not manually, so when I click on Documents I get F:\Users\Paul\Documents etc , except the above still gives C:\Users\Paul\Documents etc

Would anyone know how to obtain the 'real' document path at all?

TIA
jdraw said:
Gasman,

I try to suggest approaches/tools to help a poster with concepts and approach. I do not necessarily remember all aspects of the video or article. I was suggesting that video by PhilS because he is an experienced developer and offers free videos to the rest of us.

Here is another --this one Importing by Steve Bishop --it's intended for reference and concepts that will hopefully offer some insight.

Good luck.
Click to expand...
 
isladogs

isladogs

MVP / VIP
Local time
Today, 02:38
Joined
Jan 14, 2017
Messages
18,218
The easiest way is Environ("USERPROFILE") & "\Downloads"

However this also fails if you move My Documents & other 'special folders to a different drive (as I do for the same reasons as you - it saves a lot of time when reinstalling a fresh copy of Windows)

However the following code worked for me (taken from the SO reference listed below)

Copy it to a standard module

Code: 
Option Compare Database
Option Explicit

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Function GetDownloadsFolder()

'Code from https://stackoverflow.com/questions/23070299/get-the-windows-download-folders-path
'{374DE290-123F-4565-9164-39C4925E467B} is the GUID for the Downloads folder

GetDownloadsFolder = RegKeyRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\{374DE290-123F-4565-9164-39C4925E467B}")
   
Debug.Print GetDownloadsFolder

End Function

HTH
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:38
Joined
Sep 21, 2011
Messages
14,287
Brilliant Colin :cool:

Thank you so much.

I've managed to get it all working today and that was pretty much the icing on the cake.

Learnt a fair bit today. :)

Thank you all.

ridders said:
The easiest way is Environ("USERPROFILE") & "\Downloads"

However this also fails if you move My Documents & other 'special folders to a different drive (as I do for the same reasons as you - it saves a lot of time when reinstalling a fresh copy of Windows)

However the following code worked for me (taken from the SO reference listed below)

Copy it to a standard module

Code: 
Option Compare Database
Option Explicit

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Function GetDownloadsFolder()

'Code from https://stackoverflow.com/questions/23070299/get-the-windows-download-folders-path
'{374DE290-123F-4565-9164-39C4925E467B} is the GUID for the Downloads folder

GetDownloadsFolder = RegKeyRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\{374DE290-123F-4565-9164-39C4925E467B}")
   
Debug.Print GetDownloadsFolder

End Function
HTH
Click to expand...
 
isladogs

isladogs

MVP / VIP
Local time
Today, 02:38
Joined
Jan 14, 2017
Messages
18,218
Gasman said:
Brilliant Colin :cool:

Thank you so much.

I've managed to get it all working today and that was pretty much the icing on the cake.

Learnt a fair bit today. :)

Thank you all.
Click to expand...

You're welcome

The same idea can be used for any 'special folder' by checking for its GUID in the registry key:
Code: 
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

Now if you'd like to do me a favour back, please try my 'Get Current Location using VBA' example and let me know if it works for you in A2007 & whichever version of Windows you use

https://www.access-programmers.co.uk/forums/showpost.php?p=1576182&postcount=9
 
You must log in or register to reply here.

Similar threads

C
make query updatable
Replies
17
Views
599
ClaraBarton
C
S
Import Large Excel File
Replies
16
Views
339
standenman
S
S
Solved Import .CSV VBA Run time Error 3011
Replies
4
Views
336
sathsaj
S
R
Solved Importing and calculating the data
Replies
12
Views
507
Robrobby
R
M
Importing data from excel with file name
Replies
15
Views
804
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom