Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-30-2004, 01:12 PM   #1
hamed_gan
Registered User
 
Join Date: Nov 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
hamed_gan is on a distinguished road
Lightbulb Linking TXT Files to Access

Hello everybody,

I have a problem which should be simple.

I have a txt file (exported from oracle) containing data I want to add to access. What I usually did was the follwing:

1. Importing the txt file to Excel,
2. Using the Excel Import text wizard to set the brakes for the columns
3. Sort the data and remove the unwanted "crap-lines/data".
4. Save the file as Excel-Worksheet
5. Linking the file to Access. In access the data in the excel file would be taken, manipulated, and added in the Table

But now I have a problem, the txt file is more than 65,536 lines, which we all know is the maximum number of lines Excel can handle in one sheet.

What to do now? The file is a txt file, so I can perform the Import-File function for txt files. However, the Access Import Text wizard works differently than MS Excel. And I don’t really want to import them to Access, as the first need to manipulated in a Query before written in a table. So I need to link them. Linking them would result in the same pronlem.

Could anybody recommend a Program which is capable of doing the same as Excel Import Text wizard, only with large files? So, editing a txt file in such a way to remove the crap/unwanted data, but keeping the “columns” in the txt file in tact?

Any suggestions would be welcome,

Regards,
Zurvy

hamed_gan is offline   Reply With Quote
Old 11-30-2004, 01:42 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,378
Thanks: 15
Thanked 1,618 Times in 1,536 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
You can use the import wizard to define your .txt file and then save the import spec. Once you do that, you can automate the import/link process and use the TransferText Method or Action. This Method allows you to link or import. You can then use a query to take the linked file and select the appropriate data and append it to the permanent table.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-30-2004, 01:47 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,079
Thanks: 100
Thanked 1,891 Times in 1,727 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
When dealing with this amount of data, the only other methods I can think of would be to either

a. Break the file up with an editor into 50,000 line chunks and do several spreadsheets in sequence.

b. Write VBA code to do the equivalent of the import.

1. Open a recordset into a pre-defined table.

2. Open the file.

3. Read (INPUT) a line unless you hit the EOF.

4. Skip your infamous crap lines, assuming there is a programmatic way to identify them.

5. Parse the line into pieces-parts. For instance, if it is comma-delimited, look for the commas using InStr to identify the fields. Do one InStr call for each comma, then use that location + 1 as the starting point for the next InStr function. Pick apart each field and store it either as text or in the eventually desired format in variables inside your VBA module. Remember that if you used the InStr function, what you found included the comma you were searching for. So you might have to remove it before translating the string you got.

6. Do an .AddNew on your recordset

7. Set the field values in the new record based on the pieces-parts you parsed earlier.

8. Do an .Update on your recordset

9. Go back and do it again unless you hit the EOF.

10. Close the file.

11. Close the recordset.

If you don't feel comfortable with VBA than option "a" might be your only shot. I don't recall any interfaces for Windows that allow you to use raw text (delimited or not) as a database.

The_Doc_Man is offline   Reply With Quote
Old 12-02-2004, 01:45 PM   #4
hamed_gan
Registered User
 
Join Date: Nov 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
hamed_gan is on a distinguished road
tnx ya all!

You really helped me out.

Ciao for now.

hamed_gan is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:04 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World