This will take me a While.
This will take me a While.
no prob! tks!
Hello, I have the Table Creation routine working fine.
Are the names of the Tables that you provided the names you want to use for the tables?
Is the data going to be different in the 2 tables, as it appears to be the same in those that you supplied.
Yes, sometime the data may be same.Originally Posted by OBP
Ok, here is the database, it has a Form with 2 Command Buttons.
The first Button takes the data from the table "Post_Approval_Accepted" and it uses the first record to create 2 new tables called tblone and tbltwo.
The second Button then transfers the second record which is data and splits it between the 2 tables as record 1 in each.
The 2 records are on the form so that you can see the data held in the 2 records it is going to work with.
The tables have already been created in this version, so you need to delete them before clicking the first Button.
Hi OBP,
I've added one more record to test the program. However, I've encountered error message when trying to transfer records, the error msg: "Object variable or With Block variable not set". Attached a new database for your assistance. tks a lot!
Btw, can i check the following:
1. How to have the 1st field (Loan No) to appear in the 2 new tables? Because 1st field is my primary key.
2. How to have Null value (instead of "-") if the original field is empty? I've tried to change your coding from "-" to "", it doesnt work.
I deliberately used the "-" instead of Null values as it shows that all of the Fields have been translated, but I have now set it to "".
I have added the LoanNo field to the 2nd table in place of the Autonumber field.
Hi OBP,
Yeh! Successfully downloaded! tks a lot!!!
You are great!
9 years after the fact when you posted this and I run across it. I'm in the same situation as the subject of this thread. You stated in this post you uploaded a db. Where can I access that? I'm in need of performing the same type of import. 262 columns in a delimited text file. Looking to to split into two tables and once in Access delete out what I do not need. I've tried and tried and am coming up short. I hope you receive this. Thanks for any help.
Hello, I haven't been on VBAX for quite some time, I have reviewed the origiinal request and my responses.
I no longer have a copy of that database
Do you understand the gist of my answers?
Can you do the same thing with your file and split it?
What version of Access are you working with?
ps I have found a couple of Importing and parsing databases, but I am not sure if one of them is the one that you want.
Last edited by OBP; 05-05-2017 at 06:32 AM.
OBP, thanks for getting back with me. I wasn't expecting you to have a copy of the db but I figured I'd ask in hopes to get lucky.
I'm working with Access 2010. From what I have read in this thread, it seems you performed exactly what I'm trying to do. I created two tables containing only field names. tblImport1 - 200 fields and tblImport2 - 62 fields. Ive tried modifying an existing solution I found and the code runs to the end as the msgBox is executed; however, no data is imported into the tables. When I step through it, I believe the file is being opened but maybe it isn't. Here is the code below. Any comments or suggestions are appreciated. Thanks
Option Compare Database Public Sub Import() ' to use the ADODB.Recordset, be sure you have a reference set to ADO Dim rst As ADODB.Recordset Dim rst2 As ADODB.Recordset Dim strFile As String Dim strInput As String Dim varSplit As Variant Dim intCount As Integer Set rst = New ADODB.Recordset Set rst2 = New ADODB.Recordset ' CHANGE THE TABLE NAME HERE rst.Open "tblImport1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic rst2.Open "tblImport2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic ' CHANGE THE TEXT FILE NAME AND LOCATION HERE strFile = "D:\Users\Cox\Desktop\testfile.txt" Open strFile For Input As #1 Dim i As Integer Dim n As Long Do Until EOF(1) ' This counter is just to get to the applicable line before importing intCount = intCount + 1 ' reads the text file line by line Line Input #1, strInput ' starts importing on the second line. Change the number to match which line you ' want to start importing from If intCount >= 2 Then n = n + 1 ' creates a single dimension array using the split function varSplit = Split(strInput, vbTab, , vbBinaryCompare) ' adds the record With rst .AddNew .Fields(0) = n For i = 1 To 200 If Nz(varSplit(i - 1), "") = "" Then .Fields(i) = Null Else .Fields(i) = varSplit(i - 1) End If Next i .Update '.MoveNext 'I don't think you should need this End With With rst2 .AddNew .Fields(0) = n .Fields(1) = varSplit(0) For i = 201 To 264 If Nz(varSplit(i - 1), "") = "" Then .Fields(i - 199) = Null Else .Fields(i - 199) = varSplit(i - 1) End If Next i .Update End With End If Loop ' garbage collection Close #1 rst.Close Set rst = Nothing rst2.Close Set rst2 = Nothing MsgBox "Successful file import.", vbInformation End Sub
The interesting thing is you can import the data first using this kind of code (change the table & file names obviously)
DoCmd.TransferText acImportDelim, , "temptable2", "C:\Access\TEST.TXT", True
temptable2 is a table with just one memo field in it.
Once the data is in the table you can then use your VBA to parse the data in to the fields into the new table(s)
Can you try that code to see if it works for you.
I created temptable with only one field (Import) set to memo. Used the command statement provided but import failed. Received run-time error 2391 - table does not contain....It listed out header names in the text file. Set "hasfieldnames" to false in the command statement but same error but listed out F1, F2, and so on. Any thoughts?
If this does work, what is the field size for a memo field?
The memo field will hold 65,536 characters.
I have no idea why it won't work for you when it did for me and other users.
So let's see if we can find out if your VBA code is working on the Import part.
After this line add this line of code
Line Input #1, strInput
Msgbox strInput
That should attempt to display the Data in the string.
If that doesn't work we can input a character at a time to see if that works using
Input #1, strInput
instead if Line input.
The memo field will hold 65,536 characters.
I have no idea why it won't work for you when it did for me and other users.
Did you try
DoCmd.TransferText acImportDelim, , "temptable2", "C:\Access\TEST.TXT", False
So let's see if we can find out if your VBA code is working on the Import part.
After this line add this line of code
Line Input #1, strInput
Msgbox strInput
That should attempt to display the Data in the string.
If that doesn't work we can input a character at a time to see if that works using
Input #1, strInput
instead if Line input.
I get this error message Error.JPG when trying to import everything into one memo field.
As far as the VBA import, I added the msgBox and a string of data was displayed. It displayed the column header names in the text file. It didn't have any of the data from the file only column headers.
Thanks for your help thus far. It can't be too far away if its reading the file. Something just off in the loop portion.
OK, so when you press OK does the Msgbox display the next line of input data?
You could also add your counter n to the msgbox ie
Msgbox n & " = " & strInput
OK, so when you press OK does the Msgbox display the next line of input data?
If intCount >= 2 Then
You could also add your counter n to the msgbox ie
Msgbox n & " = " & strInput
When I click ok, the code runs to end and displays the last msgBox "Successful Import". I added the counter n as you suggested. With that being added, the msgBox displays the column headers as it previously was but now there is 0 = followed by the column header names. It's almsot like it's reading the header names but nothing further. I don't know what to try.
If you only click once and it exits the loop something is triggering it.
Can I suggest making a few simple changes, first of all add an Error trap.
After your Dim Statements add
On Error goto errortrap
before End Sub add
Exit sub
errortrap:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Now add intCount to your Msgbox
Msgbox n & " - " & intCount & " = " & strInput
Now move the n = n+1 to above the line
If intCount >= 2 Then
At the moment intCount should increment, but n will only increment if intCount is 2 or greater so by moving it we will see if both of the increment..
Would it be possible to send me a copy of the database and the text files?