Hi,
I am trying to import a text file to a database but I am getting 3 rows for each record because the txt file is setup the same way. How do I fix the issue?
txt file stores the data like this:
line 1 name ssn dob
line 2 add city zip
line 3 pcp nurse
those 3 lines belong to the same patient and they're imported to access table the same way.
Thanks in advance
The easiest way would be to read the full text file into an array then create a second array with the data rearranged the way you want it. That new array would be printed to the excel sheet. We will need to see a sample (could used dummy data) to test (if you don't know how to do what I just said).
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
thanks for trying to help. Here is some of the data and the way it's formatted. The xxxxxx represetn SSN or ID# etc..
If I can import all the data then I can delete the columns I don't need, else I need only the following columns:
XXXXXX, xxxxxxx, Name (Baker...), Date (8/30/1957), xxxxx, Date (07/02/2006), Gender (M) # (1554)
Sometimes there is a third # (1 and 4 lines) but (2 and 3 lines do not have a third #.
Also I don't know if this can help but all the records ends with a 0 ,
Thanks in avance
Last edited by elamranii; 08-07-2012 at 05:58 PM.
Maybe try this:
Please Login or Register to view this content.
Hi,
I put the above code in macro and made the change of the location of the file. When I ran it I get this: run-time error '6' overflow
and this line highlighted: For r = 0 To UBound(arr)
Thanks
Change the declaration of r from r% to r&. In other words from an integer to long. Let me know it resolves the error.
Hi,
It does run now, but once done it drops the data (or some)in one row. It does not go to the next row.
Very close. Thank you so much for your help this will help a lot since I have 5 years of these files.
I still have to export to an access database.
---------- Post added at 09:16 AM ---------- Previous post was at 09:13 AM ----------
Boucetta,
If this can be done in access will even be better else if the above code can work that's also fine. Thanks so much.
There must be something different from what you posted in your example and the real data that I am unaware of because it works fine from my end. Could you take the first few patients' data from one of the real files, dummy this data so that it retains the exact structure but different data and post it. Let's say 5 to 10 rows should suffice.
Because I was trying to protect some of pateint data I may have changed something when I copied it to you. Here is a few rows that have the same structue as the original txt file.
I also the last 2 rows don't end with 0 , is that matters. There are some cases like that. Again thanks.
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
55555555 ,111111111 ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,01/01/1963,M,
11111111 ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,
7777777 ,666666666 ,CHOATE ,CAASSSSS ,200 EAST 57 STREET ,NEW YORK ,NY,10022 ,01/01/1963,F,
99999999 ,S,MED,EO,08/02/2006,941 ,1925 ,Kxxx, Dxxxx P. ,1925 ,Kxxxx, Dxxxx P. ,Y,04WO,0449,01,09/21/2006,
730 ,
7777777 ,666666666 ,CHOATE ,CAASSSSS ,200 EAST 57 STREET ,NEW YORK ,NY,10022 ,01/01/1963,F,
99999999 ,S,MED,EO,08/02/2006,941 ,1925 ,Kxxx, Dxxxx P. ,1925 ,Kxxxx, Dxxxx P. ,Y,04WO,0449,01,09/21/2006,
730 ,
Here's a slight modification:
The results should look like this:Please Login or Register to view this content.
I have also attached the text file and excel sheet used for testing.Please Login or Register to view this content.
Now as for Access, I don't yet know enough about it to help you, but if this works out for you, then you can post the final code in a new thread on the Access subforum (or a forum dedicated to Access) for further assistance. I have converted code in the past between Excel and Access and it shouldn't be too hard.
Hope this helps.
abousetta
P.S. So what specialty are you in?
O my god that's was great. It worked like a charm. Thankkkkkkkkkkkkkkkk you.
I can do access by exportting the excel sheets to access.. By the way, I have a friend of mine withe the same last name?
I'm from Egypt.
Question for you, are you doing this for repeated files or just one file at any given time. The reason I am asking is because if it for several files then you should have a loop in place instead of manually picking each file.
Hi,
I was travelling last night, that's why I am late responding.
Yes, I am transfering 6 files (2006 -2012). I am ok doing doing one at the time. Thanks so much for your help.
Try this (didn't get a chance to fully test but should work fine):
Please Login or Register to view this content.
Last edited by abousetta; 08-06-2012 at 11:07 PM.
what's the difference between this one the one one you sent yesterday?
It should prompt you for a folder. It will loop through the text files in the folder importing them after the other. In your case you should be able to put all 6 files in one folder run the new code and it should import them all sequentially (one run instead of 6).
That's brillant Bousetta. I'll let you how it goes. Thanks.
Now you spoiled me I have an issue that you may have the answer to. If you're willing to help of course.
Post a new thread and I will let you know if I can manage it easily. You can PM me the link to the new thread because I won`t be available the next few hours.
Let me get this straight. Open an excel sheet, create the macro and put the code. All the 6 txt files will be imported to excel all at once? in the same excelsheet Seperate tabs?
Here is the lin to the other issue.
http://www.excelforum.com/excel-prog...excel-tab.html
You can email me to my personal email address if you prefer: [email protected]
Thanks
OK. I just tested it from my end and it works fine now. It was missing an End If statement but I just added to the code in post 14.
When you run the macro, it will prompt you for a to show it where the files are located. Once you click OK, it will import all the data from the text files in that folder.
If you have any question then let me know. If not then please mark the thread as solved.
Good luck.
abousetta
P.S. I would remove your email unless you want a ton of spam to be coming your way.
Last edited by abousetta; 08-06-2012 at 11:16 PM.
Sorry for the delay.
I am in a training all day all week, and I have no access to email.
Thank you for removing my email address.
Yes we can close and make it solved.
Again thanks for all your help.
---------- Post added at 06:03 PM ---------- Previous post was at 05:58 PM ----------
How do I close it as solved?
You can mark the thread Solved at the top of the page under "thread tools". As you removing your email, I can't edit your post but you can PM one of the moderators if you having trouble as they have access to all the posts.
Good luck.
@ elamranii
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thank you Cutter. I posted a thank you to the person who helped me. And now I know how to close a post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks