By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,472 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Import text

P: 13
Hi,

What's the quickest way of importing text into SQL Server when importing data using Access Project (.adp)?

By the way, I can't use DoCmd.TransferText as the files don't have headers.

Current Code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub ImpData_Click()
  3. Dim cncurrent As ADODB.Connection
  4. Set cncurrent = CurrentProject.Connection
  5. Dim rs As ADODB.Recordset
  6. Set rs = New ADODB.Recordset
  7. 'sql to open table connection
  8. Dim strsql As String
  9. strsql = "Select * from [T Table]"
  10. 'opening the record set
  11. rs.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
  12. Dim column(10) As String ' holder for the text data
  13. Dim linedata As String
  14. Dim i As Integer
  15. Open Me.Path For Input As #1 ' open file for input
  16. Do Until EOF(1) 'scan through file inputing data to the dataset from the Array
  17. Do Until i = 10
  18. Input #1, linedata ' input a line of data from the text file
  19. column(i) = linedata
  20. i = i + 1
  21. Loop
  22. i = 0
  23. rs.AddNew
  24. Do Until i = 10
  25. rs.Fields(i) = column(i)
  26. i = i + 1
  27. Loop
  28. rs.Update
  29. i = 0
  30. Loop
  31. End Sub
  32.  
This code works fine but the text files that will be imported are around 50Mb each. It takes a long time for this code to get through 50Mb!!!!

Regards,

Dark
Feb 4 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
I use DoCmd.TransferText for tab delimited data without column headings. Have you tried an import specification?
Feb 4 '09 #2

ADezii
Expert 5K+
P: 8,597
@Darkside12
I'm a little confused by the logic, but would something like this work?
Expand|Select|Wrap|Line Numbers
  1. Dim cncurrent As ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim strsql As String
  4. Dim linedata As String
  5. Dim i As Integer
  6.  
  7. Set cncurrent = CurrentProject.Connection
  8. Set rs = New ADODB.Recordset
  9.  
  10. 'SQL to open table connection
  11. strsql = "Select * from [T Table]"
  12.  
  13. 'Opening the Recordset
  14. rs.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
  15.  
  16. Open Me.Path For Input As #1 ' open file for input
  17.  
  18. i = 0
  19.  
  20. Do Until EOF(1)
  21.   Do Until i = 10
  22.     Input #1, linedata ' input a line of data from the text file
  23.       rs.AddNew
  24.         rs.Fields(i) = linedata
  25.       rs.Update
  26.       i = i + 1
  27.   Loop
  28.     i = 0
  29. Loop
  30.  
  31. Close #1
  32.  
  33. rs.Close
  34. Set rs = Nothing
  35.  
Feb 5 '09 #3

P: 13
Hello again,

Thanks for the responses,

@ChipR
ChipR,

Great suggestion, as was my first thought when I was trying to over come this. Unfortunately Microsoft seem to have forgotten to add the import specification feature to MS Access Project ;-)


@ADezii
ADezii,

Your code is way better than mine and should cut down on the time it takes to process the file. (Thank You!!!)

But I was looking for a different method for importing the text file. Something other than reading the text file into a recordset (as per my code) or transfer text which I can't do due to the lack of a import specification function in Access Project (.adp).

I'd be really greatful for any ideas.

p.s. is there a way to access the SQL Server import Export wizard from VBA?
Just had that thought.

Thanks again for your responces,

Regards,

Dark
Feb 5 '09 #4

ADezii
Expert 5K+
P: 8,597
How about Plan B, DTS (Data transformation Services), which can bring the Text Based Data directly into SQL Server? I'll provide a brief overview of the process:
  1. Programs
  2. Microsoft SQL Server
  3. Import/Ex[port Data
  4. DTS Import/Export Wizard
  5. Set the Data Source = Text File
  6. Select a File name (*.txt)
  7. Specify certain Parameters regarding the Source File Format
  8. Specify the Column Delimiter
  9. Select the Destination SQL Server, Database, and means of Authentication
  10. Select Views to copy and Column Mappings if required
  11. Save/Execute the DTS Package
Feb 5 '09 #5

P: 13
@ADezii
Hi Again ADezii,

Yup, I can use DTS via remote access to import data (and do regularly) as I'm admin. Unfortunately the rest of my team don't have that kind of access.

Is there any way of controling DTS from VBA?

Regards,

Dark
Feb 5 '09 #6

ADezii
Expert 5K+
P: 8,597
@Darkside12
Here is some sample code that I dug up that will execute a DTS Package via VBA. To execute a package from Visual Basic is a relatively simple task using the DTS object model. Before you start using the object model you must add a the appropriate reference to your project. From the Project menu select References and check the "Microsoft DTSPackage Object Library".

Here is a very simple example that uses integrated security to load the package MyPackage and then execute it:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SimpleExecutePackage()
  2. Dim oPKG As New DTS.Package
  3.   oPKG.LoadFromSQLServer "MyServer", , , _
  4.   DTSSQLStgFlag_UseTrustedConnection, , , , "MyPackage"
  5.   oPKG.Execute
  6.   oPKG.UnInitialize
  7.   Set oPKG = Nothing
  8. End Sub
  9.  
Before going any further the one drawback to using Visual Basic is that it is apartment threaded and DTS is free threaded. This can cause exception access violations. The simple way top overcome this is to ensure all steps execute on the main package thread. To do this set the ExecuteInMainThread property as illustrated below:
Expand|Select|Wrap|Line Numbers
  1. ' Set Exec on Main Thread
  2. For Each oStep In oPKG.Steps
  3.   oStep.ExecuteInMainThread = True
  4. Next
  5.  
Whilst the simple example above will do the job of executing your package, you will probably want some more information about the status of execution and any errors that occurred. The first option is to check the execution result (ExecutionResult property) for each step. If this indicates failure (DTSStepExecResult_Failure) then you can go on to retrieve the full error number, source and description using the GetExecutionErrorInfo method for the step.
Feb 5 '09 #7

Expert 100+
P: 1,287
Can you import excel files? I think most delimited text could be put in an excel spreadsheet and saved with an automated process.
Feb 5 '09 #8

P: 13
@ChipR

Hi Again,

Sorry for the late responce.

ChipR,

I could have imported a spread sheet but unfortunately the text files were about 500,000 lines + in size. Might have still worked if I had office 2007 to hand in the office but we're still stuck on 2000 here.

@ADezii
Adezii,

I found the same code as you ;-)

In the end I didn't use it and went for some code that was on msdn. I got the code to work fine but........... you needed to reference a SQL Server dll. Looked for our copy of SQL Server (So I could install the client tools on the workstations that would run the ap) and couldn't find the Disk :-(

Gave up, built the whole solution in VB.NET without a backend database. It takes no more than a minute to plow through 500,000 records.

Thanks again for all your help with this.

Dark
Feb 12 '09 #9

Post your reply

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