473,287 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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 4820
nico5038
3,080 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

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

Similar topics

3
by: serge | last post by:
I am using SQL Server 7 SP4. I have created a blank database in which i am trying to import using DTS wizard all tables/views/stored procedures without any DATA (records). I keep getting...
1
by: Chris | last post by:
Background: I am using a MS Access 2000 front end with SQL Server 8.0 back end. I have the requirement to import all text files (regardless of filename) from a given folder on the network into a...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
4
by: Nhmiller | last post by:
This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced...
1
by: lorirobn | last post by:
Hi, I have been manually importing a text file on a weekly basis to an Access table. The data on one field is about to change (happens to be the first field). It was numeric, and is defined as...
1
by: Andrew Tatum | last post by:
I have the following procedure that works great when I run it from SQL Server Manager. EXEC prGetReferrerPayroll @startdate, @enddate, @totalsignups, @totalopts. All I do is replace the...
1
by: tezza98 | last post by:
I need some help. Im using a dtsrun command to import 9 tables into an Access database, most of the tables have about 1000 rows, but one has 20000+ rows and grows everyday. Im Using Access 2003...
13
by: Neil | last post by:
Can I get the name of a procedure from within the procedure? In my error handler, I write the error to an error table. I'd like to write the name of the procedure that's writing the error. But,...
0
docdiesel
by: docdiesel | last post by:
The fastest (and cheapest) transfer of data is that which doesn't have to be done - because the data is already there. Or still there - the caching of files is widely known and practised. Each web...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.