468,733 Members | 1,677 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,733 developers. It's quick & easy.

Advanced CSV Import Procedure using VB and SQL within Access 2007

129 100+
I am wanting to import CSV files into Access, which isn't a problem at the moment the code i have is as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim CSVTable As String
  3. Dim FilePath As String
  4. Dim Result As String
  5.  
  6. strSQL = "DELETE * from csvordlin"
  7. CSVTable = "csvordlin"
  8.  
  9. FilePath = InputBox("Please Enter a Path File for the CSV Location!", "Criteria Required")
  10. If FilePath = "" Then
  11. Else
  12. DoCmd.SetWarnings False
  13. DoCmd.RunSQL strSQL
  14. DoCmd.SetWarnings True
  15. DoCmd.TransferText acImportDelim, , CSVTable, FilePath, False
  16. Result = MsgBox("You have Successfully Imported your chosen CSV file!", vbInformation, "Import Status")
  17. End If
This successfully imports details into csvordlin table. Attributes such as StkID, Qty, Description, Height, Width and Depth are contained within. I am wanting to transfer these details into an existing table called "ordlin" (OrderLine) for an particular order but it needs check parameters such as if a certain StkID does not exist within the "stkmas" table then provide the option to ADD these Details to the "stkmas" table with an INSERT statement.

I have created a Customer Order Form where there is an OrderHeader and OrderLine contained within. I have OrderNo set as the Primary Key for the OrderHeader and OrderNo and StkID for the OrderLine so i can have many lines for one OrderHead.

So as a Step-by-Step approach, i am wanting the following to happen:

1. Create the OrderHead
2. Click on a Button to Import the CSV file as shown above.
3. Check the Imported Data exists within existing Stk file.
4. If not have the option to add to the existing Stk file.
5. Import the data into the Form OrderLine where it will have to assign an OrderNo with each row of data.
6. Assign correct Stk ID's or (New ones) with the Stk Descriptions imported. (Will explain below **).
7. Assign any Price that belongs to the existing "stkmas" table. (D-LOOKUP most likely)

This is my crappy attempt of transferring the data from the csvordlin table to the ordlin table:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "INSERT INTO ordlin(StkID,Qty,Width,Depth,Height)SELECT F1,F4,F5,F6,F7 FROM csvordlin"
  4.  
  5. DoCmd.RunSQL strSQL
It didn't work as u may know. F1 = StkID, F2 Stk Description, F4 = Qty, F5 = Width, F6 = Depth and F7 = Height.

** When sorting this database out i assigned StkID's to Stock Items, where as the Imported data uses the StkDesc as there IDs, so i am gonna have to match my StkShortDesc with there StkID as they dont use numbers. For example;
Expand|Select|Wrap|Line Numbers
  1. StkID = 133, StkShortDesc = Base, Qty = 1 - Mine
  2. StkID = Base, StkDesc = Base Board, Qty = 1 - CSV File (Theirs)
My Visula Basic is limited when trying to do something like this and is a bit out of my league as you could probably tell. Any help would be greatly appreciated.

Thanks in advance!
Aug 3 '08 #1
2 4566
nico5038
3,079 Expert 2GB
A rather daunting task.
To start with the SQLrun I would run the string first from the query-editor. Simply paste the string in the SQL-mode and press the ! for running the statement.
I guess that one of the fields datatype doesn't match,but Access will warn you for he proper error.

Nic;o
Aug 3 '08 #2
Stewart Ross
2,545 Expert Mod 2GB
Constantine Al, this thread is a virtual duplicate of this thread which you posted. I have retained your former thread as it contains a screenshot which the current thread does not, but I have closed it to prevent confusion. Please do not double-post your threads in future - you will not find they are answered any faster as a result. It is also against our posting guidelines, with which you should be familiar. The guidelines are listed at the top of the forum.

MODERATOR
Aug 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Andrew Tatum | last post: by
13 posts views Thread by Neil | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.