Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 8th, 2005, 11:02 PM
Authorized User
 
Join Date: Sep 2004
Location: Quincy, MA, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to automate import with notepad

Hi everyone,
I am charged with the responsibility of creating a database that imports data from text files. One of the files is longer than 65,536 rows but if I open it in notepad, I can see all the rows.
Is it possible to write a vba procedure to automatically open the text file in notepad, and then populate an access table with this data?
I am very grateful for any help with this project.

Reply With Quote
  #2 (permalink)  
Old February 8th, 2005, 11:34 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Do you have to use notepad? Could you use Word?

Reply With Quote
  #3 (permalink)  
Old February 9th, 2005, 12:18 PM
Authorized User
 
Join Date: Sep 2004
Location: Quincy, MA, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the post.
It seems that I do need to use Notepad as some of the rows are extremely long and Word mashes them together in an unusable way. Notepad lines them out properly.

Reply With Quote
  #4 (permalink)  
Old February 9th, 2005, 01:34 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

So you are saving each line in notepad as a row on a table? Are you doing this to be able to recreate the notepad document?

Kevin

Reply With Quote
  #5 (permalink)  
Old February 9th, 2005, 01:44 PM
Authorized User
 
Join Date: Sep 2004
Location: Quincy, MA, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I want to recreate the document as a table and then perform the filtering with that table.

Reply With Quote
  #6 (permalink)  
Old February 9th, 2005, 01:48 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

The reason I'm asking is you can save the whole document, as an OLE Object, which is a single row on a table. The object can then be edited by notepad.

Kevin

Reply With Quote
  #7 (permalink)  
Old February 9th, 2005, 03:18 PM
Authorized User
 
Join Date: Sep 2004
Location: Quincy, MA, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kevin,
Thanks again. However, I am confused by the suggestion.
I cannot see how the text file can be saved as an OLE object. I've tried using "Save As" but it doesn't give that option. Even if I could, I don't want to edit the document on Notepad, as it appears more-or-less fine (there is another problem at that point but I need to take one-step-at-a-time, as I am new to Access).
Essentially, I seek to have a button on a form that, when clicked, automatically launches Notepad, opens the large file (85,000 rows) and then imports it into Access.

Thanks

Reply With Quote
  #8 (permalink)  
Old February 9th, 2005, 03:26 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Tim,
What type of data is in these rows? I'm trying to understand why you would want to have a Notepad text file, then save it line by line into an Access table.

Kevin

Reply With Quote
  #9 (permalink)  
Old February 9th, 2005, 04:04 PM
Authorized User
 
Join Date: Sep 2004
Location: Quincy, MA, USA.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kevin,
The entire project captures 13 daily files describing "Corporate Actions". The files are produced from an out-of-state mainframe and sent to us in Excel (they seem to be text-type, although the "file properties" calls them the current date!).
The actual data consists mostly of words and some numbers. Some of the fields are long (100 charcaters), others are very short (1 character). The vast majority of the rows consist of 4 columns. The others range from 20 columns to 82 columns.
Management wants to capture this daily info in an Access database.
If I attempt to import the data of the largest file (85000 rows) into its particular Access table, only the first 65,536 rows are opened. If I open the file with Notepad, the entire 85,000 rows are opened. It seems that I need to open the file in Notepad (from Access) and then import the file to its particular Access table.
This is the process that I want to automate. I do not want to "save" the rows "line-by-line" into Notepad: I just want to open it with notepad and then save it to Access.
Thanks Kevin

Reply With Quote
  #10 (permalink)  
Old February 9th, 2005, 04:10 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Tim,

Here's how you do it. This is the On_Click for your form's button.


Dim TextLine
Dim strquote As String
strquote = """"

Open "C:\TESTFILE.txt" For Input As #1 ' Open file.
DoCmd.SetWarnings False
Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    SQL = "INSERT INTO [Text Table] ( [text line] ) " _
          & "SELECT " & strquote & TextLine & strquote & " AS Expr1;"
    DoCmd.RunSQL SQL
Loop
Close #1 ' Close file.

---------------
C:\Testfile is the name of your file.
Since you have 13 of these you can replicate this. It doesn't use notepad, it opens the file directly.

Try one and see if it works.

any trouble, email me directly @ kevinpreilly at aol

kevin


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Encryption Notepad, need some help! Groogy Visual C++ 2005 0 March 23rd, 2007 12:31 PM
Automate import (fixed width) U.N.C.L.E. Access VBA 0 February 3rd, 2006 05:55 PM
Notepad or VS,Net Oh_Help VS.NET 2002/2003 2 February 11th, 2004 12:59 AM
Reference in notepad???? quetzalcoatl ADO.NET 2 October 31st, 2003 05:54 PM
Newline in notepad, HOW.....???? veskoula XSLT 3 October 29th, 2003 09:49 AM



All times are GMT -4. The time now is 06:24 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.